응용 프로그램 성능 최적화를 위한 숨겨진 데이터 찾기
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 |