펌 : http://www.uhoon.co.kr/mssql/2309


참고 Url 

http://technet.microsoft.com/ko-kr/library/bb838723(v=office.12).aspx (SQL Server 상태 모니터링)

http://technet.microsoft.com/ko-kr/library/bb510705(v=sql.105).aspx (모니터링(데이터베이스 엔진))



일전에 사내 디비서버 성능저하 현상으로 상태 체크하는 방법을 찾다가 알게된 정보입니다.

결과적으로는 별로 사용해보지 못했으나 유용한 정보입니다.

( 성능 저하 문제는 RAID IO 비율 하드웨어 셋팅 문제였다는.. HP .. Accelerator ratio Read/Write 비율 설정하는 옵션이 있더군요.. ..)



SQL Server 상태를 모니터링하기 위해 동적 관리 뷰 및 함수에 대해 일반적으로 사용하는 몇 가지 쿼리들..



- CPU 병목 현상 모니터링

CPU 병목 현상은 일반적으로 최적화되지 않은 쿼리 계획, 잘못된 구성, 잘못된 디자인 요소 또는 충분하지 않은 하드웨어 리소스 등으로 인해 발생합니다. 

다음은 CPU 병목 현상을 일으키는 원인을 찾아내기 위해 일반적으로 사용되는 몇 가지 쿼리입니다.

다음 쿼리를 실행하면 현재 캐시된 배치나 프로시저 중 CPU 사용률이 가장 높은 항목이 무엇인지 쉽게 파악할 수 있습니다.


?
1
2
3
4
5
6
7
8
SELECT TOP 50
      SUM(qs.total_worker_time) AS total_cpu_time,
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements,
      qs.sql_handle
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC


다음 쿼리는 SQL 텍스트를 사용하여 캐시된 계획별로 집계한 CPU 사용량을 보여 줍니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
      total_cpu_time,
      total_execution_count,
      number_of_statements,
      s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
      (SELECT TOP 50
            SUM(qs.total_worker_time) AS total_cpu_time,
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements,
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset,
            --MAX(statement_end_offset) AS statement_end_offset
      FROM
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2


다음 쿼리는 평균 이상의 CPU 사용률을 보이는 상위 50개 SQL 문을 보여 줍니다.

?
1
2
3
4
5
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC


다음은 과도한 컴파일/재컴파일을 찾기 위한 DMV 쿼리입니다.

?
1
2
3
4
select * from sys.dm_exec_query_optimizer_info
where
      counter = 'optimizations'
      or counter = 'elapsed time'


다음 샘플 쿼리에서는 다시 컴파일된 상위 25개의 저장 프로시저를 표시합니다. plan_generation_num은 쿼리를 다시 컴파일한 횟수를 나타냅니다.

?
1
2
3
4
5
6
7
8
9
10
11
select top 25
      sql_text.text,
      sql_handle,
      plan_generation_num,
      execution_count,
      dbid,
      objectid
from sys.dm_exec_query_stats a
      cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc


쿼리 계획이 충분하지 않으면 CPU 사용량이 증가할 수 있습니다.
다음 쿼리에서는 가장 많은 누적 CPU 사용량을 보이는 쿼리를 표시합니다.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from
    (select top 50
        qs.plan_handle,
        qs.total_worker_time
    from
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc


다음 쿼리에서는 '%Hash Match%', '%Sort%' 등과 같이 CPU를 집중적으로 사용할 수 있는 몇 가지 의심해볼 만한 연산자를 표시합니다.

?
1
2
3
4
5
6
7
select *
from
      sys.dm_exec_cached_plans
      cross apply sys.dm_exec_query_plan(plan_handle)
where
      cast(query_plan as nvarchar(max)) like '%Sort%'
      or cast(query_plan as nvarchar(max)) like '%Hash Match%'

쿼리 계획이 충분하지 않아 CPU 사용량이 높아졌다는 사실을 확인했다면 쿼리에 관련된 테이블에 대해 UPDATE STATISTICS를 실행하고 문제가 지속되는지 확인합니다.
그런 다음 데이터를 수집하고 그 문제를 PerformancePoint 계획 지원 센터에 보고합니다.
시스템에서 컴파일과 재컴파일이 과도하게 이루어지면 시스템의 CPU 관련 성능에 문제가 발생할 수 있습니다.
다음 DMV 쿼리를 실행하면 과도한 컴파일/재컴파일을 찾아낼 수 있습니다.
?
1
2
3
4
select * from sys.dm_exec_query_optimizer_info
where
counter = 'optimizations'
or counter = 'elapsed time'


다음 샘플 쿼리에서는 다시 컴파일된 상위 25개의 저장 프로시저를 표시합니다. plan_generation_num은 쿼리를 다시 컴파일한 횟수를 나타냅니다.

?
1
2
3
4
5
6
7
8
9
10
11
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc



- 메모리 병목 현상

메모리 부족 문제에 대한 감지와 조사를 시작하려면 먼저 SQL Server에서 고급 옵션을 활성화해야 합니다. 마스터 데이터베이스에 대해 다음 쿼리를 실행하여 우선 이 옵션을 활성화합니다.


?
1
2
3
4
5
6
sp_configure 'show advanced options'
go
sp_configure 'show advanced options', 1
go
reconfigure
go


다음 쿼리를 실행하여 메모리 관련 구성 옵션을 먼저 검사합니다.

?
1
2
3
4
5
6
7
8
9
10
sp_configure 'awe_enabled'
go
sp_configure 'min server memory'
go
sp_configure 'max server memory'
go
sp_configure 'min memory per query'
go
sp_configure 'query wait'
go


다음 DMV 쿼리를 실행하여 CPU, 스케줄러 메모리 및 버퍼 풀 정보를 확인합니다.

?
1
2
3
4
5
6
7
8
9
10
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info



- I/O 병목 현상

I/O 병목 현상은 래치 대기 시간을 조사하여 확인합니다. 다음 DMV 쿼리를 실행하여 I/O 래치 대기 시간에 대한 통계를 확인합니다.


?
1
2
3
4
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats 
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
order by wait_type


waiting_task_counts 및 wait_time_ms가 평상시와 비교하여 크게 달라졌으면 I/O 문제가 있는 것입니다. 이 비교를 위해서는 SQL Server가 안정적으로 실행되고 있을 때 성능 카운터와 주요 DMV 쿼리 출력의 기준선을 마련해 두는 것이 중요합니다.
이러한 wait_types를 살펴보면 I/O 하위 시스템이 병목 현상을 겪고 있는지 알 수 있습니다.
다음 DMV 쿼리를 사용하면 현재 보류 중인 I/O 요청을 확인할 수 있습니다. 이 쿼리를 정기적으로 실행하여 I/O 하위 시스템의 상태를 점검하고 I/O 병목 현상에 관련된 물리적 디스크를 격리하십시오.

?
1
2
3
4
5
6
7
8
9
select
    database_id,
    file_id,
    io_stall,
    io_pending_ms_ticks,
    scheduler_address
from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

정상적인 상태에서는 대개 쿼리를 실행해도 아무 것도 반환되지 않습니다. 만일 이 쿼리에서 어떤 행이 반환된다면 더 자세한 조사를 해볼 필요가 있습니다.
다음 DMV 쿼리를 실행하여 I/O 관련 쿼리를 찾을 수도 있습니다.
?
1
2
3
4
5
6
7
8
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
                   (total_logical_writes/execution_count) as avg_logical_writes,
           (total_physical_reads/execution_count) as avg_physical_reads,
           Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
      cross apply sys.dm_exec_query_plan(plan_handle) p
      cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc


다음 DMV 쿼리를 사용하면 가장 많은 I/O를 생성하는 배치/요청이 무엇인지 찾을 수 있습니다. 

다음과 같은 DMV 쿼리를 사용하면 가장 많은 I/O를 생성하는 상위 5개 요청을 찾을 수 있습니다.

이러한 쿼리를 조정하여 시스템 성능을 향상시킬 수 있습니다.

?
1
2
3
4
5
6
7
8
9
10
select top 5
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count,
    statement_start_offset as stmt_start_offset,
    sql_handle,
    plan_handle
from sys.dm_exec_query_stats 
order by  (total_logical_reads + total_logical_writes) Desc



- 차단


다음 쿼리를 실행하면 차단 세션을 확인할 수 있습니다.

?
1
2
3
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null


이 호출을 사용하면 blocking_session_id를 통해 반환되는 SQL을 찾을 수 있습니다. 예를 들어 blocking_session_id가 87인 경우 SQL을 확인하려면 다음 쿼리를 실행합니다.

?
1
dbcc INPUTBUFFER(87)


다음 쿼리에서는 SQL 대기 상태 분석 및 대기 중인 상위 10개 리소스를 표시합니다.

?
1
2
3
4
select top 10 *
from sys.dm_os_wait_stats
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc


어떤 spid가 다른 spid를 차단하고 있는지 확인하려면 데이터베이스에 다음과 같은 저장 프로시저를 만들어 실행합니다.

이 저장 프로시저는 차단 상황을 보고합니다. 

@spid를 알아내려면 sp_who를 입력합니다. @spid는 선택적 매개 변수입니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create proc dbo.sp_block (@spid bigint=NULL)
as
select
    t1.resource_type,
    'database'=db_name(resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id   
from
    sys.dm_tran_locks as t1,
    sys.dm_os_waiting_tasks as t2
where
    t1.lock_owner_address = t2.resource_address and
    t1.request_session_id = isnull(@spid,t1.request_session_id)


다음은 이 저장 프로시저를 사용하는 예제입니다.

?
1
2
exec sp_block
exec sp_block @spid = 7


저작자표시

'job > mssql' 카테고리의 다른 글

mssql 튜닝을위한 성능 모니터  (0) 2018.08.31
모니터링시 조치 방법  (0) 2018.08.31

+ Recent posts

티스토리툴바