Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

How to Identify Useful SQL Server Table Statistics


By:   |   Last Updated: 2016-05-18   |   Comments (5)   |   Related Tips: More > Maintenance

Problem

Have you ever wondered how many statistics objects there are in a database? Unlike other objects, statistics are often created and managed by SQL Server automatically and unless database performance starts to crawl or statistics update jobs exceed maintenance windows, we probably don't think much about this.

Some DBA's prefer to let SQL Server handle the statistics updates (by keeping the 'auto update statistics' option on) while others prefer to update them manually or have a combination of auto and manual updates. Regardless of what approach you choose, as your database grows, statistics maintenance will require more time and resources and there will be a point at which you're faced with questions like: "Are all my statistics useful?, Which statistics are used frequently? Which ones could possibly be dropped?".

In this tip I'll describe different approaches to maintain statistics and show how you can use the data from your servers for intelligent statistics updates.

Solution

The Importance of SQL Server Statistics

Statistics are one of the most important factors that impacts SQL Server performance. The topic of statistics is well described on the web and I've provided links to some articles at the end of this tip.

For those of you who are not familiar with statistics here are some key points, which will help with understanding this topic:

  • SQL Server statistics contain statistical information about the distribution of data in your tables and is always specific to certain columns. The SQL Server Query Optimizer uses this information when creating execution plans to arrive at optimal performance.

  • There are two types of statistics: index statistics and column statistics. Index statistics are created automatically, when we create indexes and include columns specific to that index. Column statistics can be created manually or automatically if the AUTO_CREATE_STATISTICS option of your database is turned on. By default this setting is on, which allows SQL Server to create useful statistics when required and it's recommended to keep this setting on.

  • Statistics could be updated manually (by running scripts or maintenance plans) or automatically, if the AUTO_UPDATE_STATISTICS setting of your database is turned on (it is on by default).

  • If the AUTO_UPDATE_STATISTICS property for a database is turned on, statistics would be updated when certain conditions are met such as a number of data changes since the last update, etc.

  • Statistics updates could be done either on the full or partial data set. When a partial update is selected (sampling) a percentage of rows against entire data volume could be specified. If the 'sampling' parameter is not specified, by default SQL Server will choose a full update for small tables and partial updates for large tables.

  • If full statistics update is not feasible for certain tables (say due to size constraints) it's preferable to provide as high a sampling as possible when updating statistics. This will ensure that statistics reflect accurate, up to date distribution of your data and would lead to better performance. I've seen cases where SQL Server has created sub-optimal execution plans, because certain statistics have been partially updated.

SQL Server Statistics Update Challenges

As mentioned above, there are a few approaches for updating statistics and unfortunately each one has its challenges.

Here are some of these challenges:

Automatic Update SQL Server Statistics

With AUTO_UPDATE_STATISTICS setting on, statistics would be updated when:

  • Table with less than 500 rows has an increase of 500 rows or more since the last update
  • Table with over 500 rows has an increase of 500 rows + 20% of the table size since the last update

This logic may work well for relatively small tables, however for larger tables it may require a lot of changes, before it's marked for update. As you can see from this article, with SQL Server 2008 R2 - SP 1 and later versions you can lower this threshold by applying Trace Flag 2371. This will ensure that query plans use more accurate statistics and perform better. However, as update statistics requires large scans on the underlying tables, they may consume significant server resources and impact TempDB in some cases.  Take a look at this article for more details about the process running in the background which may be impacting performance.

Manual Update

With this approach you have more controls on the timing, sampling and intensity of statistics update. However, you'd be restricted to the maintenance window, because running intensive statistics updates may seriously impact business applications. As your tables grow, some compromises would need to be made to fit into a maintenance window, like:

  • Reducing update sampling by either providing a low sampling or skip the sampling parameter and let SQL Server choose the sampling. The problem with this approach is the SQL Query Optimizer may not choose statistics with low sampling and therefore create sub-optimal execution plans, as I've mentioned above.
  • Update statistics for a limited number of tables in each session. There are a few popular conditions for choosing best statistics candidates, such as using the STATS_DATE() function to get last update date/time for each statistic, while others prefer using the number of updates on the underlying table columns since the last statistics update.  You can get this number by querying the modification_counter column from sys.dm_db_stats_properties system function available on SQL Server 2008 R2 and above.

There's no simple answer for the problems I've described above and these challenges grow as your database grows.

Are all my SQL Server statistics really useful?

The short answer is no, as I mentioned above. When AUTO_CREATE_STATISTICS option is on, SQL Server will create statistics when it thinks it is beneficial for the query. Over time your database will collect many obsolete statistics, which were created a long time ago for queries with a single use purpose or queries which are no longer in use. Unfortunately it's not easy to find usage frequency for existing statistics. So, some people suggest dropping all column statistics from time to time and let SQL Server create new statistics, see this article for more info. Although this seems to be a reasonable approach, some DBA's would hesitate dropping all statistics in a production environment as this can temporarily impact query performance. So, the approach described below is based on real knowledge collected from your server.

How to identify useful SQL Server statistics?

With SQL Server 2012 and later versions you can use Trace Flag 8666 to obtain some debugging information from the SQL Server query cache which includes the names of the statistics used within each execution plan, see this article for more information. So, if we can collect execution plans for some period, we can get usage frequency of statistics and make intelligent decisions on cleaning up obsolete statistics or maintaining useful statistics.

In this previous tip, I discussed a simple query collection tool which could be used for statistics analysis purposes. Here is the link to download the stored procedure discussed below.

Here are the steps required to achieve this:

The stored procedure CollectQueryStats includes statistics as part of the execution plans, if @vCollectStatsInfo parameter is set to 'True'. That would enable Trace Flag 8666 for the duration of this procedure. The procedure checks the SQL Server version and enables the flag only if it's SQL Server 2012 or later. Please also note that this flag is undocumented. I've used it with SQL Server versions 2012 and 2014 and didn't have any problems. This will allow you to collect query statistics for a certain period from your production server if the @vCollectStatsInfo parameter is set to 'True'.

Transfer the PerfStats database into a non-production server and enable these XML indexes by executing the following script on that server:

USE [PerfStats]
GO
ALTER INDEX [PXML_QueryPlans] ON [dbo].[QueryPlans] REBUILD 
ALTER INDEX [IXML_QueryPlans_Path] ON [dbo].[QueryPlans] REBUILD 
ALTER INDEX [IXML_QueryPlans_Value] ON [dbo].[QueryPlans] REBUILD 
GO

Execute the below query to get total number of statistics calls:

USE [PerfStats]
GO
;WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
,GetStatsCTE As (
SELECT DISTINCT
cp.SqlHandle,
cp.PlanHash,
cp.DbName, 
schnds.value('(@FieldValue)[1]', 'varchar(128)') AS Schema_Name, 
tblnds.value('(@FieldValue)[1]', 'varchar(128)') AS Table_Name, 
obj.value('(@FieldValue)[1]', 'varchar(128)') AS Stats_Name, 
cp.ExecutionCount
FROM vQueryStats_Plans AS cp 
CROSS APPLY cp.queryplan.nodes('//Recompile/Field[@FieldName="wszTable"]') AS StatNodes(tblnds) 
CROSS APPLY tblnds.nodes('../Field[@FieldName="wszSchema"]') AS SchNodes(schnds) 
CROSS APPLY tblnds.nodes('../ModTrackingInfo') AS TblNodes(stnds) 
CROSS APPLY stnds.nodes('./Field[@FieldName="wszStatName"]') AS vidx(obj) 
Where MinElapsedTime_Msec >50
) 
Select DbName,Schema_Name,Table_Name,Stats_Name,Sum(ExecutionCount) As TotalScanCount 
FROM GetStatsCTE Where Schema_Name<>'sys' Group by DbName,Schema_Name,Table_Name,Stats_Name 

Here is what my results look like and from here we can see what is being used.

Query output
Next Steps


Last Updated: 2016-05-18


next webcast button


next tip button



About the author
MSSQLTips author Fikrat Azizov Fikrat Azizov has been working with SQL Server since 2002. Hes currently working as Business Intelligence consultant at Avanade Canada.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, February 25, 2017 - 2:45:47 PM - Greg Robidoux Back To Top

The tip has been updated to show trace flag 8666.

-Greg


Saturday, February 25, 2017 - 2:23:21 PM - Fikrat Back To Top

 Yes, this was meant to be flag 8666, sorry for typo.I'll fix the error.
Thanks you!

 


Thursday, February 23, 2017 - 7:24:00 AM - Konstantin Back To Top

 Hi, Fikrat.

 

Thanks for nice article. Do you mean undocumented Trace Flag 8666 (in article 8661)?

 


Wednesday, February 22, 2017 - 7:54:35 PM - Jeff Moden Back To Top

Haven't done a deep dive on your code yet but I'm pretty sure that it should be Trace Flag 8666 instead of 8661.


Sunday, May 22, 2016 - 5:55:10 AM - Brahma Back To Top

 Excellent! !!

 


Learn more about SQL Server tools