Navi's

Necessity, the mother of invention

  • Visitor Map

    Locations of Site Visitors
  • Flag Counter

    free counters

Get slowest query in SQL server database

Posted by Navi's on May 20, 2010


Using this query we can identify the slowest query which is executing in database.
To execute this you must have a sufficient privileges other wise you will get this error.

The user does not have permission to perform this action.

SELECT TOP 10 SUBSTRING(qtext.TEXT, (qstats.statement_start_offset/2)+1,
((CASE qstats.statement_end_offset
WHEN -1 THEN DATALENGTH(qtext.TEXT)
ELSE qstats.statement_end_offset
END - qstats.statement_start_offset)/2)+1),
qstats.execution_count,
qstats.total_logical_reads, qstats.last_logical_reads,
qstats.total_logical_writes, qstats.last_logical_writes,
qstats.total_worker_time,
qstats.last_worker_time,
qstats.total_elapsed_time/1000000 total_elapsed_time_in_S,
qstats.last_elapsed_time/1000000 last_elapsed_time_in_S,
qstats.last_execution_time,
qplan.query_plan
FROM sys.dm_exec_query_stats qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) qtext
CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) qplan
ORDER BY qstats.total_logical_reads DESC -- Order by Logical Reads

You can try this in order by also

ORDER BY qstats.total_logical_writes DESC -- Order by Logical Writes
ORDER BY qstats.total_worker_time DESC -- Order by CPU Time

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

 
%d bloggers like this: