Stats Date Function and Most Used Indexes

Statistics date is a useful function in retruning the date of the most recent update for a statistics object on a table or an indexed view 

SELECT ‘Index Name’ = i.name,
    ‘Statistics Date’ = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = ’employee’ AND o.id = i.id]

 

Comment out the predicate to get details of the whole database

Similarly, It can also be useful to find out the most used indexes (or least used) depending on what the investigation is looking at –

select object_name(s.object_id) as TableName, isnull(i.name,‘HEAP’) as IndexName,
case i.index_id
when 0 then ‘HEAP’
when 1 then ‘CLUS’
else ‘NC’
end as IndexType
,reads=user_seeks + user_scans + user_lookups
,writes =  user_updates
from sys.dm_db_index_usage_stats s join sys.indexes i
on s.object_id = i.object_id and i.index_id = s.index_id
where objectproperty(s.object_id,‘IsUserTable’) = 1
and s.database_id = db_id()
order by reads desc

There are many different uses for sys.dm_db_index_usage_stats The Last_User_Seek, Last_User_Update columns can be useful in identifying unused indexes

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: