DMV::Index

SQL SERVER 2005 이상

SQL 2005는 DMV를 통해서 실시간으로 인덱스의 사용빈도, 변경 횟수, lock 현황, missing 된 인덱스 정보를 제공한다.

이 자료를 바탕으로 튜닝과 관리 모니터링이 가능하다.

DMV의 정보는 마지막으로 SQL 인스턴스가 시작된 후로 누적된 값이다.

 사용하고 있는 INDEX 무엇인가?

 DDL 문을 통해 인덱스를 생성하게 되면 인덱스가 카탈로그는 업데이트 된다.

 그렇다고 이 인덱스가 "USE" 사용되는 것은 아니다.  인덱스가 select, insert, update가 될 때 sys.dm_db_index_usage_stats 에서 정보를 찾을 수 있다.

 마지막으로 SQL SERVER를 방금 재 시작 했다면 sys.dm_db_index_usage_stats 정보를 찾을 수 없을 수도 있다.

 

user_seeks : 사용자 쿼리별 검색(Seek) 수입니다.

user_scans : 사용자 쿼리별 검색(Scan) 수입니다.

user_lookups :  사용자 쿼리별 책갈피 조회 수입니다.

user_updates :  사용자 쿼리별 업데이트 수

 

사용하지 않는 테이블 & 인덱스

1. -- unused tables & indexes.

2. DECLARE @dbid INT

3. SET @dbid = DB_ID('AdventureWorks')

4.  

    

5. SELECT OBJECT_NAME(IDX.object_id) as object_name,

6.        IDX.name AS index_name,

7.        CASE WHEN IDX.type = 1 THEN 'Clustered'

8.           WHEN IDX.type = 2 THEN 'Non-Clustered'

9.           ELSE 'Unknown' END Index_Type

10. FROM sys.dm_db_index_usage_stats  AS DIS

11.       RIGHT OUTER JOIN sys.indexes AS IDX  ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id

12.       JOIN sys.objects AS OBJ  ON IDX.object_id = OBJ.object_ID

13. WHERE  OBJ.type IN ('U', 'V') AND DIS.object_id IS NULL

14. ORDER BY OBJECT_NAME(IDX.object_id), IDX.name

     

 

드물게 사용하는 인덱스

 

1. DECLARE @dbid INT

2. SET @dbid = DB_ID('AdventureWorks')

3.  

4. --- rarely used indexes appear first

5. SELECT OBJECT_NAME(DIS.object_id) as object_name,

6.        IDX.name AS index_name, IDX.index_id,

7.        CASE WHEN IDX.type = 1 THEN 'Clustered'

8.           WHEN IDX.type = 2 THEN 'Non-Clustered'

9.           ELSE 'Unknown' END Index_Type,

10.         DIS.user_seeks, DIS.user_scans, DIS.user_lookups, DIS.user_updates

11. FROM sys.dm_db_index_usage_stats AS DIS

12.             JOIN sys.indexes AS IDX ON DIS.object_id = IDX.object_id AND DIS.index_id = IDX.index_id

13. WHERE DIS.database_id = @dbid AND objectproperty(DIS.object_id,'IsUserTable') = 1

14.             --AND DIS.user_updates > 0 AND DIS.user_seeks = 0

                 --AND DIS.user_scans = 0 AND DIS.user_lookups  = 0  --(업데이트는 일어나는 사용되지 않은것, 관리 부담만 있다.)

15. ORDER BY (DIS.user_updates + DIS.user_seeks + DIS.user_scans + DIS.user_lookups ) asc

 

인덱스에 lock 이 걸려있는 지 확인

1.  declare @dbid int
2. select @dbid = db_id()
3. Select dbid=database_id, objectname=object_name(s.object_id)
   , indexname=i.name, i.index_id       --, partition_number
   , row_lock_count, row_lock_wait_count
   , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
   , row_lock_wait_in_ms
   , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
   from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,   sys.indexes i
   where objectproperty(s.object_id,'IsUserTable') = 1
   and i.object_id = s.object_id
   and i.index_id = s.index_id
   order by row_lock_wait_count desc

 

실시간 LOCK 확인 -- 다른 LOOK 쿼리 확인. 구분 이해하기

위에 부분은 실시간으로 처리되는 것이 아니라 누적된 결과를 보는 쿼리 이다. 실시간으로 락이 걸려있는 상태를 확이낳려면 프로시저를 생성하고 처리 결과를 보는것도 좋은 방법이다.

1. create proc sp_block_info
   as
   set rowcount ON
   select t1.resource_type as [lock type]
               ,db_name(resource_database_id) as [database]
               ,t1.resource_associated_entity_id as [blk object]
               ,t1.request_mode as [lock req]                                          --- lock requested
               ,t1.request_session_id as [waiter sid]                                  --- spid of waiter
               ,t2.wait_duration_ms as [wait time]        
               ,(select text from sys.dm_exec_requests as r                           --- get sql for waiter
                           cross apply sys.dm_exec_sql_text(r.sql_handle)
                           where r.session_id = t1.request_session_id) as waiter_batch
               ,(select substring(qt.text,r.statement_start_offset/2,
                                       (case when r.statement_end_offset = -1
                                       then len(convert(nvarchar(max), qt.text)) * 2
                                       else r.statement_end_offset end - r.statement_start_offset)/2)
                           from sys.dm_exec_requests as r
                           cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
                           where r.session_id = t1.request_session_id) as waiter_stmt    --- statement blocked
                ,t2.blocking_session_id as [blocker sid]                                 -- spid of blocker
        ,(select text from sys.sysprocesses as p                                         --- get sql for blocker
                           cross apply sys.dm_exec_sql_text(p.sql_handle)
                           where p.spid = t2.blocking_session_id) as blocker_stmt
               from
               sys.dm_tran_locks as t1,
               sys.dm_os_waiting_tasks as t2
   where  t1.lock_owner_address = t2.resource_address
   set rowcount OFF
   go

 

---

 SEE ALSO : http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx