asked
Auto update stats disabled for a user Database in Sql server
While trying to improve the performance of few queries, we found via execution plan that there were lot of Index/Clustered index seeks. Therefore: First thing we did, was to check our Re-indexing and update stats job which runs weekly for this user DB ( Around 400 GB in size and is used 24*5). The job was running fine. Later when we ran SP_Blitz, we came to know that auto-update-stats is disabled for this user DB. We expected this to be a possible cause and change it from false to TRUE(Auto update stats) Also, per SP_blitz there are user-created statistics for this DB. When ran the query to check how many, we saw around 7K user stats out there. So my question would be 1) setting the Auto update stats to TRUE would require a reboot or once changed i need to track the performance and 2) Should we consider dropping those user created stats or manually look into them one by one. @Grant, query to check last updated stats: SELECT o.name, s.name, stats_date(o.object_id, s.stats_id) AS lastupdated FROM sys.objects o JOIN sys.stats s ON s.object_id = o.object_id --WHERE s.user_created = 1 ORDER BY lastupdated How should we proceed on this, please suggest, thanks!
1) No you won't have to reboot. You'll start getting automatic statistics updates as the data changes. You'll see that it follows a formula. With zero rows, the addition of one or more rows results in stats updates. With < 500 rows, add/edit/delete of 500 or more results in stats updates. > 500, add/edit/delete of 20%+500 rows results in stats updates. 2)Are you confusing the automatically created statistics with manually created statistics? If you have 7000 manually created statistics, yes, I would evaluate them to see which ones can be dropped. Just because you have an index seek in an execution plan doesn't mean that is the correct function. Seeks should only be with smaller data sets. For very large data sets, scans can work better.
Index maintenance is not the same as statistics maintenance. You can leave the stats to false if it makes you feel better, but then you'll need to manually update the statistics using the UPDATE STATISTICS command for each of the tables, the indexes, etc. Statistics are only updated against an index during a rebuild, not a reorg.
Thanks @Grant for the suggestions. However I have a question, when I ran the script (edited in question) to find last updated stats, it shows me the date of 03/15 (on this date we manually ran a job to update stats)which should not be the case when I have already enabled the auto update stats option on 03/17 itself, Kindly suggest.
I would suggest using DBCC SHOW_STATISTICS for statistics information on any given statistic.
Thanks @Grant, You're answer very much clears the air! On the very last note, just want to confirm : the DB (A)in question ( whose auto_update stats was turned of) is the subscriber and is the replica of OLTP DB (B) which is set up with transactional replication. Therefore this DB A, is getting used by developers to fetch the reports. So my last concern would be , was i correct changing auto update stats from false to true, or should i keept it false and increase the frequency of the index maint'ce job, running every sunday. Please suggest. thanks!
15 People are following this question.
