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.
USE AdventureWorks
GO
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 better script to retrieve information about statistics, please share here and I will publish it with due credit.
Update: Read follow up excellent blog post by Jason Brimhall.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Thanks a lot Pinal for the Script…
I knew only about sp_helpstats….
Hi,
Very interesting article.
For people that don’t know much about DMVs, they can discover a lot more about improving SQL performance via DMVs in this new book http://www.amazon.com/gp/product/1935182730/ It contains more than 100 scripts to identify problems, and offers a wide range of solutions.
Chapters 1 and 3 can be downloaded for free at http://www.manning.com/stirk
Chapter 1 includes scripts for:
A simple monitor
Finding your slowest queries
Find your missing indexes
Identifying what SQL is running now
Quickly find a cached plan
Thanks
Ian
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 ?