유용한 DMV

으앙 ㅜ...ㅜ.
많이 달라졌다...

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!1129.entry?wa=wsignin1.0&sa=974308588


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


SQL2008
sys.dm_exec_procedure_stats DMV that was added in SQL Server 2008

    -- Top SPs By Execution Count (SQL 2008)
    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;
    
    -- Top SPs By Execution Count (SQL 2005)
    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

Technorati Tags: SQL Server 2008