My latest Spatial-Data post SQL Server CPU utilization in Graphical form was pretty popular and I've decided to continue doing SQL Server reporting using spatial capabilities of SSMS.
This time I want to show a query to report top 10 SQL Serve queries in your system.
Will start with a diagram:
At first there is nothing interesting until you learn how to interpret the diagram.
The diagram is reporting three main parameters of SQL Server queries stored in the query plan cache:
1. Horizontal coordinate reports amount of CPU used by particular query. It goes from Left to Right. On this diagram the winner is query #1. It's execution time is way higher than for any other query. Obviously, Query #1 is my CPU eater.
2. Vertical coordinate reports amount of I/O used by particular query.It goes from Bottom to a Top. On this diagram the winner by I/O is query #2. It's I/O usage is higher than for any other query. That is my biggest I/O consumer.
3. Size of a circle indicates number of query executions. Her my winner is query #3. That parameter might not represent well the usage of my resources, but it is something I have to be aware of.
Now, here is a query to produce that diagram:
Besides of the diagram that query produces a list of most crucial query characteristics:
As you can guess and see in the query, all parameters are presented in Logarithmic scale to keep sizes and coordinates in manageable format.
What Next?When we identified the most annoying queries in our system we can run another query to get actual queries' texts and plans. You just have to use value from "query_hash" column to identify the query you want to do a research on:
Here is the result:
I can click on Query plan XML to get query plan in graphical form:
Hope you find it useful for troubleshooting SQL Server performance.