Systems Engineering and RDBMS

Archive for February 12th, 2008

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.

Posted in SQL Server | Leave a Comment »

SET OPTIONS and execution plans – II

Posted by decipherinfosys on February 12, 2008

In one of our previous blog post, we had covered the importance of the SET options and how they can effect the usage/non-use of the indexed views in the system. Another important thing to mention is that you should always SET these options at the connection time when connecting from the application and ensure that they do not change during the duration of that connection. If the application instead makes these changes as part of a batch, then recompilations will occur and will you will see recompiles when tracing out those sessions or monitoring through the DMVs/DMFs. You can read more in this MSFT whitepaper (search for SET Options):

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Posted in SQL Server | 1 Comment »