인덱스 사용률_특정 기간

1. Take a snap shot of the information prior to running your test. This will be our baseline.

/* Powered by General SQL Parser (www.sqlparser.com) */

DECLARE  @dbid INT

SELECT @dbid = Db_id()

SELECT   objectname = Object_name(s.object_id),
         s.object_id,
         indexname = i.name,
         i.index_id,
         user_seeks,
         user_scans,
         user_lookups,
         user_updates
INTO     beginning_data
FROM     sys.dm_db_index_usage_stats s,
         sys.indexes i
WHERE    database_id = @dbid
         AND Objectproperty(s.object_id,'IsUserTable') = 1
         AND i.object_id = s.object_id
         AND i.index_id = s.index_id
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

2. Run the test scenario/script.

3. Then run the same script again, just to a different table.

/* Powered by General SQL Parser (www.sqlparser.com) */

DECLARE  @dbid INT

SELECT @dbid = Db_id()

SELECT   objectname = Object_name(s.object_id),
         s.object_id,
         indexname = i.name,
         i.index_id,
         user_seeks,
         user_scans,
         user_lookups,
         user_updates
INTO     ending_data
FROM     sys.dm_db_index_usage_stats s,
         sys.indexes i
WHERE    database_id = @dbid
         AND Objectproperty(s.object_id,'IsUserTable') = 1
         AND i.object_id = s.object_id
         AND i.index_id = s.index_id
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

4. Next run a script to calculate the difference.

/* Powered by General SQL Parser (www.sqlparser.com) */

SELECT a.objectname,
       a.indexname,
       b.user_seeks - a.user_seeks     'delta_seeks',
       b.user_scans - a.user_scans     'delta scans',
       b.user_lookups - a.user_lookups 'delta lookups',
       b.user_updates - a.user_updates 'delta updates'
FROM   beginning_data a,
       ending_data b
WHERE  a.object_id = b.object_id
       AND a.index_id = b.index_id