July 27, 2017 at 3:13 am
Hi All,
Question regarding manually updating the statistics. I have auto updates stats enabled but noticed the stats become stale very quickly , especially for larger tables (Our database has table with row count of 133 millions with a data change of 1 million rows in a day and some others above half of this count) . Since SQL server doesn't update till the change threshold reaches 20% of sampling , it leave out some tables which I suspect are causing performance issues . To update manually I deployed a job to run once 24 hours but strangely the log of the job shows that not all tables' stats are updated . Wondering on what criteria SQL deciding on updating or leaving out table . I am using the exec sp_updatestats proc . Any comments /advices on this would be of great help ..thanks in advance..
July 27, 2017 at 8:34 am
Small correction above .. over 1 million inserts are there for the table I mentioned .
Thank you
Arshad
August 2, 2017 at 5:59 pm
Arsh - Thursday, July 27, 2017 3:13 AMWondering on what criteria SQL deciding on updating or leaving out table . I am using the exec sp_updatestats proc . Any comments /advices on this would be of great help ..thanks in advance..
You can look at the code for sp_updatestats and you can see it checks if rowmodctr from sys.sysindexes <> 0.
So the tables left out had no row changes. You could also have stats that just aren't too good based on sample size, data skew, etc. So you may want to do more digging around at the performance issues as it may not be just an issue with some tables not having statistics updated. And it may not be statistics. But you would probably want to look at the execution plans and see if the estimated rows and actual rows are way different. That's often an indicator that the stats are stale. Other things can affect the estimates but stale or missing stats is one of the things that throws off the estimates which can lead to poor execution plans.
If you want to look at other issues with statistics and maintenance, the following article does a good job going through many of the questions about updating statistics:
Updating Statistics in SQL Server: Maintenance Questions & Answers
In terms of stats becoming stale quickly, that's a bit of a known issue with large tables - as you noticed due to the modifications to the table needing to be 500 + 20% of the table. If you are on 2008R2 with SP1, then you may want to look into the trace flag 2371. The article above does talk about this trace flag. And you can find Microsoft's KB article here:
Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server
Sue
August 3, 2017 at 11:28 am
Hi Sue,
Thank you for throwing light on this. I know the 20%+500 and the other criteria for the auto update.. Now it means that even if i issue a manual auto update , it still leaves out some tables as the threshold wasn't reached . My initial understanding was that it looks at inserted row-count only.
So it means that to force an update one needs to provide a non-default sampling , say 10% ? Pls advise on any implications of doing this , in ur experience / knowledge? Thank you.
Arshad
August 3, 2017 at 12:02 pm
Arsh - Thursday, August 3, 2017 11:28 AMHi Sue,Thank you for throwing light on this. I know the 20%+500 and the other criteria for the auto update.. Now it means that even if i issue a manual auto update , it still leaves out some tables as the threshold wasn't reached . My initial understanding was that it looks at inserted row-count only.So it means that to force an update one needs to provide a non-default sampling , say 10% ? Pls advise on any implications of doing this , in ur experience / knowledge? Thank you. Arshad
sp_updatestats needs one row modification. That's it. One row and it updates. And row modifications don't necessarily mean an inserted - just any modification increments the counter. And if you update one row 50 times, it increments the counter 50 times. If nothing has nothing has changed and the row modification counter is 0 then it will skip the table.
It is the auto stats that uses the threshold of 20% + 500.
With sp_udatestats, if you use @resample it uses the current sample rate. If you don't specify resample, it uses the default which is whatever SQL Server chooses - I think it's a max of 20%.
If you want more control of how this is done then you should probably use update statistics instead of just sp_updatestats.
No one will be able to tell you what to do with sampling - there are usually trade offs to any approach when updating stats and the direction you go really depends on the application. The first link I gave you in the previous posts goes over different considerations with different options.
And again as you said previously, you think the tables skipped is causing performance problems. So you would really want to go through some of the execution plans to understand more of where the issues may be. The link in that first article explains some of this as well. It also suggests things other than statistics that could be causing the problems.
With the large table and statistics going stale too early and not being picked up by auto stats, that is when you would want to look at the trace flag mentioned above. It was added for this very reason and is available on 2008R2 SP1 and above.
Sue
August 3, 2017 at 2:10 pm
Hi Sue,
Thanks so much on the points mentioned by you. Really helpful.
Thank you...Arshad
August 3, 2017 at 3:50 pm
Arsh - Thursday, August 3, 2017 2:10 PMHi Sue,Thanks so much on the points mentioned by you. Really helpful.Thank you...Arshad
You're very welcome Arshad - thanks for the feedback!
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply