Systems Engineering and RDBMS

Handy Index Stats Information Script for SQL Server 2005

Posted by decipherinfosys on February 12, 2008

Here is a SQL Script for getting information on Indexes and Statistics in SQL Server 2005:

select
object_name(ss.object_id) as TABLE_NAME,
coalesce(si.type_desc, ‘***NO INDEX***’ ) as INDEX_TYPE,
case si.is_unique when 1 then ‘Y’ else ‘N’ end as UNIQUE_INDEX,
case si.is_primary_key when 1 then ‘Y’ else ‘N’ end as PRIMARY_KEY,
case si.is_unique_constraint when 1 then ‘Y’ else ‘N’ end as UNIQUE_CONSTRAINT,
ss.name as STATISTIC_NAME,
case when si.name is not null then stats_date(t.object_id, si.index_id)
else stats_date(t.object_id, ss.stats_id)
end as LAST_UPDATE_STATS,
stuff((SELECT ‘, ‘ + c.name
FROM sys.stats_columns as sc
inner join sys.columns as c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
WHERE ss.object_id = sc.object_id
and ss.stats_id = sc.stats_id
ORDER BY c.column_id FOR XML PATH(”)), 1, 1, ”) as INDEX_COLUMNS,
case auto_created when 1 then ‘Y’ else ‘N’ end as STAT_AUTO_CREATED,
case user_created when 1 then ‘Y’ else ‘N’ end as STAT_CREATED_BY_USER
from sys.stats as ss
inner join sys.tables as t
on object_name(ss.object_id) = t.name
left outer join sys.indexes as si
on ss.name = si.name
where t.is_ms_shipped = 0
order by t.name, coalesce(si.index_id, 999)

The _WA_Sys_xxx statistics that you see will be the statistics that are getting created automatically by SQL Server. For that to happen, the auto_create_statistics option needs to be ON. And subsequently, whenever a column or a set of columns (that are not indexed) are involved in a WHERE clause filter criteria or are involved as part of the join in the ON clause, SQL Server will automatically create statistics for those un-indexed columns.

Such statistics can help in plan optimization and tomorrow, we will post an example demonstrating how these stats on un-indexed columns can help in performance optimization.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: