Post List

레이블이 SQL Server인 게시물을 표시합니다. 모든 게시물 표시
레이블이 SQL Server인 게시물을 표시합니다. 모든 게시물 표시

2025년 6월 24일 화요일

[Performance Tuning] 성능의 기본 7원칙

Contents

1 제 1 원칙 : 초기 연결점의 유지 원칙(Fixing-Point Principle)
2 제 2 원칙 : 가까운 자원 사용 원칙(Locality-Design Principle)
3 제 3 원칙 : 처리와 빈도의 상관원칙(Processing vs Frquency Trade off Principle)
4 제 4 원칙 : 자원공유원칙(Shared-Resource Princlple)
5 제 5 원칙 : 병렬처리원칙(Parallel Processing Principle)
6 제 6 원칙 : 집중화 원칙(Centering Principle)
7 제 7 원칙 : 설비활용 원칙(Instrumenting Principle)



이 글은 소프트웨어 성능공학(SPE, Software Performance Engineering)의 전문가인 스미스 박사(Connie U. Smith)의 저서 ‘Performance Engineering of Software System’의 시스템 성능과 관련된 의사결정에 결정적인 근거를 제공해 주는 7가지 기본원칙이다.


[edit]

1 제 1 원칙 : 초기 연결점의 유지 원칙(Fixing-Point Principle) #

“신속한 응답을 위하여 가능한 빠른 시간에 연결을 시작하여 지속적으로 유지하는 것이 비용이 효과적이다.”

  • 동적 SQL을 제거하라
  • 관련된 SQL문장을 Stored Prodedure로 대치하라
  • 빈번히 반복적으로 수행되는 메뉴나 폼을 범용사용자 인터페이스를 사용하지 말고 전용으로 별도로 구성하라.
  • DB 서버와의 네트워크 액세스를 최소화하기 위해 필요한 경우 테이블을 클라이언트 PC로 다운로드 한다.
  • 디스크 I/O를 최소화를 위해 자주 사용되는 테이블과 인덱스를 메모리 안으로 버퍼링한다.
  • DB 비정규화를 준비한다.
  • 경영자 정보시스템을 위한 DB는 별도로 준비한다.

[edit]

2 제 2 원칙 : 가까운 자원 사용 원칙(Locality-Design Principle) #

“물리적으로 가까운 거리의 컴퓨터 자원을 최우선적으로 사용하여 결과를 구하라”

  • 모든 사용자 인터페이스 로직은 클라이언트 PC에서 수행하라
  • 표준적인 DB 처리를 위한 내장 프로시저는 Db서버에서 실행토록 하라
  • 실행 도중 서로 다른 지역에 위치한 DB로부터 데이터를 조합하는 프로그램을 가급적 피하라.
  • 사용자와 가까운 곳으로 데이터를 분산 배치하기 위한 방법으로 체계적인 데이터 중복구조(Replication Schemes)를 사용하라
  • 데이터 처리 작업의 85% 이상이 지역의 데이터를 사용하도록 설계목표를 정하라

[edit]

3 제 3 원칙 : 처리와 빈도의 상관원칙(Processing vs Frquency Trade off Principle) #

“빈번히 수행되는 작업의 처리시간을 최소화하도록 노력하라”

  • 개발 초기 단계에 작업별 실행빈도를 분석해 두어라
  • 클라이언트와 서버간의 LAN 전송횟수를 최소화하도록 프로그램을 설계하라
  • 서버와 서버간의 WAN 전송횟수를 최소화하도록 데이터를 분산하라
  • 다수의 SQL 요구 대신 내장 프로시저를 사용하라

[edit]

4 제 4 원칙 : 자원공유원칙(Shared-Resource Princlple) #

“가능한 한 공유될 수 있는 형태로 자원을 사용하라. 독립적인 사용이 필요한 경우에도 자원 점유시간 및 대기시간을 최소화하라”

  • 서로 다른 지역에 위치한 데이터의 동질성을 유지하기 위하여 2단계 커밋(2 Phase Commit)을 사용하지 말고 데이터중속/복사 구조를 활용하라
  • 처리능력이 상대적으로 열악한 공유자원(서버디스크, WAN)의 점유시간을 최소화하도록 프로그램을 설게하라
  • 신속한 응답을 요구하는 온라인 작업과 장시간의 처리를 수행하는 일괄작업은 별도의 서버로 분리하라

[edit]

5 제 5 원칙 : 병렬처리원칙(Parallel Processing Principle) #

“병렬처리는 처리사간의 단축이 커뮤니케이션 상의 추가적인 부담과 기타 자원의 경합에 따른 지연을 상쇄할 수 있는 경우에만 선택하라”

  • 서버에서 병렬처리의 과도한 개시와 종료를 반복하지 마라.
  • Top End, TUXIDO, ENCNA등의 TP모니터를 이용하여 자원사용에 대한 우선 순위 관리를 맡기는 것도 좋은 방법이다.
  • 병렬처리를 위한 DBMS제품과 하드웨어 제품의 겸용성을 확인하라.

[edit]

6 제 6 원칙 : 집중화 원칙(Centering Principle) #

“시스템 부하를 가중시키는 핵심적인 처리작업을 선별하여 그들의 처리시간 단축에 관심을 집중하라. –80/20 규칙을 기억하라”

  • 처리 우선순취가 높은 프로그램
  • 규모가 매우 큰 프로그램
  • 신속한 응답을 요구하는 프로그램
  • 프로그램 내에서는 처리가능의 핵심 부분에 관심을 집중하여 가장 심각하게 문제가 되는 자원을 먼저 튜닝한다.

[edit]

7 제 7 원칙 : 설비활용 원칙(Instrumenting Principle) #

“작업 부하, 자원 요구 정도와 성능목표 달성 여부 등을 측정 보완할 수 있는 설비를 갖추고 이를 활용하라”

  • 문제가 있는 프로그램이나 시스템 자원을 파악하기 위해 모델링 기법을 이용하라
  • 어디서 병목현상이 나타나는지 알고 있다고 가정하지 마라
  • 클라이언트/서버 분산 시스템에 활용 가능한 감시용 소프트웨어가 있을 것이라고 가정하지 마라(대행 시스템 벤더들은 클라이언트/서버 솔루션의 지원에 적극적이지 않다.)


시간: 댓글 없음:
라벨:

[Performance Tuning] Performance Health Status - DMV(2005)

Contents

1 Wait Type
2 I/O(읽기)
3 I/O(쓰기)
4 데이터베이스별 인덱스 누락수
5 모든 데이터베이스에 대해 가장 비용이 높은 누락된 인덱스
6 가장 비용이 높은 사용되지 않는 인덱스
7 사용되고 있는 인덱스 중 기본 테이블을 변경할 때 비용이 가능 높은 인덱스
8 자주 사용되는 인덱스
9 논리적으로 조각화가 가장 심한 인덱스 확인
10 I/O 비용이 가장 높은 쿼리 확인
11 CPU 비용이 가장 높은 쿼리 확인
12 가장 비용이 높은 CLR 쿼리 확인
13 가장 자주 실행되는 쿼리 확인
14 가장 자주 블로킹 당하는 쿼리 확인
15 계획이 가장 적게 재사용되는 쿼리 확인
16 Retrieve Buffer Counts by Object and Index
17 Determine CPU Resources Required for Optimization
18 Retrieve Parallel Statements With the Highest Worker Time
19 Retrieve Statements with the Highest Plan Re-Use Counts
20 Retrieve Statements with the Lowest Plan Re-Use Counts
21 Determine Index Cost Benefits
22 List Indexes With the Most Contention
23 Retrieve Index Usage Statistics
24 Retrieve Tables, Indexes, Files, and File Groups Information
25 Calculate Average Stalls
26 List Rarely-Used Indexes
27 List Statements By Input/Output Usage
28 Compare Single-Use and Re-Used Plans
29 List Statements By Plan Re-Use Count
30 List Real Time Tempdb Task Usage
31 List Real-Time Tempdb Statements
32 Retrieve a SQL Statement with a Specified .SQL_Handle
33 List Runnable Queues
34 List Recompiled Statements
35 List Currently-Executing Parallel Plans
36 List Cached Plans Where Worker Time Exceeds Elapsed Time
37 List Real-Time Blocker and Waiter Statements
38 Report Blocker and Waiter SQL Statements
39 Compare Signal Waits and Resource Waits
40 List Currently-Executing Statements
41 List Scheduler Wait List Information
42 List Schedulers, Workers, and Runnable Queues
43 List Session and Scheduler ID Information
44 List SQLOS Execution Model Information
45 List Statements from a Specified Waiter List
46 마지막으로 통계한 날짜
47 현재 Buffer의 Clean/Dirty Page
48 객체의 메모리 및 디스크 사용 모니터링
49 Cache Object Monitoring Script
50 Source Code Download 및 참고자료



[edit]

1 Wait Type #

SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 
               / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
ORDER BY wait_time_ms DESC;
track_waitstats_2005.sql
get_waitstats_2005.sql



Wait Type원인해결
OLEDBOLE DB 공급자호출 (4 Part Name 호출, 원격프로시저호출, OPENQUERY, OPENROWSET, DMV호출, 프로파일러추적)OLE DB를 사용한 어플리케이션, 쿼리튜닝
CXPACKET병렬처리로 인한 대기병렬처리를 발생시키는 쿼리 튜닝, 누락된 인덱스 추가
PAGEIOLATCH_*, PAGELATCH_*PAGEIOLATCH_*: 데이터 페이지의 I/O가 완료할 때까지 대기
PAGELATCH_* : 동일 페이지 Insert경합, 자동증가, 페이지 분할등		메모리증설, 쿼리튜닝, 인덱스 추가, 디스크 증설, 데이터파일 추가
WRITELOG, IO_COMPLETION비-데이터 페이지(트랜잭션 로그등) 의 IO가 완료할 때까지 대기 * IO의분산, IO 대역폭의 추가, 트랜잭션 로그 드라이브 분리
LCK_*다른 세션에 의해 개체(행, 페이지, 테이블)의 잠금 설정공유 잠금에 대해서는 트랜잭션 격리수준 조정, 트랜잭션의 지속시간 최소화
Latch_*버퍼 페이지를 제외한 나머지 내부 캐시의 경합해결을 위한 동기화 개체메모리 압박
CMEMTHREAD동시에 여러 개의 쿼리 실행하여, 메모리 할당 곤란AdHoc Query 최소화
RESOURCE_SEMAPHORE쿼리를 실행하기 위한 메모리 부족메모리증설, 대량 메모리요청 작업(해싱, 정렬등) 쿼리튜닝
- http://support.microsoft.com/kb/822101
- http://msdn.microsoft.com/ko-kr/library/ms179984.aspx
[edit]

2 I/O(읽기) #

SELECT TOP 10 
        [Total Reads] = SUM(total_logical_reads)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

[edit]

3 I/O(쓰기) #

SELECT TOP 10 
        [Total Writes] = SUM(total_logical_writes)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;

[edit]

4 데이터베이스별 인덱스 누락수 #

SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

[edit]

5 모든 데이터베이스에 대해 가장 비용이 높은 누락된 인덱스 #

SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

[edit]

6 가장 비용이 높은 사용되지 않는 인덱스 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
        -- 기타 유용한 필드를 아래에 나열
        --, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempUnusedIndexes 
SELECT TOP 10    
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND i.name IS NOT NULL    -- HEAP 인덱스 무시
ORDER BY user_updates DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
-- 임시 테이블 정리
DROP TABLE #TempUnusedIndexes

[edit]

7 사용되고 있는 인덱스 중 기본 테이블을 변경할 때 비용이 가능 높은 인덱스 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempMaintenanceCost
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_updates + system_updates) > 0 – 활성 행에 대해서만 보고
    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempMaintenanceCost 
SELECT TOP 10
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- HEAP 인덱스 무시
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_updates + system_updates) > 0 -- 활성 행에 대해서만 보고
ORDER BY [Maintenance cost]  DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempMaintenanceCost 
ORDER BY [Maintenance cost]  DESC
-- 임시 테이블 정리
DROP TABLE #TempMaintenanceCost

[edit]

8 자주 사용되는 인덱스 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempUsage
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 
-- 활성 행에 대해서만 보고
    AND s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempUsage 
SELECT TOP 10
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- HEAP 인덱스 무시
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 -- 활성 행에 대해서만 보고
ORDER BY [Usage]  DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
-- 임시 테이블 정리
DROP TABLE #TempUsage

[edit]

9 논리적으로 조각화가 가장 심한 인덱스 확인 #

-- 필요한 테이블 구조만 작성합니다.
-- 참고: 이 SQL은 다음 단계에서 지정된 데이터베이스 루프 내에 있어야 합니다.
SELECT TOP 1 
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.[object_id] = -999  -- 테이블 구조를 얻기 위한 임시 값
;

-- 서버의 모든 데이터베이스를 대상으로 반복합니다.
EXEC sp_MSForEachDB    'USE [?]; 
-- 테이블이 이미 있는 경우
INSERT INTO #TempFragmentation 
SELECT TOP 10
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE s.database_id = DB_ID() 
      AND i.name IS NOT NULL    -- HEAP 인덱스 무시
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC
;
'

-- 레코드 선택
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
-- 임시 테이블 정리
DROP TABLE #TempFragmentation

[edit]

10 I/O 비용이 가장 높은 쿼리 확인 #

SELECT TOP 10 
 [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
        ,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

[edit]

11 CPU 비용이 가장 높은 쿼리 확인 #

SELECT TOP 10 
 [Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

[edit]

12 가장 비용이 높은 CLR 쿼리 확인 #

SELECT TOP 10 
 [Average CLR Time] = total_clr_time / execution_count 
,[Total CLR Time] = total_clr_time 
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_clr_time <> 0
ORDER BY [Average CLR Time] DESC;

[edit]

13 가장 자주 실행되는 쿼리 확인 #

SELECT TOP 10 
 [Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;

[edit]

14 가장 자주 블로킹 당하는 쿼리 확인 #

SELECT TOP 10 
 [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time - total_worker_time 
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) 
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;

[edit]

15 계획이 가장 적게 재사용되는 쿼리 확인 #

SELECT TOP 10
 [Plan usage] = cp.usecounts
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), 
qt.text)) * 2 ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
,cp.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;

[edit]

16 Retrieve Buffer Counts by Object and Index #

select b.database_id, db=db_name(b.database_id)
		,p.object_id
		,object_name(p.object_id) as objname
		,p.index_id
		,buffer_count=count(*)
from sys.allocation_units a,
		sys.dm_os_buffer_descriptors b,
		sys.partitions p
where a.allocation_unit_id = b.allocation_unit_id
and a.container_id = p.hobt_id
and b.database_id = db_id()
group by b.database_id,p.object_id, p.index_id
order by buffer_count desc

[edit]

17 Determine CPU Resources Required for Optimization #

Select * from sys.dm_exec_query_optimizer_info
where counter in ('optimizations','elapsed time','trivial plan','tables','insert stmt','update stmt','delete stmt')

[edit]

18 Retrieve Parallel Statements With the Highest Worker Time #

SELECT TOP 50 qs.total_worker_time,
			qs.total_elapsed_time,
            SUBSTRING(qt.text,qs.statement_start_offset/2, 
			(case when qs.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else qs.statement_end_offset end -qs.statement_start_offset)/2) 
		as query_text,
		qt.dbid, dbname=db_name(qt.dbid),
		qt.objectid,
		qs.sql_handle,
		qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
where qs.total_worker_time > qs.total_elapsed_time
ORDER BY 
       qs.total_worker_time DESC

[edit]

19 Retrieve Statements with the Highest Plan Re-Use Counts #

SELECT TOP 100
        qs.sql_handle
		,qs.plan_handle
		,cp.cacheobjtype
		,cp.usecounts
		,cp.size_in_bytes  
		,qs.statement_start_offset
		,qs.statement_end_offset
		,qt.dbid
		,qt.objectid
		,qt.text
		,SUBSTRING(qt.text,qs.statement_start_offset/2, 
			(case when qs.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else qs.statement_end_offset end -qs.statement_start_offset)/2) 
		as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [dbid],[Usecounts] DESC

[edit]

20 Retrieve Statements with the Lowest Plan Re-Use Counts #

SELECT TOP 50
        cp.cacheobjtype
		,cp.usecounts
		,size=cp.size_in_bytes  
		,stmt_start=qs.statement_start_offset
		,stmt_end=qs.statement_end_offset
		,qt.dbid
		,qt.objectid
		,qt.text
		,SUBSTRING(qt.text,qs.statement_start_offset/2, 
			(case when qs.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else qs.statement_end_offset end -qs.statement_start_offset)/2) 
		as statement
		,qs.sql_handle
		,qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
and qt.dbid is NULL
ORDER BY [usecounts],[statement] asc

[edit]

21 Determine Index Cost Benefits #

/*
select 'object'=object_name(o.object_id), o.index_id
		, reads=range_scan_count + singleton_lookup_count
		, 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count 
		, 'leaf_page_splits' = leaf_allocation_count
		, 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
		, 'nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (10,NULL,NULL,NULL) o
where objectproperty(o.object_id,'IsUserTable') = 1
order by reads desc, leaf_writes, nonleaf_writes
go
*/
declare @dbid int
select @dbid = db_id('Northwind')
--- sys.dm_db_index_usage_stats
select 'object' = object_name(object_id),index_id
		,'user reads' = user_seeks + user_scans + user_lookups
		,'system reads' = system_seeks + system_scans + system_lookups
		,'user writes' = user_updates
		,'system writes' = system_updates
from sys.dm_db_index_usage_stats
where objectproperty(object_id,'IsUserTable') = 1
and database_id = @dbid
order by 'user reads' desc

select 'object'=object_name(o.object_id), o.index_id
		, 'usage_reads'=user_seeks + user_scans + user_lookups
		, 'operational_reads'=range_scan_count + singleton_lookup_count
		, range_scan_count
		, singleton_lookup_count
		, 'usage writes' =  user_updates
		, 'operational_leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count 
		, leaf_insert_count,leaf_update_count,leaf_delete_count 
		, 'operational_leaf_page_splits' = leaf_allocation_count
		, 'operational_nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
		, 'operational_nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) o
	,sys.dm_db_index_usage_stats u
where objectproperty(o.object_id,'IsUserTable') = 1
and u.object_id = o.object_id
and u.index_id = o.index_id
order by operational_reads desc, operational_leaf_writes, operational_nonleaf_writes
go

[edit]

22 List Indexes With the Most Contention #

declare @dbid int
select @dbid = db_id()
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

[edit]

23 Retrieve Index Usage Statistics #

select 
	* 
from 
	sys.dm_db_index_usage_stats 
order by 
	user_updates desc

[edit]

24 Retrieve Tables, Indexes, Files, and File Groups Information #

select 'table_name'=object_name(i.id)
		,i.indid
		,'index_name'=i.name
		,i.groupid
		,'filegroup'=f.name
		,'file_name'=d.physical_name
		,'dataspace'=s.name
from	sys.sysindexes i
		,sys.filegroups f
		,sys.database_files d
		,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
go

[edit]

25 Calculate Average Stalls #

select database_id, file_id
	,io_stall_read_ms
	,num_of_reads
	,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
	,io_stall_write_ms
	,num_of_writes
	,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
	,io_stall_read_ms + io_stall_write_ms as io_stalls
	,num_of_reads + num_of_writes as total_io
	,cast((io_stall_read_ms+io_stall_write_ms)/
         (1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc

[edit]

26 List Rarely-Used Indexes #

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
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

[edit]

27 List Statements By Input/Output Usage #

SELECT TOP 50
        (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
            SUBSTRING(qt.text,qs.statement_start_offset/2, 
			(case when qs.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else qs.statement_end_offset end -qs.statement_start_offset)/2) 
		as query_text,
		qt.dbid, dbname=db_name(qt.dbid),
		qt.objectid,
		qs.sql_handle,
		qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY 
       [Avg IO] DESC

[edit]

28 Compare Single-Use and Re-Used Plans #

declare @single int, @reused int, @total int

select @single=
	sum(case(usecounts)
		when 1 then 1
		else 0
	end),
	@reused=
	sum(case(usecounts)
		when 1 then 0
		else 1
	end),
	@total=count(usecounts)
from sys.dm_exec_cached_plans

select 
'Single use plans (usecounts=1)'= @single,
'Re-used plans (usecounts>1)'= @reused,
're-use %'=cast(100.0*@reused / @total as dec(5,2)),
'total usecounts'=@total


select 'single use plan size'=sum(cast(size_in_bytes as bigint))
from sys.dm_exec_cached_plans
where usecounts = 1

[edit]

29 List Statements By Plan Re-Use Count #

SELECT TOP 50
        qs.sql_handle
		,qs.plan_handle
		,cp.cacheobjtype
		,cp.usecounts
		,cp.size_in_bytes  
		,qs.statement_start_offset
		,qs.statement_end_offset
		,qt.dbid
		,qt.objectid
		,qt.text
		,SUBSTRING(qt.text,qs.statement_start_offset/2, 
			(case when qs.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else qs.statement_end_offset end -qs.statement_start_offset)/2) 
		as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [Usecounts] DESC

[edit]

30 List Real Time Tempdb Task Usage #

SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
 deallocated 
from sys.dm_db_session_space_usage as t1, 
(select session_id, 
   sum(internal_objects_alloc_page_count)
   			as task_alloc,
   sum (internal_objects_dealloc_page_count) as 
		task_dealloc 
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t2.session_id >50
order by allocated DESC

[edit]

31 List Real-Time Tempdb Statements #

SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
	, t3.sql_handle, t3.statement_start_offset
	, t3.statement_end_offset, t3.plan_handle
from sys.dm_db_session_space_usage as t1, 
		sys.dm_exec_requests t3,
(select session_id, 
   sum(internal_objects_alloc_page_count) as task_alloc,
   sum (internal_objects_dealloc_page_count) as task_dealloc
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
and t1.database_id = 2   --- tempdb is database_id=2
and t1.session_id = t3.session_id
order by allocated DESC

[edit]

32 Retrieve a SQL Statement with a Specified .SQL_Handle #

create proc get_sql_text (@sql_handle varbinary(64)=NULL
					,@stmtstart int=NULL
					,@stmtend int =NULL)
as

if @sql_handle is NULL 
	or @stmtstart is NULL 
	or @stmtend is NULL
begin
	print 'you must provide sqlhandle, stmtstart, and stmtend'
	return -999
end

select 
		 substring(qt.text,s.statement_start_offset/2, 
			(case when s.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else s.statement_end_offset end -s.statement_start_offset)/2) 
		as "SQL statement"
		,s.statement_start_offset
		,s.statement_end_offset
		,batch=qt.text
		,qt.dbid
		,qt.objectid
		,s.execution_count
		,s.total_worker_time
		,s.total_elapsed_time
		,s.total_logical_reads
		,s.total_physical_reads
		,s.total_logical_writes
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(s.sql_handle) as qt
where s.sql_handle = @sql_handle
and s.statement_start_offset = @stmtstart
and s.statement_end_offset = @stmtend
go
exec get_sql_text @sql_handle = 0x0300050014ba910b5a89af00bb9600000100000000000000,@stmtstart = 84,@stmtend = 210
go

--Retrieve SQL Text and XML Plans
select 
(select text from sys.dm_exec_sql_text(put_sql_handle_here)) as sql_text
,(select query_plan from sys.dm_exec_query_plan(put_plan_handle_here)) as query_plan
go

[edit]

33 List Runnable Queues #

select scheduler_id, session_id, status, command 
from sys.dm_exec_requests
where status = 'runnable'
and session_id > 50
order by scheduler_id

[edit]

34 List Recompiled Statements #

select top 25
	--sql_text.text,
	sql_handle,
	plan_generation_num,
	substring(text,qs.statement_start_offset/2, 
			(case when qs.statement_end_offset = -1 
			then len(convert(nvarchar(max), text)) * 2 
			else qs.statement_end_offset end - qs.statement_start_offset)/2) 
		as stmt_executing,
	execution_count,
	dbid,
	objectid 
from sys.dm_exec_query_stats as qs
	Cross apply sys.dm_exec_sql_text(sql_handle) sql_text
where plan_generation_num >1
order by sql_handle, plan_generation_num

[edit]

35 List Currently-Executing Parallel Plans #

select 
  qs.sql_handle, 
  qs.statement_start_offset, 
  qs.statement_end_offset, 
  q.dbid,
  q.objectid,
  q.number,
  q.encrypted,
  q.text
from sys.dm_exec_query_stats qs
	cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time > qs.total_elapsed_time

[edit]

36 List Cached Plans Where Worker Time Exceeds Elapsed Time #

select r.session_id,
	r.request_id,
	max(isnull(exec_context_id, 0)) as number_of_workers,
	r.sql_handle,
	r.statement_start_offset,
	r.statement_end_offset,
	r.plan_handle
from sys.dm_exec_requests r
	join sys.dm_os_tasks t on r.session_id = t.session_id
	join sys.dm_exec_sessions s on r.session_id = s.session_id
where s.is_user_process = 0x1
group by r.session_id, r.request_id, r.sql_handle, r.plan_handle, 
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

[edit]

37 List Real-Time Blocker and Waiter Statements #

select t1.resource_type
	,db_name(resource_database_id) as [database]
	,t1.resource_associated_entity_id as [blk object]
	,t1.request_mode
	,t1.request_session_id   -- spid of waiter
	,(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_text
	,t2.blocking_session_id  -- 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_text
	from 
	sys.dm_tran_locks as t1, 
	sys.dm_os_waiting_tasks as t2
where 
	t1.lock_owner_address = t2.resource_address
go
[edit]

38 Report Blocker and Waiter SQL Statements #

if exists (select 1 from sysobjects where name = 'sp_block_info')
	drop proc sp_block_info
go
create proc sp_block_info
as

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    
		--- this is the statement executing right now
	 ,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
go
exec sp_block_info

[edit]

39 Compare Signal Waits and Resource Waits #

Select signal_wait_time_ms=sum(signal_wait_time_ms)
	,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
	,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)
	,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / 
	  sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats

[edit]

40 List Currently-Executing Statements #

select r.session_id
		,status
		,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) 
		as query_text   --- this is the statement executing right now
		,qt.dbid
		,qt.objectid
		,r.cpu_time
		,r.total_elapsed_time
		,r.reads
		,r.writes
		,r.logical_reads
		,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

[edit]

41 List Scheduler Wait List Information #

select 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count,
	current_workers_count,
	active_workers_count,
	work_queue_count,
	load_factor
from sys.dm_os_schedulers
where scheduler_id < 255

[edit]

42 List Schedulers, Workers, and Runnable Queues #

select 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count,
	current_workers_count,
	active_workers_count,
	work_queue_count,
	load_factor,
	status
from sys.dm_os_schedulers
--where scheduler_id < 255
order by scheduler_id

[edit]

43 List Session and Scheduler ID Information #

select 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count,
	current_workers_count,
	active_workers_count,
	work_queue_count,
	load_factor,
	status
from sys.dm_os_schedulers
--where scheduler_id < 255
order by scheduler_id

select r.session_id
		,status
		,wait_type
		,r.scheduler_id
		,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) 
		as stmt_executing
		,r.sql_handle
		,qt.dbid
		,qt.objectid
		,r.cpu_time
		,r.total_elapsed_time
		,r.reads
		,r.writes
		,r.logical_reads
		,r.plan_handle
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

[edit]

44 List SQLOS Execution Model Information #

select distinct s.scheduler_id as sched
	, r.session_id as sid
	, w.exec_context_id as eid
	--, w.blocking_exec_context_id as beid
	, r.status
	, r.wait_type
	, s.runnable_tasks_count as runnable
	, s.active_workers_count as act_workers
	, s.current_workers_count as cur_workers
from sys.dm_os_schedulers s
left outer join sys.dm_exec_requests r
on s.scheduler_id = r.scheduler_id
left outer join sys.dm_os_waiting_tasks w
on r.session_id = w.session_id
where r.session_id > 50
order by s.scheduler_id
	, r.session_id
	, w.exec_context_id
	, r.status
	, r.wait_type
	, s.runnable_tasks_count
	, s.active_workers_count
go
select distinct session_id, exec_context_id, count(*)
from sys.dm_os_waiting_tasks
where session_id > 50
group by session_id, exec_context_id
order by session_id, exec_context_id

[edit]

45 List Statements from a Specified Waiter List #

select 
	    r.wait_type
		,r.wait_time
        ,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) 
		as query_text
		,qt.dbid, dbname=db_name(qt.dbid)
		,qt.objectid
		,r.sql_handle
		,r.plan_handle
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id > 50

[edit]

46 마지막으로 통계한 날짜 #

select 
	object_name(object_id) obj_name
,	name as stats_name
,    stats_date(object_id, stats_id) as statistics_update_date
from sys.stats 
where object_id > 100
go

[edit]

47 현재 Buffer의 Clean/Dirty Page #

SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'PageState',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'DatabaseName',
   COUNT (*) AS 'PageCount'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];

[edit]

48 객체의 메모리 및 디스크 사용 모니터링 #

--ref: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1334856,00.html
--귀찮아서 대충 했다.
use master;

create table #temp
(
	db_name nvarchar(255)
,	schema_name nvarchar(255)
,	table_name nvarchar(255)
,	memory_space_MB int
,	storage_space_MB bigint
,	percentage_of_object_in_memory numeric(18,2)
)

declare 
	@dbname nvarchar(500)
,	@sql nvarchar(4000)

declare cur cursor for
	select 
		name
	from sys.sysdatabases;

open cur;

fetch next from cur into @dbname
while @@fetch_status not in (-1, -2)
begin

	set @sql = '
		use ' + @dbname + ';
		select 
			db_name() dbname
		,	schema_name(sys.tables.schema_id) schemaname
		,	sys.tables.name tablename
		,	sum(a.page_id)*8/1024 as mmb
		,	sum(sys.allocation_units.data_pages)*8/1024 as smb
		,	case 
					when sum(sys.allocation_units.data_pages) <> 0 then 
						sum(a.page_id)/cast(sum(sys.allocation_units.data_pages) as numeric(18,2)) 
			end as ''obj_in_memory''
		from 
			(
			select 
				database_id, 
				allocation_unit_id, 
				count(page_id) page_id 
			from sys.dm_os_buffer_descriptors 
			group by database_id, allocation_unit_id) a
				inner join sys.allocation_units 
					on a.allocation_unit_id = sys.allocation_units.allocation_unit_id 
				inner join sys.partitions 
					on (sys.allocation_units.type in (1,3)
					and sys.allocation_units.container_id = sys.partitions.hobt_id)
					or (sys.allocation_units.type = 2 
					and sys.allocation_units.container_id = sys.partitions.partition_id)
				inner join sys.tables 
					on sys.partitions.object_id = sys.tables.object_id
					and sys.tables.is_ms_shipped = 0
		where a.database_id = db_id()
		group by schema_name(sys.tables.schema_id), sys.tables.name
		';
		insert #temp exec(@sql);
		fetch next from cur into @dbname
end

select 
	db_name
,	schema_name
,	table_name
,	sum(memory_space_MB) memory_space_MB
,	sum(storage_space_MB) storage_space_MB
,	sum(percentage_of_object_in_memory) percentage_of_object_in_memory
from #temp 
where memory_space_MB > 0
group by 
	db_name
,	schema_name
,	table_name
union all
select
	'TOTAL'
,	''
,	''
,	sum(memory_space_MB) memory_space_MB
,	sum(storage_space_MB) storage_space_MB
,	sum(percentage_of_object_in_memory) percentage_of_object_in_memory
from #temp 
where memory_space_MB > 0

drop table #temp
close cur;
deallocate cur;

[edit]

49 Cache Object Monitoring Script #

--DBCC FREEPROCCACHE
select
	c.name db_name
,	b.name object_name
,	a.objtype object_type
,	a.cacheobjtype cache_object_type
,	a.refcounts reference_counts
,	a.usecounts use_counts
,	a.pagesused pages_used
,	a.sqlbytes sql_bytes
,	a.sql
from master..syscacheobjects a left join master..sysobjects b
on a.objid = b.id left join master..sysdatabases c
on a.dbid = c.dbid
order by 1, 2, 3, 5 desc, 6 desc

[edit]

50 Source Code Download 및 참고자료 #


시간: 댓글 없음:
라벨:

[Performance Tuning] 성능측정

Contents

1 쿼리 모니터링
2 현재 cpu 사용율
3 latch wait
4 쿼리의 반환된 row수
5 버퍼 풀
6 버퍼 풀(table단위)
7 버퍼 풀(db단위)
8 실행계획보기
9 세션 모니터링
10 big wait stats
11 spin lock
12 Latency
13 IO Bottlenecks
14 Page IO Latch
15 CPU Bottlenecks
16 블록킹
17 dm_os_performance_counters
18 복구 Tracking
19 세션 모니터링
20 현재 cpu 사용율
21 Transaction/sec
22 대기유형들
23 각 데이터베이스에 대해 캐시된 페이지 수 반환
24 현재 데이터베이스의 각 개체에 대해 캐시된 페이지 수
25 데이터베이스, 파일, pagetype별 페이지수



[edit]

1 쿼리 모니터링 #

select   
    a.session_id
,   b.status
,   b.blocking_session_id
,   b.wait_type
,   wait_resource 
,   b.wait_time / (1000.0) wait_sec
,   b.cpu_time 
,   requested_memory_kb * 1.0 / 1024 / 1024 request_memory_gb
,   b.logical_reads
,   b.reads
,   b.writes
,   b.total_elapsed_time / (1000.0) elaps_sec
,   substring(c.text,(b.statement_start_offset / 2) + 1, ((case b.statement_end_offset when -1 then datalength(c.text) else b.statement_end_offset end - b.statement_start_offset) / 2) + 1) as statement_text
,   coalesce(quotename(db_name(c.dbid)) + N'.' + quotename(object_schema_name(c.objectid,c.dbid)) + N'.' + quotename(object_name(c.objectid,c.dbid)), '') as command_text
,   b.command
,   a.login_name
,   a.host_name 
,   a.program_name
,   a.last_request_end_time
,   a.login_time
,   b.open_transaction_count 
from sys.dm_exec_sessions as a
    inner join sys.dm_exec_requests as b 
        on b.session_id = a.session_id 
    cross apply sys.dm_exec_sql_text(b.sql_handle) as c
    inner join sys.dm_exec_query_memory_grants d
        on a.session_id = d.session_id
where 1=1
and b.session_id != @@spid 
order by 
    b.cpu_time desc
,   b.status
,   b.blocking_session_id
,   a.session_id


[edit]

2 현재 cpu 사용율 #

select
        max(a.cntr_value * 1.0 / b.cntr_value) cpu_usage
    from sys.dm_os_performance_counters a
        inner join sys.dm_os_performance_counters b
            on a.object_name = b.object_name
    and a.object_Name = 'SQLServer:Resource Pool Stats'
    and a.counter_name = 'CPU usage %'
    and b.counter_name = 'CPU usage % base'


[edit]

3 latch wait #

WITH Latches AS
     (SELECT
         latch_class,
         wait_time_ms / 1000.0 AS WaitS,
         waiting_requests_count AS WaitCount,
         100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
         ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
     FROM sys.dm_os_latch_stats
     WHERE latch_class NOT IN (
         'BUFFER')
     AND wait_time_ms > 0
     )
 SELECT
     W1.latch_class AS LatchClass, 
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
     W1.WaitCount AS WaitCount,
     CAST (W1.Percentage AS DECIMAL(14, 2)) AS Percentage,
     CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
 FROM Latches AS W1
 INNER JOIN Latches AS W2
     ON W2.RowNum <= W1.RowNum
 WHERE W1.WaitCount > 0
 GROUP BY W1.RowNum, W1.latch_class, W1.WaitS, W1.WaitCount, W1.Percentage
 HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
 GO

[edit]

4 쿼리의 반환된 row수 #

SELECT TOP 50 query_stats.query_hash AS "Query Hash", 
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
,total_rows,last_rows,min_rows,max_rows
FROM 
(SELECT QS.*, 
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset 
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END 
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash,total_rows,last_rows,min_rows,max_rows
ORDER BY 2 DESC;
GO

[edit]

5 버퍼 풀 #

http://feedproxy.google.com/~r/PaulSRandal/~3/1p2yduALcpE/post.aspx
-- db buffer-pool
SELECT 
   (CASE WHEN ([database_id] = 32767)
        THEN 'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
 FROM sys.dm_os_buffer_descriptors
 GROUP BY [database_id];
 GO


-- table buffer-pool
EXEC sp_MSforeachdb 
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name] 
    FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
 BEGIN
 SELECT
     ''?'' AS [Database],
     OBJECT_NAME (p.[object_id]) AS [Object],
     p.[index_id],
     i.[name] AS [Index],
     i.[type_desc] AS [Type],
     --au.[type_desc] AS [AUType],
     --DPCount AS [DirtyPageCount],
     --CPCount AS [CleanPageCount],
     --DPCount * 8 / 1024 AS [DirtyPageMB],
     --CPCount * 8 / 1024 AS [CleanPageMB],
     (DPCount + CPCount) * 8 / 1024 AS [TotalMB],
     --DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
     --CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
     ([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
     CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
 FROM
     (SELECT
         allocation_unit_id,
         SUM (CASE WHEN ([is_modified] = 1)
             THEN 1 ELSE 0 END) AS [DPCount], 
        SUM (CASE WHEN ([is_modified] = 1)
             THEN 0 ELSE 1 END) AS [CPCount],
         SUM (CASE WHEN ([is_modified] = 1)
             THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace], 
        SUM (CASE WHEN ([is_modified] = 1)
             THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
     FROM sys.dm_os_buffer_descriptors
     WHERE [database_id] = DB_ID (''?'')
     GROUP BY [allocation_unit_id]) AS buffers
 INNER JOIN sys.allocation_units AS au
     ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
 INNER JOIN sys.partitions AS p
     ON au.[container_id] = p.[partition_id]
 INNER JOIN sys.indexes AS i
     ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
 WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than 100MB
 ORDER BY [FreeSpacePC] DESC;
 END';


[edit]

6 버퍼 풀(table단위) #

http://www.sqler.com/384271#2
USE 데이터베이스;
GO
 
;WITH src AS
(
   SELECT
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id],
       p.index_id,
       au.allocation_unit_id
   FROM
       sys.partitions AS p
   INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id
   INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id]
   INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id]
       AND p.index_id = i.index_id
   WHERE
       au.[type] IN (1,2,3)
       AND o.is_ms_shipped = 0
)
SELECT
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type,
   buffer_pages = COUNT_BIG(b.page_id),
   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
   src
INNER JOIN
   sys.dm_os_buffer_descriptors AS b
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID()
GROUP BY
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type
ORDER BY
   buffer_pages DESC;

[edit]

7 버퍼 풀(db단위) #

http://www.sqler.com/384271#2
-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters 
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT 
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767 
       THEN 'Resource DB' 
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3), 
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
[edit]

8 실행계획보기 #

http://www.yangdb.com/
SELECT     
      db_name(qt.dbid) AS 'db_name'    
    , OBJECT_NAME(qt.objectid,qt.dbid) as sp_name    
    , qp.query_plan    
    , execution_count as execution_count    
    , ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS 'Calls/Second'    
    , DATEDIFF(Minute, qs.cached_time, GetDate()) AS 'Age in Cache(min)'    
    , qs.cached_time    
    , qs.last_execution_time    
    , ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0 AS 'avg_Elapsed_Time(ms)'    
    , qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'    
    , max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'    
    , ISNULL(qs.total_worker_time/qs.execution_count, 0)/1000.0 AS 'avg_worker_time(ms)'    
    , total_worker_time/1000.0/1000.0  as 'total_worker_time(sec)'    
    , qs.max_worker_time/1000.0 as 'max_worker_time(msdb)'    
    , ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'    
    , total_logical_reads as total_logical_reads    
    , qs.max_logical_reads as max_logical_reads    
    , ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'    
    , total_physical_reads as total_physical_reads    
    , qs.max_physical_reads as max_physical_reads    
    , ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'    
    , qs.total_logical_writes as total_logical_writes    
    , qs.max_logical_writes as max_logical_writes    
    , qs.plan_handle
FROM 
    sys.dm_exec_procedure_stats AS qs    
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS qt    
CROSS APPLY 
    sys.dm_exec_query_plan(qs.plan_handle) qp    
where 
    db_name(qt.dbid) is not null    
    AND OBJECT_NAME(qt.objectid,qt.dbid) = @ProcName
    AND qt.dbid = DB_ID(@DBName)

[edit]

9 세션 모니터링 #

SELECT
     owt.session_id,
     owt.wait_duration_ms,
     owt.wait_type,
     owt.blocking_session_id,
     owt.resource_description,
     es.program_name,
     est.text,
     est.dbid,
     eqp.query_plan,
     es.cpu_time,
     es.memory_usage
 FROM sys.dm_os_waiting_tasks owt
 INNER JOIN sys.dm_exec_sessions es
     ON owt.session_id = es.session_id
 INNER JOIN sys.dm_exec_requests er
     ON es.session_id = er.session_id
 OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
 OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
 WHERE es.is_user_process = 1;
 GO

[edit]

10 big wait stats #


 --리셋
 --DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);
 
 ;WITH Latches AS
     (SELECT
         latch_class,
         wait_time_ms / 1000.0 AS WaitS,
         waiting_requests_count AS WaitCount,
         100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
         ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
     FROM sys.dm_os_latch_stats
     WHERE latch_class NOT IN ('BUFFER')
 )
 SELECT
     W1.latch_class AS LatchClass, 
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
     W1.WaitCount AS WaitCount,
     CAST (W1.Percentage AS DECIMAL(14, 2)) AS Percentage,
     CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
 FROM Latches AS W1
 INNER JOIN Latches AS W2
     ON W2.RowNum <= W1.RowNum
 GROUP BY W1.RowNum, W1.latch_class, W1.WaitS, W1.WaitCount, W1.Percentage
 HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
 GO
[edit]

11 spin lock #


 SELECT * FROM sys.dm_os_spinlock_stats
 ORDER BY [spins] DESC;
 GO
[edit]

12 Latency #

SELECT 
    --virtual file latency
     ReadLatency = CASE WHEN num_of_reads = 0
         THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
     WriteLatency = CASE WHEN num_of_writes = 0 
        THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
     Latency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
         THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
     --avg bytes per IOP
     AvgBPerRead = CASE WHEN num_of_reads = 0 
        THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
     AvgBPerWrite = CASE WHEN io_stall_write_ms = 0 
        THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
     AvgBPerTransfer = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
         THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written) / 
            (num_of_reads + num_of_writes)) END,    
     LEFT (mf.physical_name, 2) AS Drive,
     DB_NAME (vfs.database_id) AS DB,
     --vfs.*,
     mf.physical_name
 FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
 JOIN sys.master_files AS mf
     ON vfs.database_id = mf.database_id
     AND vfs.file_id = mf.file_id
--WHERE vfs.file_id = 2 -- log files
-- ORDER BY Latency DESC
-- ORDER BY ReadLatency DESC
 ORDER BY WriteLatency DESC;
 GO
http://feedproxy.google.com/~r/PaulSRandal/~3/kjZ7zRrkCmI/post.aspx

[edit]

14 Page IO Latch #

SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,page_io_latch_wait_count
,page_io_latch_wait_in_ms
,CAST(1. * page_io_latch_wait_in_ms / NULLIF(page_io_latch_wait_count ,0) AS decimal(12,2)) AS page_io_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY 5 DESC
[edit]

15 CPU Bottlenecks #


select plan_handle,
      sum(total_worker_time) as total_worker_time, 
      sum(execution_count) as total_execution_count,
      count(*) as  number_of_statements 
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc

[edit]

16 블록킹 #

SELECT 
   DB_NAME() AS database_name,  
   GETDATE() AS audit_time,  
   s.spid AS process_id,  
   s.blocked AS blocking_process_id, 
   s.hostname,  
   s.loginame,  
   s.program_name, 
   blocking_s.hostname AS blocking_hostname,  
   blocking_s.loginame AS blocking_loginame,  
   blocking_s.program_name AS blocking_program_name, 
   REPLACE(REPLACE(buffer.[text], CHAR(10), ''),  
           CHAR(9), '') AS sql_statement, 
   SUBSTRING (buffer.[text], request.statement_start_offset/2,  
   (CASE  
      WHEN request.statement_end_offset = -1  
      THEN LEN(CONVERT(NVARCHAR(MAX), buffer.[text])) * 2  
      ELSE request.statement_end_offset  
    END - request.statement_start_offset)/2) AS specific_sql, 
   REPLACE(REPLACE(blocking_buffer.[text], CHAR(10), ''),  
           CHAR(9), '') AS blocking_sql_statement, 
   o.[name] AS blocking_object,  
   blocking_tr_locks.request_mode 
FROM  
   sys.sysprocesses s INNER JOIN 
   sys.dm_exec_connections conn 
ON 
   s.spid = conn.session_id CROSS APPLY  
   sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS buffer  
   LEFT JOIN sys.dm_exec_requests request 
ON 
   conn.session_id = request.session_id INNER JOIN 
   sys.dm_exec_connections blocking_conn  
ON 
   s.blocked = blocking_conn.session_id CROSS APPLY  
   sys.dm_exec_sql_text(blocking_conn.most_recent_sql_handle)  
   AS blocking_buffer INNER JOIN 
   sys.dm_tran_locks blocking_tr_locks 
ON 
   s.blocked = blocking_tr_locks.request_session_id INNER JOIN 
   sys.objects o  
ON 
   blocking_tr_locks.resource_associated_entity_id = o.object_id  
   INNER JOIN sys.sysprocesses blocking_s 
ON 
   s.blocked = blocking_s.spid 
WHERE 
   s.blocked <> 0

[edit]

17 dm_os_performance_counters #

SELECT [object_name], [counter_name],  
   [instance_name], [cntr_value] 
FROM sys.[dm_os_performance_counters] 
WHERE [object_name] = 'SQLServer:Buffer Manager';
[edit]

18 복구 Tracking #

USE <db_name>
GO
DROP TABLE [dbo].[tbl_recovery_tracking]
GO
DROP TABLE [dbo].[tbl_dm_tran_database_transactions]
GO
CREATE TABLE [dbo].[tbl_recovery_tracking](
      [runtime] [datetime] NOT NULL,
      [command] [nvarchar](256) NOT NULL, 
      [session_id] [smallint] NOT NULL,
      [database_id] [smallint] NOT NULL,
      [total_elapsed_time] [int] NOT NULL,
      [percent_complete] [real] NOT NULL,
      [estimated_completion_time] [bigint] NOT NULL,
      [wait_resource] [nvarchar](256) NOT NULL,
      [wait_time] [int] NOT NULL,
      [wait_type] [nvarchar](60) NULL,
      [blocking_session_id] [smallint] NULL,
      [reads] [bigint] NOT NULL,
      [writes] [bigint] NOT NULL,
      [cpu_time] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_dm_tran_database_transactions](
      [runtime] [datetime] NOT NULL,
      [transaction_id] [bigint] NOT NULL,
      [database_id] [int] NOT NULL,
      [database_transaction_log_record_count] [bigint] NOT NULL,
      [database_transaction_log_bytes_used] [bigint] NOT NULL,
      [database_transaction_log_bytes_reserved] [bigint] NOT NULL,
      [database_transaction_next_undo_lsn] [numeric](25, 0) NULL
) ON [PRIMARY]
GO

-- collect the information in a loop
WHILE 1 = 1
BEGIN
      INSERT INTO [dbo].[tbl_recovery_tracking]
      SELECT GETDATE() as runtime, command,
      session_id, database_id, total_elapsed_time,
      percent_complete, estimated_completion_time,  
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
      FROM sys.dm_exec_requests
      WHERE command = 'DB STARTUP' -- may need to change this if troubleshooting recovery as part of attach database or restore
 
      INSERT INTO tbl_dm_tran_database_transactions 
      SELECT GETDATE() as runtime,  
      transaction_id, database_id, 
      database_transaction_log_record_count, database_transaction_log_bytes_used, 
      database_transaction_log_bytes_reserved, database_transaction_next_undo_lsn 
      FROM sys.dm_tran_database_transactions
      
      WAITFOR DELAY '00:00:01'            -- change this capture interval

END
GO

-- after you collect information for some time, you can analyze the information to understand the progress of recovery
SELECT runtime,   command,
      session_id, database_id, total_elapsed_time, 
      percent_complete, estimated_completion_time,  
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
FROM [dbo].[tbl_recovery_tracking]
WHERE session_id = 25         -- change this
ORDER BY runtime
GO

SELECT 
runtime, transaction_id, database_id, 
database_transaction_log_record_count, 
database_transaction_log_bytes_used, database_transaction_log_bytes_reserved, 
database_transaction_next_undo_lsn 
FROM tbl_dm_tran_database_transactions 
WHERE database_id = 11 and transaction_id = 1452239         -- change this
ORDER BY runtime
GO
http://blogs.msdn.com/b/psssql/archive/2010/12/29/tracking-database-recovery-progress-using-information-from-dmv.aspx

[edit]

19 세션 모니터링 #

select 
	spid
,	hostname
,	cpu
,	physical_io
,	status
,	login_time
,	last_batch 
,	st.text 
from sys.sysprocesses a
	cross apply sys.dm_exec_sql_text(sql_handle) st 
--where status = 'runnable'
order by physical_io desc

[edit]

20 현재 cpu 사용율 #

--sql server 2008 r2
DECLARE @ts_now BIGINT 
SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks) FROM sys.dm_os_sys_info 
 
SELECT record_id, 
        DATEADD(ms, 0 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,  
        SQLProcessUtilization, 
        SystemIdle, 
        100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization 
FROM ( 
        SELECT  
                record.value('(./Record/@id)[1]', 'int') AS record_id, 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle, 
                record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization, 
                TIMESTAMP 
        FROM ( 
                SELECT TIMESTAMP, CONVERT(XML, record) AS record  
                FROM sys.dm_os_ring_buffers  
                WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                AND record LIKE '% %') AS x 
        ) AS y  
ORDER BY record_id DESC

[edit]

21 Transaction/sec #

SET NOCOUNT ON
SET STATISTICS IO OFF

DECLARE 
	@cntr_value bigint
,	@min bigint
,	@sec bigint

DECLARE @Result TABLE
(
	tps bigint
)

SET @sec = 1 
SET @min = 1 --10 minutes

WHILE(@sec <= @min*60)
BEGIN
	SELECT @cntr_value = cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name LIKE 'Transactions/sec%'
	AND instance_name LIKE '_Total%'

	WAITFOR DELAY '00:00:01'
	
	INSERT @Result(tps)
	SELECT cntr_value - @cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name LIKE 'Transactions/sec%'
	AND instance_name LIKE '_Total%'

	SET @sec = @sec + 1
END

SELECT AVG(tps) [Avg. Transaction/Sec]
FROM @Result

[edit]

22 대기유형들 #

http://feedproxy.google.com/~r/PaulSRandal/~3/CVziN8ve91M/post.aspx
WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     )
SELECT
     W1.wait_type AS WaitType, 
     CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
     CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
     CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
     W1.WaitCount AS WaitCount,
     CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
     ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
--HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO

[edit]

23 각 데이터베이스에 대해 캐시된 페이지 수 반환 #

select count(*)as cached_pages_count
    ,case database_id
        when 32767 then 'resourcedb'
        else db_name(database_id)
        end as database_name
from sys.dm_os_buffer_descriptors
group by db_name(database_id) ,database_id
order by cached_pages_count desc

[edit]

24 현재 데이터베이스의 각 개체에 대해 캐시된 페이지 수 #

select count(*)as cached_pages_count
    ,name ,index_id
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select object_name(object_id) as name
            ,index_id ,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select object_name(object_id) as name
            ,index_id, allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
where database_id = db_id()
group by name, index_id
order by cached_pages_count desc

[edit]

25 데이터베이스, 파일, pagetype별 페이지수 #

  select   case
             when grouping(dbname) = 1 then '--- total ---'
             else dbname
           end as dbname,
           case
             when grouping(fileid) = 1 then '--- total ---'
             else fileid
           end as fileid,
           case
             when grouping(pagetype) = 1 then '--- total ---'
             else pagetype
           end as pagetype,
           count(* ) as countpages,
           sum(row_count*1.0) as sumrowcount,
           avg(row_count*1.0) as avgrowcount,
           sum(freespacebytes*1.0) as sumfreespacebytes,
           avg(freespacebytes*1.0) as avgfreespacebytes
  from     (select case
                     when database_id = 32767 then 'resourcedb'
                     else cast(db_name(database_id) as varchar(25))
                   end as dbname,
                   cast(file_id as varchar(10)) as fileid,
                   cast(page_type as varchar(25)) as pagetype,
                   row_count as row_count,
                   free_space_in_bytes as freespacebytes
            from   sys.dm_os_buffer_descriptors bufferdescriptor with (nolock)) tmp
  group by dbname,fileid,pagetype with rollup
  order by case
             when grouping(dbname) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
             else dbname
           end,
           case
             when grouping(fileid) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
             else fileid
           end,
           case
             when grouping(pagetype) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
             else pagetype
           end;

시간: 댓글 없음:
라벨:
피드 구독하기: 글 (Atom)