When it comes to performance one of the easiest things you can do to identify potential bottlenecks is routinely review your systems longest running queries (LRQs). Simple changes to stored procedures or inline SQL reports can, over time, cause unexpected performance issues and, if not kept in check, can have devastating effects on the speed and reliability of your SQL environment.
A simple Google search on “how to view longest running queries” will provide you with a handful of useful scripts on how to monitor this. I’ve noticed that most are set up to show a simple
TOP 10 but I find its more relevant to get a percentage of the top longest running queries as it will give you a better snapshot of what is going on.
I found inspiration for the following script from a quick Google search but have modified it to include the database name and object name as well as a better method for displaying the actual code that is being executed. I have also added a WHERE clause, which commented out by default, to specify certain databases.
SELECT TOP 10 PERCENT
CASE WHEN d.name IS NULL THEN '-'
END AS 'Database',
CASE WHEN o.name IS NULL THEN '-'
END AS 'ObjectName',
total_physical_reads AS'Physical Reads',
total_logical_reads AS 'Logical Reads',
execution_count AS 'Execution Count',
total_elapsed_time AS 'Elapsed Time',
(total_elapsed_time / execution_count) AS 'Average Elapsed Time',
SUBSTRING(est.text, (eqs.statement_start_offset/2) + 1,
WHEN -1 THEN DATALENGTH(est.text)
END - eqs.statement_start_offset)/2) + 1) AS 'SQL Statement',
last_execution_time AS 'Last Time Executed'
FROM sys.dm_exec_query_stats AS eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
LEFT OUTER JOIN sys.objects o ON est.objectid = o.object_id
LEFT OUTER JOIN sys.databases d ON est.dbid = d.database_id
--WHERE d.name IN ('') --sepecify database(s)
ORDER BY total_elapsed_time / execution_count DESC --Average Elapsted Time
You can easily modify this script to limit the number of records being displayed if you are more interested in only identify user designed stored procedures or simply want to tackle your LRQs one at a time.
The screenshot below displays the
TOP 10 PERCENT of the longest running queries in my
DYNAMICS lab environment.
Another good strategy is to create a log table to store the results of your LRQ script so you can monitor, over time, how often certain queries are hitting the list. It’s possible, and sometimes likely, that even after performance tuning a query, it will still be hitting your LRQ. In some cases, its unavoidable but in many its just an opportunity for improvement.