NAV Sustained Engineering Team Blog: Simple query to check the recent performance history II - now including Query Plan information

[Blog bot]

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


endas avg_logical_reads,


last_logical_reads,


min_logical_reads,


max_logical_reads,


plan_handle,


ph.query_plan,


-- Query Plan Information


casewhen


ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]')= 0


then''else


ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtCursor/ns:CursorPlan/@CursorRequestedType)[1]','nvarchar (max)')


endas cursor_type


FROMsys.dm_exec_query_statsas qs


CROSSAPPLY sys.dm_exec_sql_text(qs.sql_handle)as st


CROSSAPPLY sys.dm_exec_query_plan(qs.plan_handle)as ph


ORDERBY max_logical_reads DESC


:





What I would really like, is to receive feedback on what parts of the query plans are useful. Then extend the query even further to include as much useful information as possible. For exdample, in some cases the query plan contains missing index-information. The lines below can be copied into the query above to include this information. Any feedback on whether this is useful or not, and whether other information from the query plans can be useful is really very welcome. You can add comments about this below.





-- Missing Indexes


,casewhen ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup)[1]')= 0


then''


else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/@Impact)[1]','nvarchar (max)')


endas missing_index_impact,





casewhen ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]')= 0


then''


else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/@Table)[1]','nvarchar(max)')


endas missing_index_table,


casewhen ph.query_plan.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]')= 0


then''


else ph.query_plan.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:MissingIndexes/ns:MissingIndexGroup/ns:MissingIndex/ns:ColumnGroup/ns:Column/@Name)[1]','nvarchar(max)')


endas missing_index_field











Lars Lohndorf-Larsen


Escalation Engineer


These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.



Читать дальше