{"id":1924,"date":"2014-12-31T00:00:00","date_gmt":"2014-12-31T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/exploring-query-plans-in-sql\/"},"modified":"2021-08-24T13:39:39","modified_gmt":"2021-08-24T13:39:39","slug":"exploring-query-plans-in-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/exploring-query-plans-in-sql\/","title":{"rendered":"SQL Server Query Plans: Finding Scan and Lookup Problems with DMVs and XQuery"},"content":{"rendered":"<p><b>SQL Server&#8217;s plan cache holds the execution plans of recently-run queries &#8211; a rich source of diagnostic information for identifying performance problems across an entire database rather than one query at a time. This article walks through using Dynamic Management Views (primarily sys.dm_db_index_usage_stats) combined with XQuery against the cached XML execution plans to find two specific problem patterns: (1) indexes suffering from excessive scans rather than seeks, indicating missing indexes or poorly-written queries; (2) index lookups that could be eliminated by extending the index&#8217;s included columns. The article shows the exploratory queries first, then combines them into a practical diagnostic routine that can be run on any SQL Server database to produce an actionable list of optimisation candidates. Techniques transfer to other plan-cache diagnostic questions &#8211; finding queries with specific join types, identifying implicit conversions, spotting missing-index hints &#8211; using the same XQuery-against-cached-plans pattern. This is SQL Server content; for PostgreSQL EXPLAIN analysis, see a dedicated PostgreSQL resource.<\/b><\/p>\n<div class=\"article-content\">\n<p>The Dynamic Management View (DMV) <strong>sys.dm_db_index_usage_stats<\/strong> reports on the usage of indexes, so we can use this DMV to find which indexes could have problems. This DMV can give totals of some kinds of index usages, such as scans, seeks and lookups, so we can use this DMV to find not only indexes but databases which require some attention.<\/p>\n<p>Let&#8217;s see some important information that we can find in this DMV:<\/p>\n<ul>\n<li><strong>Scans: <\/strong>Scans are usually terrible for performance, because the entire index is searched to find the information. It is likely that you&#8217;ll need to optimize any queries that use an index that is subject to lot of scan, this could involve changing the queries or on the index.<\/li>\n<li><strong>Seeks :<\/strong> Seek is the opposite of scan, and is a very good use of the index, so we can compare the proportion of seeks to scans and look for those indexes that have more scan&#8217;s than seeks as being a potential source of problems.<\/li>\n<li><strong>Lookups:<\/strong> Lookups happen when an operation over a nonclustered index needs to retrieve additional fields for the query, usually using the clustered index. This is an expensive operation, so the query optimizer can decide, sometimes, to do a clustered index scan instead of a lookup. In this last case, of course, it will count as a scan rather than lookup, so the number of lookups includes only those that aren&#8217;t too expensive to become scans, but still aren&#8217;t good for performance.<\/li>\n<\/ul>\n<p>I&#8217;m simplifying the optimization techniques, but in this article the focus is on how to find opportunities for optimization.<\/p>\n<p>The <strong>sys.dm_db_index_usage_stats<\/strong> DMV has information for user actions and system actions, so we have <strong>user_scans<\/strong> and <strong>system_scans<\/strong>, but we can ignore the system information.<\/p>\n<p>The first step is to identify the database that is most affected by these problems. We need a query for each type of problem, whether scan or lookup (a seek is not a problem, but the ratio of seeks to scans is information that will help us to identify scan problems).<\/p>\n<h2>Identifying Scan Problems<\/h2>\n<p>Let&#8217;s see the query to find the database with most scan problems :<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select db_name(database_id),max(user_scans) bigger,\r\navg(user_scans) average\r\nfrom sys.dm_db_index_usage_stats\r\ngroup by db_name(database_id)\r\norder by average desc\r\n<\/pre>\n<p>It doesn&#8217;t make much sense to sum the <strong>user_scans<\/strong> of different indexes, so we get the maximum and the average of user scans to find which database deserves our attention.<\/p>\n<p>Here the result for my SQL Server:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2114-Imagem1-3308a874-83c6-41df-b106-5cbf48d6e380.png\" alt=\"2114-Imagem1-3308a874-83c6-41df-b106-5cb\" \/><\/p>\n<p>We can see that there are a lot of scans in the &#8216;adventureworks2012&#8217; database<\/p>\n<p>Only after we&#8217;ve selected one particular database can we go on to get the name of the indexes that might have problems. To do this, we now need to join this DMV information with <strong>sys.indexes<\/strong> information to get the name of the index.<\/p>\n<p>This query needs to run in the chosen database (of course, you will change the database name on the query):<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">Use adventureworks2012 \/* &lt;&lt;&lt;&lt;------- you will need to change the  database name *\/\r\nselect object_name(c.object_id) as [table],\r\nc.name  as [index],user_scans,user_seeks,\r\n    case a.index_id\r\n    when 1 then 'CLUSTERED'\r\n    else 'NONCLUSTERED'\r\n    end as type\r\nfrom sys.dm_db_index_usage_stats a\r\n      inner join sys.indexes c\r\non\u00a0 c.object_id=a.object_id and c.index_id=a.index_id\r\n    and database_id=DB_ID('AdventureWorks2012')\u00a0\u00a0 \/*  &lt;&lt;&lt;&lt;------- you will need to \r\nchange the database name *\/\r\norder by user_scans desc\r\n<\/pre>\n<div class=\"indented tips\">\n<p class=\"note\">Side note: These examples were created using Adventureworks2012 database, that you can download from <a href=\"https:\/\/msftdbprodsamples.codeplex.com\/releases\/view\/55330\">https:\/\/msftdbprodsamples.codeplex.com\/releases\/view\/55330<\/a>, but the tables &#8216;bigproduct&#8217; and &#8216;bigtransactionhistory&#8217; were created by Adam Machanic and you can find the script at <a href=\"http:\/\/sqlblog.com\/blogs\/adam_machanic\/archive\/2011\/10\/17\/thinking-big-adventure.aspx\">http:\/\/sqlblog.com\/blogs\/adam_machanic\/archive\/2011\/10\/17\/thinking-big-adventure.aspx<\/a>. The scan activity was generated using SQL Query Stress tool, also from Adam Machanic and you can find it at <a href=\"http:\/\/dataeducation.com\/sqlquerystress-the-source-code\/\">http:\/\/dataeducation.com\/sqlquerystress-the-source-code\/<\/a>.<\/p>\n<\/div>\n<p>Notice that I also used the <strong>index_id<\/strong> to identify the index as clustered or nonclustered. I also included seek information so we can compare scans and seeks to decide which index deserves our attention.<\/p>\n<p>As a result we can clearly notice that the table &#8216;<strong>bigproduct<\/strong>&#8216; and its clustered index have a lot of scans that we need to focus on.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2114-Imagem2-e7c4d9ee-d11a-473c-88c4-d8e2fb99bd8c.png\" alt=\"2114-Imagem2-e7c4d9ee-d11a-473c-88c4-d8e\" \/><\/p>\n<p>Both queries that I&#8217;ve used so far were done to investigate the scan problem, but you can use the same queries for the lookup problem: You just need to change the <strong>user_scans<\/strong> field to <strong>user_lookups<\/strong> field.<\/p>\n<h2>Finding the query plans causing scans<\/h2>\n<p>By using these queries, we found which database and indexes deserve our attention, but how can we find those query plans that will identify the queries that are responsible for causing these problems?<\/p>\n<p>As a second step we can use SQL to identify the query plans in cache that have these problems, so we will then be able to work out which queries we need to optimize.<\/p>\n<p>Using the DMV <strong>sys.dm_exec_query_stats<\/strong> we can retrieve all the queries in cache and identify those query plans with problems.<\/p>\n<p>This DMV has a handle that we can use to retrieve the query plan and a handle that we can use to retrieve the text of the query. For these we will use <strong>sys.dm_exec_query_plan<\/strong> and <strong>sys.dm_exec_sql_text<\/strong> Dynamic Management Function (DMF), respectively. We need to do a CROSS APPLY with these.<\/p>\n<p>The query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select qp.query_plan,qt.text from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\n<\/pre>\n<p>The query_plan field is in XML, but if you view the query results as &#8216;<em>result to grid&#8217;<\/em>, the query window in SSMS recognizes the schema and shows the plan graphically for us if you click on the link. That&#8217;s good for ad-hoc exploration by inspecting single plans, but not for systematically searching through the plans for those with a particular criterion. Unless we can filter the results based on the XML, we would need to look one by one. So, the best option is to use Xquery.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2114-Imagem3-eb6ace7b-f789-4faa-a287-828c1244ee16.png\" alt=\"2114-Imagem3-eb6ace7b-f789-4faa-a287-828\" \/><\/p>\n<p class=\"caption\">If we click the query_plan field we will see the graphical query plan<\/p>\n<p>In order to sensibly use XQuery to search through XML, we need information about the schema that the XML uses.<\/p>\n<p>The schema of this XML is published at http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan<\/p>\n<p>There are plenty of possibilities for finding execution problems using this method, but I can only show a few, and to do different searches, you will need to study the schema.<\/p>\n<p>By looking through the schema, we find that we have a <strong>RelOp<\/strong> element with a <strong>LogicalOp<\/strong> attribute that we can use to find all plans that have index scans or table scans. Let&#8217;s see the query :<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select qp.query_plan,qt.text from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nqplan=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n            \/\/qplan:RelOp[@LogicalOp=\"Index Scan\"\r\n            or @LogicalOp=\"Clustered Index Scan\"\r\n            or @LogicalOp=\"Table Scan\"]')=1\r\n<\/pre>\n<p>The XML of the query plan is typed, so we need to declare the namespace in order to use xquery. The schema is a bit complicated and you need to be careful when you create your own queries. For example, it would be a mistake to query for the element<strong>IndexScan<\/strong>, because the element <strong>IndexScan<\/strong>is used for all index operations, including seeks and lookups.<\/p>\n<p>The next step is to filter the result by a specific index. We already found the indexes with most scans, so now we can find out which plans are causing this. Following the schema, the name of the index is an attribute in the Object element, inside the <strong>IndexScan<\/strong> element that is inside the <strong>RelOp<\/strong>element.<\/p>\n<p>So this will be the query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select qp.query_plan,qt.text from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nqplan=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n            \/\/qplan:RelOp\/qplan:IndexScan\/qplan:Object[@Index=\"[pk_bigProduct]\"]')=1\r\n<\/pre>\n<p>If this query results in too much plans, we can use other fields in <strong>sys.dm_exec_query_Stats<\/strong> to find the plans that we need to optimize. For example, we can use <strong>total_worker_time<\/strong> field to order the result by CPU time, like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select qp.query_plan,qt.text,total_worker_time from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nqplan=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n            \/\/qplan:RelOp[@LogicalOp=\"Index Scan\"\r\n            or @LogicalOp=\"Clustered Index Scan\"\r\n            or @LogicalOp=\"Table Scan\"]\/qplan:IndexScan\/qplan:Object[@Index=\"[pk_bigProduct]\"]')=1\r\norder by total_worker_time desc\r\n<\/pre>\n<p>In my simulation, the result is only one query plan. We have the text of the offending query and we can see the plan graphically if the query results as &#8216;<em>result to grid&#8217;<\/em> and we click on the link. SSMS shows even the &#8216;missing index&#8217; information, making it even easier to solve the problem.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2114-Imagem4-26466a68-efe7-411a-b70d-881023740bb7.png\" alt=\"2114-Imagem4-26466a68-efe7-411a-b70d-881\" \/><\/p>\n<p class=\"caption\">We found exactly the query causing the problem and&#8230;<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2114-Imagem5-98aafb57-3e00-4428-acfb-379d91d5ee6a.png\" alt=\"2114-Imagem5-98aafb57-3e00-4428-acfb-379\" \/><\/p>\n<p class=\"caption\">&#8230; we can see the graphical query plan and the missing index suggestion<\/p>\n<h2>Identifying Lookup Problems<\/h2>\n<p>Another example of the use of DMVs is to find the plans with index lookups. If we change the first query to calculate the amount of lookups we will notice a lot of lookups in &#8216;adventureworks2012&#8217;:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select db_name(database_id),max(user_lookups) bigger,\r\navg(user_lookups) average\r\nfrom sys.dm_db_index_usage_stats\r\ngroup by db_name(database_id)\r\norder by average desc\r\n<\/pre>\n<p>Trying to find the index that is causing this will reveal to us where to focus:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">use adventureworks2012 \/* &lt;&lt;&lt;&lt;&lt;------- you will need to change the database name *\/\r\nselect object_name(c.object_id) as [table],\r\nc.name as [index],user_lookups,\r\n     case a.index_id\r\n     when 1 then 'CLUSTERED'\r\n     else 'NONCLUSTERED'\r\n     end as type\r\nfrom sys.dm_db_index_usage_stats a\r\n\tinner join sys.indexes c\r\non  c.object_id=a.object_id and c.index_id=a.index_id\r\n    and database_id=DB_ID('AdventureWorks2012')   \/* &lt;&lt;&lt;&lt;&lt;------- you will need to \r\nchange the database name *\/\r\norder by user_lookups desc\r\n<\/pre>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2114-Imagem7-bcc4f76f-75d8-4c0f-8e89-0c9f98f44370.png\" alt=\"2114-Imagem7-bcc4f76f-75d8-4c0f-8e89-0c9\" \/><\/p>\n<p class=\"caption\">Adventureworks2012 also has lookup problems<\/p>\n<p>Finally, to find query plans with lookups we need to filter by the lookup attribute in the <strong>IndexScan<\/strong> element. The new query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select qp.query_plan,qt.text, plan_handle,query_plan_hash from \r\nsys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nAWMI=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n\r\n\/\/AWMI:IndexScan[@Lookup]\/AWMI:Object[@Index=\"[PK_TransactionHistory_TransactionID]\"]')=1\r\n<\/pre>\n<p>In this example we will find out that, if we remove two of the fields , the &#8216;<strong>quantity<\/strong>&#8216; and &#8216;<strong>actualcost<\/strong>&#8216; fields, from the query, then the lookup will disappear. Of course, this might not be possible, in which case we&#8217;d need to look for other solution, but this is not the focus of this article.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2114-Imagem8-f85c673c-085e-4bd8-9a9a-2b681f91277f.png\" alt=\"2114-Imagem8-f85c673c-085e-4bd8-9a9a-2b6\" \/><\/p>\n<p class=\"caption\">We can see the cause of the lookup<\/p>\n<h2>Turning exploratory queries into a practical solution<\/h2>\n<p>It&#8217;s nice to see that we can search the query plans in cache to find problems, but it isn&#8217;t practical to use queries like this in daily optimizations. So, how can we make this more practical? Simple: We can create a reusable function so that we would then no longer need to worry about the complexity of the query syntax any more. So, we can create queries for each of the main problems in query plans and then create one function for each query.<\/p>\n<p>The XQuery &#8216;<strong>exist()<\/strong>&#8216; method only accepts constants as parameters. So, the only solution to turn the name of the index into a variable is to use <strong>sql:variable<\/strong> expression to access a variable from inside the xquery expression.<\/p>\n<p>Here is the resulting function:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">Create FUNCTION [dbo].[FindScans] \r\n(\t\r\n\t-- Add the parameters for the function here\r\n\t@Index varchar(50)\r\n)\r\nRETURNS TABLE \r\nAS\r\nRETURN \r\n(\r\nselect qp.query_plan,qt.text,total_worker_time from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nqplan=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n            \/\/qplan:RelOp[@LogicalOp=\"Index Scan\"\r\n            or @LogicalOp=\"Clustered Index Scan\"\r\n            or @LogicalOp=\"Table Scan\"]\/qplan:IndexScan\/qplan:Object[fn:lower-case(@Index)=fn:lower-\r\ncase(sql:variable(\"@Index\"))]')=1\r\n)\r\nGO\r\n<\/pre>\n<p>Notice that I included the function &#8216;<strong>fn:lower-case<\/strong>&#8216;, otherwise the function would be case-sensitive like xml.<\/p>\n<p>Now the query to find the plans in the cache that do index scan over a specific index becomes as simple as a single select:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">select * from dbo.FindScans('[pk_bigProduct]')\r\n<\/pre>\n<p>The same applies to lookup problems :<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">CREATE FUNCTION FindLookups \r\n(\t\r\n\t-- Add the parameters for the function here\r\n\t@Index varchar(50) \r\n)\r\nRETURNS TABLE \r\nAS\r\nRETURN \r\n(\r\nselect qp.query_plan,qt.text from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nAWMI=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n            \/\/AWMI:IndexScan[@Lookup]\/AWMI:Object[fn:lower-case(@Index)=fn:lower-\r\ncase(sql:variable(\"@Index\"))]')=1\r\n\r\n)\r\nGO\r\n\r\nselect * from dbo.FindLookups('[PK_TransactionHistory_TransactionID]')\r\n<\/pre>\n<p>Now, the final touch: As we are creating re-usable functions, the best approach is to return more fields from <span class=\"codegreen\">sys.dm_exec_query_stats<\/span> so the functions became more flexible.<\/p>\n<p>The final functions will be like these:<\/p>\n<pre class=\"lang:tsql theme:ssms2012\">   Create FUNCTION [dbo].[FindScans] \r\n(\t\r\n\t-- Add the parameters for the function here\r\n\t@Index varchar(50)\r\n)\r\nRETURNS TABLE \r\nAS\r\nRETURN \r\n(\r\nselect qp.query_plan,qt.text, \r\nstatement_start_offset, statement_end_offset,\r\ncreation_time, last_execution_time,\r\nexecution_count, total_worker_time,\r\nlast_worker_time, min_worker_time,\r\nmax_worker_time, total_physical_reads,\r\nlast_physical_reads, min_physical_reads,\r\nmax_physical_reads, total_logical_writes,\r\nlast_logical_writes, min_logical_writes,\r\nmax_logical_writes, total_logical_reads,\r\nlast_logical_reads, min_logical_reads,\r\nmax_logical_reads, total_elapsed_time,\r\nlast_elapsed_time, min_elapsed_time,\r\nmax_elapsed_time, total_rows,\r\nlast_rows, min_rows,\r\nmax_rows from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nqplan=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n            \/\/qplan:RelOp[@LogicalOp=\"Index Scan\"\r\n            or @LogicalOp=\"Clustered Index Scan\"\r\n            or @LogicalOp=\"Table Scan\"]\/qplan:IndexScan\/qplan:Object[fn:lower-\r\ncase(@Index)=fn:lower-case(sql:variable(\"@Index\"))]')=1\r\n)\r\nGO\r\n\r\nCreate FUNCTION FindLookups \r\n(\t\r\n\t-- Add the parameters for the function here\r\n\t@Index varchar(50) \r\n)\r\nRETURNS TABLE \r\nAS\r\nRETURN \r\n(\r\nselect qp.query_plan,qt.text, \r\nstatement_start_offset, statement_end_offset,\r\ncreation_time, last_execution_time,\r\nexecution_count, total_worker_time,\r\nlast_worker_time, min_worker_time,\r\nmax_worker_time, total_physical_reads,\r\nlast_physical_reads, min_physical_reads,\r\nmax_physical_reads, total_logical_writes,\r\nlast_logical_writes, min_logical_writes,\r\nmax_logical_writes, total_logical_reads,\r\nlast_logical_reads, min_logical_reads,\r\nmax_logical_reads, total_elapsed_time,\r\nlast_elapsed_time, min_elapsed_time,\r\nmax_elapsed_time, total_rows,\r\nlast_rows, min_rows,\r\nmax_rows from sys.dm_exec_query_stats\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\r\nwhere qp.query_plan.exist('declare namespace \r\nAWMI=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan\";\r\n            \/\/AWMI:IndexScan[@Lookup]\/AWMI:Object[fn:lower-case(@Index)=fn:lower-\r\ncase(sql:variable(\"@Index\"))]')=1\r\n\r\n)\r\n\r\nGO\r\n<\/pre>\n<h2>Summary<\/h2>\n<p>We can start by building a lot of queries that exploit the power of XQuery to search the execution plans stored in in cache to find opportunities to optimize queries and avoid potential problems. We can then turn these queries into functions to make them reusable. When creating new functions, it is well worth studying the schema of the execution plan and exercising due caution about the performance of the queries when using them on a production server. Though they are very handy in tracking performance problems, these queries aren&#8217;t fast.<\/p>\n<p>Do you have any suggestions for queries over the cached execution plans? Please, share the queries you create using this technique or your suggestions for new queries over the cache.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Use SQL Server DMVs (sys.dm_db_index_usage_stats) and XQuery against cached query plans to find queries causing scan and lookup problems. Converts exploratory queries into a practical diagnostic tool for identifying optimisation candidates across a database. Complete T-SQL scripts included.&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4170,4750,4783,5993,4150,4151],"coauthors":[6810],"class_list":["post-1924","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database-administration","tag-dynamic-management-views","tag-execution-plans","tag-query-plans","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1924","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1924"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1924\/revisions"}],"predecessor-version":[{"id":76710,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1924\/revisions\/76710"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1924"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1924"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1924"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1924"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}