I was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command, but I remembered that this is marked to be deprecated in future. Again, using DMV is the right thing to do moving forward. I quickly wrote following script which gives a lot more information than sp_helpstats.
-- Details About Statistics
-- Original Author: Pinal Dave
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc
ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);
If you have a better script to retrieve information about statistics, please share here and I will publish it with due credit.
If you need further help with this script, do leave a comment or send me email at my email address.
Reference: Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Thanks a lot Pinal for the Script…
I knew only about sp_helpstats….
Hi Pinal,
Your query gives a whole lot of information about statistics.
The one which i was using always was
sp_msforeachtable “sp_autostats ‘?'”
This wont give all the info that your script gives, but most of the time I am only interested in knowing last updated and whether auto stats is enabled.
Thanks for the script , it is going to be handy.
Hi Pinal,
I use slightly different query using sys.index – thought to share here.
SELECT o.name AS tablename ,
i.name AS indexname ,
i.type_desc AS indextype ,
STATS_DATE(i.[object_id], i.index_id) AS statisticsdate
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.[object_id] = o.[object_id]
WHERE o.TYPE = ‘U’ –Only get indexes for User Created Tables
AND i.name IS NOT NULL
ORDER BY o.name ,i.TYPE
It doesn’t give the column name and the other details though – like yours.
– Regards
Sabya
Hello pinal ….i’m in love with ur blog….i’m a new database admin and learning very fast from ur blog…..hope to help other newbies one day.
Regards
Wakil
hey i am new too. i saw your comment on a blog site. i was wondering where you got your training at. what videos or websites or examples did you use to learn experience/
Thank you
Nice pinal
The above script does not work in SQL2000. Any other query I can try in SQL2000 ?
@Amir, and others, you could start with this and then build it up as you desire:
select c.name, o.name, t.name from syscolumns c
INNER JOIN sysobjects o on c.id = o.id and o.xtype = ‘U’
INNER JOIN systypes t on c.xusertype = t.xusertype
order by o.name, c.name
Pinal
I started with your query and changed it a bit. I do not know why you join to sys.partitions.Instead of sys.objects I use sys.tables.
SELECT
t.name AS TableName,
c.name AS ColumnName,
s.name AS StatName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
s.has_filter,
s.filter_definition,
s.auto_created,
s.user_created,
s.no_recompute
FROM sys.stats s
JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.tables t ON s.[object_id] = t.[object_id]
WHERE t.is_ms_shipped = 0
ORDER BY t.name,s.name,c.name;
Is there a way to find out how often a statistic is used? similar to the DMV that allows you to get usage information for a statistic.
Is there a right time or method to remove auto created statistics ?
DBCC SHOW_STATISTICS (KRM_ACCR_INT,KRM_ACCR_INT_uidx1) this will rock if we are useing for specific Table index.
Hi Pinal
I had a need to script out stats from rather a lot of databases with many tables, and couldn’t see an easy way to do this other than generate scripts and then trimming out all of the stuff I didn’t need.
I googled and while there are scripts to do this for indexes, there are non around for stats.
On finding you post I decided that spending the time to create such a script using yours as a basis would be better than doing the above generate scripts and and trim out the unneeded stuff.
I thought I would share the script here for the benefit of anyone else who might ever need to do the same thing.
I’m sure there are further tweaks that can be made but this suited the needs we had.
DECLARE @script AS VARCHAR(2000),
@tableName2 AS VARCHAR(100),
@statName AS VARCHAR(100),
@column AS VARCHAR(100),
@last AS BIT,
@dbName2 AS VARCHAR(100)
DECLARE StatsCursor CURSOR FOR
SELECT DISTINCT
‘DROP STATISTICS [‘ + sch.name + ‘].[‘ + obj.name +’].[‘ + S.name + ‘]’ AS Script
FROM sys.stats s
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,’IsUserTable’) = 1
AND (s.user_created = 1)
OPEN StatsCursor
FETCH NEXT FROM StatsCursor INTO @script
SET @dbName2 = (SELECT DB_NAME())
PRINT ‘–SERVER: ‘ + @@SERVERNAME
PRINT ‘USE ‘ + @dbName2
PRINT ‘GO’
PRINT ”
PRINT ‘———- Drop STATS ———-‘
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @script
PRINT ‘GO’
FETCH NEXT FROM StatsCursor INTO @script
END
CLOSE StatsCursor
DEALLOCATE StatsCursor
PRINT ”
PRINT ‘———- Create STATS ———-‘
DECLARE StatsCursor CURSOR FOR
SELECT DISTINCT
‘CREATE STATISTICS [‘ + S.name + ‘] ON [‘ + sch.name + ‘].[‘ + obj.name +’]’ AS Script,
OBJECT_NAME(s.[object_id]) AS TableName,
s.name AS StatName
FROM sys.stats s
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,’IsUserTable’) = 1
AND (s.user_created = 1)
ORDER BY Statname, TableName
OPEN StatsCursor
FETCH NEXT FROM StatsCursor INTO @script, @tableName2, @statName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @script = @script + ‘(‘
SET @last = 1
DECLARE ColumnCursor CURSOR FOR
SELECT
c.name
FROM
sys.stats s JOIN
sys.stats_columns sc JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
ON sc.[object_id] = s.[object_id]
AND sc.stats_id = s.stats_id JOIN sys.objects o ON c.[object_id] = o.[object_id]
WHERE
s.name = @statName AND o.[name] = @tableName2
ORDER BY stats_column_id
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @script = @script + ‘[‘ + @column + ‘],’
FETCH NEXT FROM ColumnCursor INTO @column
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
SET @script = LEFT(@script,LEN(@script)-1)
SET @script = @script + ‘)’
PRINT @script
PRINT ‘GO’
FETCH NEXT FROM StatsCursor INTO @script, @tableName2, @statName
END
CLOSE StatsCursor
DEALLOCATE StatsCursor
This query provides more information about that statistics in addition to the table schema. It allows you to be able to determine if a statistic is out of date.
SELECT ss.name AS SchemaName
, obj.name AS TableName
, stat.stats_id
, stat.name AS StatiscticsName
, stat.filter_definition
, sp.last_updated
, sp.[rows]
, sp.rows_sampled
, sp.steps
, sp.unfiltered_rows
, sp.modification_counter
FROM sys.objects AS obj
INNER JOIN sys.schemas ss ON obj.schema_id = ss.schema_id
INNER JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.is_ms_shipped = 0
ORDER BY ss.name, obj.name, stat.name
This is exactly what I needed. Thank you!
Thanks John.
Thank you. Saved me a lot of grief when the vendor tried to put the blame on us for creating extra statistics when I could prove that they were all auto created. Squashed that line of thinking real quick! I came up with a query like John’s. I found a web page where MS recommended the modification_counter value be 1000 and use that value along with looking at stale statistics (anything over 7 days for last_updated) along with rows rows_sampled when I rebuild statistics. It is more efficient than rebuilding all statistics every day. I do this to meet a particular vendor’s maintenance requirements. The rows rows_sampled is dependent upon vendor requirements, along with the size and types (heap vs regular) of tables. Statistics for really large tables may be better off with the default smaller row sample rate as they will take less time to rebuild.
Good Morning.
No words to thank for the immense help.
Thank you for sharing this jewel.
Thank you very much here Brazil.
Hugs
Guys, can some one help me in troubleshooting cpu utilization in MSSQL. i am new to MSSQL.