Identifying The Longest Running Queries In Your Database

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.

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.

lrq_top_10_percent_screenshot

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.