현재 운영중인 서버에서 쿼리별 컴파일에 사용된 리소스 및 시간을 알 수 있는 방법은 SET STATISTICS TIME 옵션과 XML 플랜이 있습니다.
위 방법을 가지고 하나씩 관련 정보를 가져오기 위해서는 꽤 긴 시간이 필요 할 수도 있습니다.
보다 쉬운 방법으로는 DMV를 이용하는 것입니다.
SQL Server 2005이상에서는 DMV를 통해 XML 플랜을 제공해주고 있습니다.
XML 플랜을 XQUERY로 표 형태로 처리한다면 보다 쉽게 컴파일에 리소스 사용에 대해 확인 할 수 있습니다.
[XML 실행계획]
|
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1779.0"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementText=" " StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="7.2111" StatementEstRows="100" StatementOptmLevel="FULL" QueryHash="0x3CFB83F9F6D5CA9A" QueryPlanHash="0x2A53000B8E4C7D2E"> <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> <QueryPlan CachedPlanSize="80" CompileTime="53" CompileCPU="53" CompileMemory="1400"> |
쿼리 플랜에서 몇가지 속성값에 대해 간단히 정리해 보면
- CompileTime : 쿼리가 컴파일 할 때 소요된 시간 (SQL Server 2005 SP2 추가)
- CompileCPU: : 쿼리가 컴파일 할 때 사용한 CPU 시간 (SQL Server 2005 SP2 추가)
- CompileMemory : 쿼리가 컴파일 할 때 사용한 메모리 크기(SQL Server 2005 SP2 추가)
아래의 DMV를 통해 확인 할 수 있습니다.
with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT TOP 10
db_name,sp_name,sp_text,[statement_text]
,[query_plan],[CompileTime(ms)],[CachedPlanSize(kb)],[CompileCPU(ms) ],[CompileMemory(kb)]
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
, 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
)X
OUTER APPLY
(
SELECT
c.value('(./@CompileTime)[1]','INT') AS "CompileTime(ms)"
,c.value('(./@CachedPlanSize)[1]','INT') AS "CachedPlanSize(kb)"
,c.value('(./@CompileCPU)[1]','INT') AS "CompileCPU(ms) "
,c.value('(./@CompileMemory)[1]','INT') AS "CompileMemory(kb)"
FROM query_plan.nodes('//sql:QueryPlan')B(C)
)xp
[결과]
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
