New answers tagged statistics
1
You may be running into a scenario where the (perhaps default) sampling percentage isn't sufficient for the stats to be useful for longer periods of time. You can quickly check to see what sample rates were used during the last UPDATE STATISTICS statement via the following query:
SELECT OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) AS ...
2
There is multiple ways to fix the parameter sniffing issue.
If you update your statistics 10 times a day and especially if that is causing performance issues, then I would look at other options.
There is some good chance that the update of the stats is not what's fixing your parameter sniffing issue. The update of the stats causes the plan to be ...
2
Time spent automatically updating statistics is included in compile time. The stats update takes 10 minutes, and the compile time for a query that triggers an auto stats update includes that time - which is why the query takes over 10 minutes every now and then.
The modification threshold for an automatic stats update, per the documentation, is
sqrt(table ...
Top 50 recent answers are included
