One of the queries I use the most, is the pplan-cache query from this post:
Simple query to check the recent performance history
The good thing about the query is that it shows information that could otherwise require a lot of work (collecting and analysing traces files). And the query does not need any advance work or setup. It can just be run. So it's a very easy way to receive information from a system, which is often very useful as a first step in troubleshooting performance. For more details about the result of the query, refer to the post linked above.
Below is a slightly enhanced version of the query. Since the query is based on the cache of compiled query plans, it is not a big step to extend it to also include the query plan itself, and even extract certain information from the plan if you know what you are looking for.
So this query does the same as the original one, but with the following additions:
- New column query_plan is included. It shows the query plan as xml which may be difficult to read, but it contains the full plan. Note: Some times, for no apparent reason, the query plan can't be retrieved, so it may not show the query plan on all lines.
- cursor_type, just as an example of how to retrieve data from the query plan. If you find other things in the plans that may be interesting, then use the syntax to retrieve this further information.
Here is the updated query
SELECTTOP 100
SUBSTRING(st.text,(qs.statement_start_offset/2)+ 1,
((CASE statement_end_offset
WHEN-1 THENDATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2)+ 1)as statement_text,
execution_count,
case
when execution_count = 0 thennull
else total_logical_reads/execution_count
<FONT color=#0000ff size=2>
end