Sunday, December 9, 2012

Update statistics some key points!

As a DBA, we all know that update stats is one of the key, which let SQL optimizer to decide the best (best means fast :)) way to retrieve the query results.
Now we also know that the best practice is to not just let statistics get updated automatically but also to place some kind of manual plan.
Although, you can choose a best time to manually update the statistics on every server in your farm, but realistically, you don’t have control over the time of modification of data into the tables as its control by application/users/business.
So what you can do your best is to make sure that statistics are updated most of the time!
You can leverage "sys.dm_db_stats_properties" to monitor the stats on your objects, especially large objects.
For smaller to medium table, it’s not a big concern as auto stats with manual plans can take care of most of the situation but with Large size tables(millions of rows), it can be bit tricky!
I would not rely on default automatic update of statistics for large objects.
Tables with millions of rows mean, you have to wait longer before it reaches 20% of threshold value.
Thankfully, SQL Server 2008 R2 released trace flag 2371, which controls the update dynamically.
What this mean is larger the number of rows exposed to modification, smaller the threshold get set and quicker the automatic update can happen.
Having said all this, make sure you test this trace flag  properly for your application and then make a decision.

No comments:

Post a Comment