SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Should you update stats for your SQL Server database?

Statistics on database objects are very important to the SQL Server engine optimizing execution plans and running at the most optimal performance.  There is often many times people ask do or do I not need to manually update stats in my database.

The answer to this question is not a simple yes or no.  It truly depends on the activity in your database.  As we all know stats get updated when data changes and meets an internal threshold in the database engine, this work is done automatically.  However if you are supporting extremely large database that do not update data frequently or update enough data those stats will not get updated automatically and will require you to do so manually.

There are also some standard database maintenance items like rebuilding and reorganizing indexes that will update stats too, but only the stats related to those objects.  Many times I have seen the SQL engine not using the most optimized execution plan for a stored procedure because table stats are out of data.

There is a bit of risk when you update stats using the stored proc named “sp_updatestats”.  The update stats stored procedure updates all user generated stats in the database.  You can even specify certain tables to update on, so if you have a logging table that gets used frequently you can specify to update that table only using SP_UpdateStats.  For usability options for SP_Updates please check out the Microsoft doc page on this stored proc using this link – SP_UpdateStats

Keeping stats fresh in databases can resolve a lot of performance problems when execution plans get out of whack.  If you find yourself wondering if your stats our out of data run this query below to pull the timestamp of the last update for stats:

SELECT OBJECT_NAME(object_id) AS [ObjectName]
,[name] AS [StatisticName]
,STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats;

 

The post Should you update stats for your SQL Server database? appeared first on VitaminDBA.

vitamindba

VitaminDBA.com is a source of SQL Server News and Tips coming from a SQL Server sr. database analyst with 10 years of IT experience ranging from Windows Server, Network, and SQL Server administration. I upload articles on the blog on a fairly consistent basis with the intention of at least 1 new article a week.

Comments

Leave a comment on the original post [vitamindba.com, opens in a new window]

Loading comments...