IQE Perspective

SQL Server Performance and Statistics

Posted by Eric Baule on Mar 25, 2015, 9:15:53 PM


SITUATION:

Outdated statistics can negatively impact SQL Server Performance.

Since at least 2005 there has been a database configuration setting named 'Auto Update Statistics' that if set on should cause SQL Server to keep data statistics on the tables fairly current. One would expect that with this setting enabled, manual updates would not be required.

What history tells us however, is that something is not fully engaged in the process. In several SQL Express edition products we've seen query timeouts and replication failures that have been resolved by manually updating the statistics.  SQL Server Expert syracuse reviewing statistics

The symptoms that we are alerted to translate into a lot more user frustration than may be apparent as a query timeout indicates that the system has been steadily degrading over a time, until it degrades past the point of no return. In these cases, user have likely been running in molasses for a while muttering about system performance to no one in particular. Not until absolute failure is achieved does our tech group get called in to fix the problem which even then is not expressed as a performance problem but as a transaction failure message of some form. 

One system in the Syracuse area that displayed this type of failure and needed SQL help was a K-12 report card system wherein each grade represented table row. The problem here occurred on a “teacher’s workshop” day when every instructor in the district was entering grades concurrently. So despite proper indexing and fairly well written queries, the system timed out as the data shifted away from the recorded distribution, rendering the existing query plans inefficient to the point of timing out.

When failures of this type occur in systems that have been operating properly for a reasonable time period, the troubleshooting regime starts looking at the data involved to see if there’s anything peculiar about it, then to reviewing what, if anything has changed, then to … well, you get the idea – statistics aren’t the first thing that comes to mind. .



ACTIONS TAKEN:

That said, here is a query that will help determine the state of the stats for a table that you can use when SQL Server performance is an issue.

Step 1: Determine the state of your stats

SELECT name AS stats_name,
STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats
WHERE auto_created = 'false' 
AND object_id = OBJECT_ID('tablename');

Step 2: Update stats for individual tables

UPDATE STATISTICS tablename

Or: Just Update Everything!

EXEC sp_updatestats 


Note: You can read what gets updated and what doesn't in the messages output by this stored procedure.

 

 
CONCLUSION:

I've read where updating the statistics can be a bad thing, but I'm not convinced that can be true as the SQL Server query processor will build query plans based on the statistics regardless of their accuracy.

All databases need SQL Server performance tuning, sooner or later, and that can be a small task for a SQL Server expert or a big unknown for a network administrator.  If you have a SQL Server, find a database expert for when that time comes so you wont have to search while your system is unavailable.

For most smaller databases, updating all stats runs quickly and uses only minimal resources. You can find detailed info at Books Online

Finally, if you'd like a server review, just click this button, complete and submit the form.

Request SQL Server Review

 


 

Topics: SQL Server

Subscribe to Email Updates

Recent Posts

Posts by Topic