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.