SQL Server 2008에 sys.dm_exec_procedure_stats DMV가 추가되었습니다.
2008이전 버젼에서는 SP별로 실행정보를 보기 위해서는 statment단위로 저장된 데이터를
GROUP BY해서 확인했지만, SQL Server 2008부터는 해당 DMV가 추가됨에 따라
쉽게 바로 확인이 가능합니다.
SELECT * FROM (
SELECT TOP 10
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
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
)X
order by
[avg_worker_time(ms)]
DESC
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com
SQL Server 2005부터 H/W NUMA를 본격적으로 지원하고 있습니다.
NUMA란 Non-Uniform Memory Access으로 간단하게 특정 CPU 그룹 단위로
전용 메모리 공간을 가지는 것을 말합니다.
기존 SMP환경에서는 다수의 CPU가 동일한 메모리를 바라보게 되어
메모리 엑세스 경합이라는 문제가 발생하여 이러한 문제를 극복하고자 NUMA 아키텍쳐가
소개되었습니다.
기존 x86, x64 프로세스에서는 AMD의 옵테론 정도만 H/W NUMA를 지원하고 있었으나,
요즘 INTEL에서 발표된 네할렘 CPU도 메모리 컨트롤러가 CPU 내부로 들어가면서
CPU 당 NUMA 노드 하나씩 설정 할 수 있게 되었습니다.
그렇다면 SQL Server에서 NUMA를 사용하는 것이 기존 SMP환경보다 더 좋을까요?
여러 CPU가 하나의 메모리를 바라보지 않아도 되고, NUMA 노드별로 Lazy Write 프로세스가
독립적으로 생겨 성능상 이점을 줄 수 있습니다. 하지만 NUMA 또한 단점이 있습니다.
CPU 노드별로 NUMA 노드가 생기는 경우를 예로 들어보면
내가 어떠한 쿼리를 수행하기 위해서 필요한 데이터가 로컬 메모리에 있을 수도 있고,
리모트 메모리에 있을 수도 있습니다. 만약 모든 데이터가 로컬 메모리에 존재한다면
특별한 추가적인 작업 없이 데이터를 엑세스 할 수 있을 것이니다. 하지만 리모트에
데이터가 있다면 추가적인 오버헤드가 발생하게 됩니다. 하지만 일반적으로 메모리가 워낙 빨라
로컬 메모리에서 읽든, 리모트에서 읽든 사용자는 체감하기는 쉽지 않습니다.
그럼 과연 NUMA를 사용하는 SQL Server환경에서 리모트 메모리 엑세스와 로컬 메모리 엑세스가
눈에 보이는 성능 차이가 발생할지 또 어느정도 차이가 발생할지 살펴보겠습니다.
테스트는 두 가지로 샘플 테이블을 만들고 테이블을 쭉 읽는 SCAN 작업과 넌 클러스터 인덱스에서
데이터 페이지를 Lookup하도록 하여 테스트 하였습니다. 노드별 시간을 측정하기 위해 우선 NUMA
노드별로 데이터를 올리기 위해 TCP/IP 포트와 NUMA노드를 매핑하여 테스트 하였습니다.
1000번 포트로 연결하면 0번 NUMA 노드로, 2000번 포트로 연결하면 1번 NUMA 노드만을
사용하도록 세팅하였습니다. 모든 데이터 페이지를 비운 후 1000번 포트로 접근하여, 0번 노드의
Freepage에 데이터 페이지를 채운 후 두 개의 쿼리를 각각 5번씩 수행하였습니다.
처음으로 살펴볼 것은 lookup이 발생하는 쿼리 입니다.
SET STATISTICS TIME은 ms 단위이기에 두 경우가 비슷하게 나오는 경향이 있어 DMV를 통해
응답시간을 비교했습니다.
아래 표에서 볼 부분은 avg_Elapsed_Time(ms) 입니다.
로컬 메모리 엑세스와 리모트 메모리 엑세스의 평균 수행시간이 12ms정도 차이가 나는 것을 볼 수 statement_text ExecutionCount avg_Elapsed_Time(ms) max_Elapsed_Time(ms) avg_Worker_Time(ms) avg_logical_reads 로컬(0번 노드) SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK,index=2) OPTION(MAXDOP 1) 5 391.406 404.296 391.406 106541 리모트(1번 노드) SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK,index=2) OPTION(MAXDOP 1) 5 403.906 413.085 403.906 106541
있습니다.
두 번째로 살펴볼 것은 SCAN하는 쿼리입니다.
로컬 메모리 엑세스와 리모트 메모리 엑세스의 평균 수행시간이 4ms정도 차이가 나는 것을 볼 수
있습니다.
|
statement_text |
ExecutionCount |
avg_Elapsed_Time(ms) |
max_Elapsed_Time(ms) |
avg_Worker_Time(ms) |
avg_logical_reads | |
|
로컬(0번 노드) |
SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK) OPTION(MAXDOP 1) |
5 |
293.554 |
303.71 |
293.554 |
100031 |
|
리모트(1번 노드) |
SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK) OPTION(MAXDOP 1) |
5 |
297.851 |
304.687 |
297.851 |
100031 |
이러한 결과를 볼 때 분명 리모트 메모리 엑세스는 분명 로컬 메모리 엑세스 보다 느린 것을 볼 수
있습니다. 그렇다면 이러한 문제를 해결 할 수 있는 방법은 없을까? 아니 보다 효율적으로 사용할 수
있는 방법은 없을까에 대한 고민은 다음에 정리하도록 하겠습니다.
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com
SQL Server 2005부터 추가된 DMV를 통해 여러 가지 유용한 스크립트를 만들 수 있습니다.
이번에는 특정 DB 또는 모든 DB에 대해 SELECT, DELETE, INSERT 또는 UPDATE Statmemt가
어떠한 비율로 발생하는지 그리고 리소스별 어떠한 비율로 사용하는지 쉽게 확인 할 수 있는 쿼리 입니다.
지금까지 막연하게 "이 DB는 대부분이 SELECT가 발생하는 것이야."
이러한 생각을 구체적이고 정확하게 보여줄 수 있습니다.
처음 접하는 시스템을 분석시 위와 같은 자료가 있으면 보다 쉽게 파악 할 수 있을 것입니다.
제 생각에는 ~ 꽤 DB관련 업무에 유용하게 사용될 수 있을 것 같습니다.ㅎㅎ
아래는 수행한 결과 입니다.
ExecutionCount는 분당 실행한 횟수 입니다.
[로그성 DB시스템]
[일반 OLTP DB시스템]
|
with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql) SELECT StatementType ,ExecutionCount ,cast((ExecutionCount*1.0/total_ExecutionCount)*100 as Decimal(18,2)) as [% ExecutionCount] ,cast((Elapsed_Time/total_Elapsed_Time)*100 as Decimal(18,2)) as [% Elapsed_Time] ,cast((Worker_Time/total_Worker_Time)*100 as Decimal(18,2)) as [% Worker_Time] ,cast((logical_reads/total_logical_reads)*100 as Decimal(18,2)) as [% logical_reads] ,cast((logical_writes/total_logical_writes)*100 as Decimal(18,2)) as [% logical_writes] ,cast((physical_reads/total_physical_reads)*100 as Decimal(18,2)) as [% physical_reads] FROM ( SELECT StatementType ,sum(avg_ExecutionCount) over(partition by StatementType) as ExecutionCount ,sum(avg_ExecutionCount) over(partition by 1) as total_ExecutionCount ,sum(avg_total_Elapsed_Time) over(partition by StatementType) as Elapsed_Time ,sum(avg_total_Elapsed_Time) over(partition by 1) as total_Elapsed_Time ,sum(avg_total_Worker_Time) over(partition by StatementType) as Worker_Time ,sum(avg_total_Worker_Time) over(partition by 1) as total_Worker_Time ,sum(avg_total_logical_reads) over(partition by StatementType) as logical_reads ,sum(avg_total_logical_reads) over(partition by 1) as total_logical_reads ,sum(avg_total_logical_writes) over(partition by StatementType) as logical_writes ,sum(avg_total_logical_writes) over(partition by 1) as total_logical_writes ,sum(avg_total_physical_reads) over(partition by StatementType) as physical_reads ,case when sum(avg_total_physical_reads) over(partition by 1) = 0 THEN NULL ELSE sum(avg_total_physical_reads) over(partition by 1) END as total_physical_reads FROM( SELECT StatementType ,count(*) as cnt ,sum(ISNULL(ExecutionCount/DATEDIFF(MINUTE , creation_time,GetDate()),0)) as avg_ExecutionCount ,cast(sum(ISNULL([total_Elapsed_Time(sec)]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_Elapsed_Time ,cast(sum(ISNULL([total_Worker_Time(ms)]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_Worker_Time ,cast(sum(ISNULL([total_logical_reads]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_logical_reads ,cast(sum(ISNULL([total_logical_writes]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_logical_writes ,cast(sum(ISNULL(total_physical_reads/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_physical_reads FROM ( SELECT db_name ,ISNULL([StatementType],StatementType2) as StatementType --,[statement_text] ,[creation_time] ,[ExecutionCount] ,[total_Elapsed_Time(sec)] ,[total_Worker_Time(ms)] ,[total_logical_reads] ,[total_logical_writes] ,[total_physical_reads] FROM ( SELECT db_name(qp.dbid) AS 'db_name' , qs.creation_time , qs.execution_count AS 'ExecutionCount' , qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)' , qs.total_worker_time/1000.0 AS 'total_Worker_Time(ms)' , total_logical_reads , qs.total_logical_writes , total_physical_reads , cast(qp.query_plan as xml) as query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp )X OUTER APPLY ( SELECT c.value('(//sql:StmtSimple/@StatementType)[1]','VARCHAR(100)') AS "StatementType" ,c.value('(//sql:StmtCond/@StatementType)[1]','VARCHAR(100)') AS "StatementType2" FROM query_plan.nodes('//sql:QueryPlan')B(C) )xp WHERE db_id(db_name) > 4 AND DATEDIFF(MINUTE,creation_time, GetDate()) > 0 AND StatementType is not null )X group by StatementType )Y )Z |
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
SQL Server 2005 이상 버전에서는 아래의 DMV를 통하여 어떠한 테이블에 인덱스가 없어 성능적으로 영향을 주는지 확인 할 수 있습니다.
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details
또한 예전에 소개하였듯이 SQL Server 2008에서는 그래픽 실행계획에서 missing index에 대한 정보도 바로 확인 할 수 있습니다.
하지만 여전히 missing index로 잡히게 한 쿼리의 원본은 쉽게 확인 할 수가 없습니다.
그래서 이번에 소개하는 내용은 조금 더 쉽게 missing index를 유발한 쿼리를 찾아보는 것입니다.
XML 실행계획에 이 missing index에 대한 정보도 같이 저장 되어있기에 전에 소개했던 DMV를 조금 응용하면 특정 테이블에 어떤 쿼리로 인해 missing index가 등록되었는지 쉽게 확인 할 수 있을 것입니다.
자세한 내용은 아래를 참고하세요.
with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT --TOP 10
db_name
,sp_name
,sp_text
,[statement_text]
,X.*
,[creation_time]
,[ExecutionCount]
FROM
(
SELECT
db_name(qt.dbid) AS 'db_name'
,qt.text AS 'sp_text'
, substring(qt.text, (qs.statement_start_offset/2)+1
, ((case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time
, qs.execution_count AS 'ExecutionCount'
, cast(qp.query_plan as xml) as query_plan
,OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)Y
CROSS APPLY
(
SELECT
c.value('(./@Impact)[1]','float') as missing_index_Impact
,c.value('(./sql:MissingIndex/@Database)[1]','varchar(100)') + '.'+
c.value('(./sql:MissingIndex/@Schema)[1]','varchar(100)') + '.'+
c.value('(./sql:MissingIndex/@Table)[1]','varchar(100)') as [missing_index_Table]
FROM Y.query_plan.nodes('//sql:MissingIndexGroup')B(C)
)X
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
