Jul 18, 2014

SQL Excution time


SELECT top 100
    OBJECT_NAME(qt.objectid) as Object_Text
  , qs.execution_count AS [Execution Count]
  ,qs.last_execution_time
  ,qs.max_worker_time
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second] 
  , 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()       
ORDER BY
    --qs.execution_count DESC
    --qs.max_worker_time desc
    qs.total_worker_time / qs.execution_count desc
OPTION (RECOMPILE);

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib