In this article I will share with you a script extracting longest running and most CPU using query statements and their plans. As a starting point I used this article. It explains in details how to extract query statements from a plan. As you can check it is very useful, but imagine a situation where you do have the query, but still you are not allowed to play with it (even execute it) but still need information on plan, operations included and some optimizer info as optimization level, cost, estimated number of rows, etc. As this info is already stored in SQL Server and exposed through DMVs it would be good to be able to take a deeper look at what is going on.
So let’s get into details.
The backbone of the query is from the article mentioned above. I simply added the TOP statements at the bottom and now I will describe what they mean and how to interpret them. First part is showing currently running statements, their plans and some details on plan execution and optimization.
Second part extracts top 10 statements by average duration (again including their plans and optimization level).
Third and fourth parts are focusing on top 50 by CPU time queries and their plans.
All four parts are returning the query plan in XML format. See and example of the output:
So having this information you can examine the plan, how the optimizer handled the statement and look for CPU intensive operations such as Hash Match for example. By clicking over the query plan you get an XML opened in new query window. You can save it as .sqlplan and analyze it further, or if you want you can query the result directly for operations as Hash Match, Sort, etc.
The good of this method for examination and troubleshooting is that you are actually getting the information that already happened and stored in SQL Server. Usually optimizations and monitoring is performed in controlled environment and more or less it does not reflect reality at 100%. But using such method would allow you to explain performance downgrades and provide some good clues to the application team or if there is some other issue.
The script you can download here.
