Retrieve the list of Top executed SQL Queries


You will come to a point were you need to know more about your currently running queries that consume a huge amount of memory and physical I/O. Below are a list of Queries that I use to get the list of Executed queries from different perspectives

Top Executed Queries

   1: -- Get Top 100 executed SP's ordered by execution count

   2: SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',

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

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

   5: qs.total_worker_time AS 'TotalWorkerTime',

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

   7: qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,

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

   9: FROM sys.dm_exec_query_stats AS qs

  10: CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

  11: WHERE qt.dbid = db_id() -- Filter by current database

  12: ORDER BY qs.execution_count DESC

  13:  

Top (CPU Usage) Queries

   1: -- Get Top 20 executed SP's ordered by total worker time (CPU pressure)

   2: SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime',

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

   4: qs.execution_count AS 'Execution Count',

   5: ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',

   6: ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

   7: qs.max_logical_reads, qs.max_logical_writes,

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

   9: FROM sys.dm_exec_query_stats AS qs

  10: CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

  11: WHERE qt.dbid = db_id() -- Filter by current database

  12: ORDER BY qs.total_worker_time DESC

Top (Logical Reads) Queries

   1: -- Get Top 20 executed SP's ordered by logical reads (memory pressure)

   2: SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads,

   3: qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',

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

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

   6: qs.total_worker_time AS 'TotalWorkerTime',

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

   8: qs.total_logical_writes,

   9: qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,

  10: DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid

  11: FROM sys.dm_exec_query_stats AS qs

  12: CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

  13: WHERE qt.dbid = db_id() -- Filter by current database

  14: ORDER BY total_logical_reads DESC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s