DMV

--> 정리하자..

응용 프로그램 성능 최적화를 위한 숨겨진 데이터 찾기
http://msdn.microsoft.com/ko-kr/magazine/cc135978.aspx#S1

4.0 Useful Queries on DMV’s to understand Plan Cache Behavior

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx


1. Top SPs By Execution Count 
   얼마나 자주 sp를 호출하고,,,,, 평균 실행시간 및 캐쉬에 존재하였던 시간까지 모두 체크 가능하다..

-- Ver SQL2005
SELECT
TOP (100) qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', 

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',

qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() -- Filter by current database

ORDER BY qs.execution_count DESC
GO

-- Ver SQL2008

SELECT TOP (100) p.name AS 'SP Name', qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS 'Calls/Second',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', 

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS 'avg_elapsed_time', qs.last_elapsed_time,

qs.cached_time, qs.last_execution_time

FROM sys.procedures AS p

INNER JOIN sys.dm_exec_procedure_stats AS qs

ON p.object_id = qs.object_id

ORDER BY qs.execution_count DESC;




'SQL Server 2005' 카테고리의 다른 글

XML execution plans out as SQLPlan files  (0) 2009.03.24
sp_depends  (0) 2009.03.13
유용한 DMV  (0) 2009.03.04
업데이트 비용에 대한 고찰  (0) 2009.02.04
SQL의 SHFT 비트 연산  (0) 2009.01.13