Home > Database Administration, Denali, SQL Server 2008 > SQL Server: Find statistics information in database

SQL Server: Find statistics information in database

Recently I worked on a performance tuning of a database and noticed a lot of user created statistics. I checked few of them and saw that lots of user created statistics are multi-column statistics and some of them have a filter defined. There was no documentation of created statistics and I wanted to find out all statistics with all defined columns and filter. That is easy with sys.stats, but for better visibility, I wanted to list all statistics columns and defined filter in a single concatenated string.
Below script will return all statistics with all columns and defined filter for all user tables and views in a database. Script works in SQL Server 2008 and Denali (SQL Server 2005 doesn’t have filtered statistics).

;WITH Stat(SchemaName, TableName, StatisticsName, StatisticsType, filter_definition, stats_column_id, name)
AS
(
SELECT sh.name AS SchemaName ,
o.name AS TableName,
s.name AS StatisticsName,
CASE
WHEN s.user_created = 1 THEN 'USER'
WHEN s.auto_created = 1 THEN 'AUTO'
WHEN s.auto_created = 0 AND s.user_created = 0 THEN 'INDEX'
ELSE 'UNKNOWN'
END AS StatisticsType,
s.filter_definition,
sc.stats_column_id,
c.name
FROM sys.stats s
INNER JOIN sys.objects o
ON o.object_id = s.object_id
INNER JOIN sys.schemas sh
ON sh.schema_id = o.schema_id
INNER JOIN sys.stats_columns sc
ON sc.stats_id = s.stats_id
AND sc.object_id = s.object_id
INNER JOIN sys.columns c
ON c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE o.type in ('U','V') -- UserTable and View
)

SELECT s.SchemaName,
s.TableName,
s.StatisticsName,
STUFF (( SELECT ', ' + s1.name
FROM Stat s1
WHERE s1.TableName=s.TableName
AND s1.StatisticsName=s.StatisticsName
ORDER BY s1.stats_column_id
FOR XML PATH('')
), 1,1, null) + CASE
WHEN s.filter_definition IS NOT NULL THEN
' FILTER: ' + s.filter_definition
ELSE ''
END AS StatisticsColumns
FROM Stat s
/* Filter statistics: USER - User-created, AUTO - automac-created, INDEX - index statistics */
-- WHERE s.StatisticsType in ('USER', 'AUTO')
GROUP BY s.SchemaName, s.TableName, s.StatisticsType, s.filter_definition, s.StatisticsName
ORDER BY s.SchemaName, s.TableName, s.StatisticsType

In the query we can filter for only automatically created and user created statistic if we want to exclude index statistics. Below is sample output for user and automatically created statistics in AdventureWorks database. (1) Show you the output for multi-column statistics with a filter defined.

If you have some suggestions for the script feel free to add comment.

Advertisements
  1. 03/11/2011 at 7:23 AM

    Great stuff. coding is very useful. Thanks for the share.

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: