Tuning Tip: Identifying Overlapping Statistics

<div><p>Statistics are used by SQL Server's query optimizer to help determine the most efficient execution plan for a query. When the option to automatically create statistics is enabled (which it is by default) SQL Server will create statistics on columns used in a query's predicate as necessary, which usually means when statistics don't already exist for the column in question.</p> <p>Statistics are also created on the key columns of an index when the index is created. SQL Server understands the difference between auto created column statistics and index statistics and maintains both - you can see this by querying the sys.stats system view. <a href="http://kendalvandyke.blogspot.com/2010/02/tale-of-two-execution-plans-solved.html">As I found out firsthand not too long ago</a> having both auto created statistics and index statistics on the same column caused the query optimizer to choose a different - and less than optimal - execution plan than when only the index statistics existed.</p> <p>According to the MSDN article <em><a href="http://msdn.microsoft.com/en-us/library/aa902688%28SQL.80%29.aspx">Statistics Used by the Query Optimizer in Microsoft SQL Server 2000</a></em> auto created statistics are automatically dropped over time if they are not used but that can take an undetermined amount of time. What if we're experiencing the kind of problem I previously wrote about? In most of the cases I've seen it makes sense to help SQL Server out by manually dropping the auto created statistics in favor of index statistics that exist for the same column.</p> <p>Fortunately SQL Server contains everything we need to know to figure out when column statistics are overlapped by index statistics. The following query will identify overlapped\overlapping statistics and generate the statements you can use to drop the overlapped statistics. All the usual warnings apply here - although this has not caused any problems for me your mileage may vary so wield this with an appropriate degree of caution:</p> <pre class="brush: sql">WITH autostats(object_id, stats_id, name, column_id) AS ( SELECT sys.stats.object_id , sys.stats.stats_id , sys.stats.name , sys.stats_columns.column_id FROM sys.stats INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id AND sys.stats.stats_id = sys.stats_columns.stats_id WHERE sys.stats.auto_created = 1 AND sys.stats_columns.stats_column_id = 1 ) SELECT OBJECT_NAME(sys.stats.object_id) AS [Table] , sys.columns.name AS [Column] , sys.stats.name AS [Overlapped] , autostats.name AS [Overlapping] , 'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id) + '].[' + OBJECT_NAME(sys.stats.object_id) + '].[' + autostats.name + ']' FROM sys.stats INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id AND sys.stats.stats_id = sys.stats_columns.stats_id INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id AND sys.stats_columns.column_id = autostats.column_id INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id AND sys.stats_columns.column_id = sys.columns.column_id WHERE sys.stats.auto_created = 0 AND sys.stats_columns.stats_column_id = 1 AND sys.stats_columns.stats_id != autostats.stats_id AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0;</pre> <p>For more information on statistics see <a href="http://msdn.microsoft.com/en-us/library/ms190397.aspx" target="_blank">Using Statistics to Improve Query Performance</a> in SQL Server Books Online. I also recommend watching Introduction to SQL Server Statistics, a presentation my friend Andy Warren (<a href="http://www.sqlandy.com/" target="_blank">Blog</a> | <a href="http://twitter.com/sqlandy" target="_blank">Twitter</a>) recently gave for the <a href="http://performance.sqlpass.org" target="_blank">PASS Performance Virtual Chapter</a> which is available in their <a href="http://performance.sqlpass.org/PresentationArchive.aspx" target="_blank">Presentation Archive</a>.</p></div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Performance SQL Server 2005 SQL Server 2008 Tips Troubleshooting