Mining XML query plans
As we mentioned in the sys.dm_exec_query_plan section, query execution plans are stored as XML, and the sys.dm_exec_query_plan DMV returns them as a proper XML data type. This allows us to leverage XML Path Language (XPath) to generate queries that can search for elements and attributes within the query execution plans. Using these XPath queries, or XQueries, we can search for common query performance issues across all the query execution plans in the cache, rather than having to examine each graphical plan individually. In this section, we will cover a few common scenarios, but be sure to reference the Mining-PlanCache section of the Tiger Toolbox (https://aka.ms/tigertoolbox) for more examples.
Tip
The queries shown in this section can be used individually to search for specific issues, but running the entire BPCheck script from the Tiger Toolbox (https://aka.ms/bpcheck) will gather all this information and more in a single resultset.
