Advertisements
Home > DBA Stuff > SQL DBA – Get top Tables with Stale Statistics

SQL DBA – Get top Tables with Stale Statistics

November 10, 2017 Leave a comment Go to comments

 
SQL Server Query Optimizer (QO) uses Statistics to create query plans that improve query performance. The QO uses these statistics to estimate the cardinality (number of rows) in the query result.

There are times when these STATISTICS may go stale after certain INSERT, UPDATE and DELETE operations which involves large amount of rows, not qualifying for Auto-Stats Threshold.

For more about Statistics and Threshold check on MSDN Docs link
 

Identify Stale Stats:

Here is a simple query to identify TOP tables that have stale statistics, so that you can create UPDATE statistics for them explicitly:

SELECT TOP 50
	 CONCAT(sch.name, '.', obj.name) AS 'Table Name'
	,MAX(sp.last_updated)			AS 'Stats Last Updated'
	,MAX(sp.rows)					AS 'Rows'
	,MAX(sp.modification_counter)	AS 'Modification Counter'
FROM sys.stats st
JOIN sys.objects obj ON st.object_id = obj.object_id
JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
CROSS APPLY sys.dm_db_stats_properties(obj.object_id, st.stats_id) AS sp
WHERE obj.is_ms_shipped = 0 
GROUP BY CONCAT(sch.name, '.', obj.name)
ORDER BY MAX(sp.modification_counter) DESC;


 

UPDATE Stats:

UPDATE STATISTICS dbo.table_name
GO

 


Advertisements
Categories: DBA Stuff
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: