Thursday, April 7, 2011

index usage and physical stats

select * from sys.dm_db_index_usage_stats where object_id=1779185784 will give you index usage stats like user seeks, scans, laster user seek , last lookup, last system scan etc.
this will help you in identifying which of the indexes in the table are in use since your last sql server restart.

select * from sys.dm_db_index_physical_stats(5,1779185784,1,1,'detailed')
                                                                (dbid,obj id, index id, partition no,options)
options - detailed, sample,limited,null

This will give you the fragmentation percentage much like dbcc showcontig, using this info we can identify if we need to rebuild that particular indexe

select * from sys.dm_db_index_operational_stats(5,1779185784,null,null)
                                                                     (dbid,obj id, indx id, partion no)

This will give details like leaf delete count, leaf insert count, leaf update count, page lock count, row lock count etc.
using this we can identify how many times inserts,updates and deletes happened for this table and how many different types of locks have been acquired on these indexes.

No comments:

Post a Comment