-- ===============================================
-- Author: Pawel Potasinski
-- Create date: 2010-01-05
-- Script: Performance query
-- Description:	Use the query below to find 
--              queries with the worst performance
-- ===============================================

SELECT TOP 50
  DB_NAME(st.dbid) AS [database_name],
  QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + '.' + 
  QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)) AS [object_name],
  SUBSTRING(
    st.text, 
    (qs.statement_start_offset/2)+1,
    (CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text)
      ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2 + 1
  ) AS statement_text,
  qs.execution_count,
  qs.creation_time,
  qs.last_execution_time,
  --
  (qs.total_logical_reads + qs.total_physical_reads) / qs.execution_count AS avg_reads,
  qs.total_logical_reads + qs.total_physical_reads AS total_reads,
  qs.max_logical_reads + qs.max_physical_reads AS max_reads,
  qs.min_logical_reads + qs.min_physical_reads AS min_reads,
  qs.max_logical_reads,
  qs.total_logical_reads,
  qs.total_physical_reads,
  qs.max_physical_reads,
  --
  qs.total_elapsed_time / qs.execution_count AS avg_duration,
  qs.total_elapsed_time  AS total_duration,
  qs.max_elapsed_time  AS max_duration,
  qs.min_elapsed_time  AS min_duration,
  --
  qs.total_worker_time / qs.execution_count AS avg_cpu,
  qs.total_worker_time AS total_cpu,
  qs.max_worker_time AS max_cpu,
  qs.min_worker_time AS min_cpu,
  --
  pl.query_plan
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS pl
-- WHERE st.dbid = DB_ID()                        -- filter by current database
WHERE st.objectid = OBJECT_ID(N'dbo.Employees') -- filter by object name
ORDER BY avg_duration DESC;
-- ORDER BY avg_cpu DESC                          -- detect CPU issues