{"id":1954,"date":"2015-02-11T00:00:00","date_gmt":"2015-02-11T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/checking-the-plan-cache-warnings-for-a-sql-server-database\/"},"modified":"2026-05-08T09:55:56","modified_gmt":"2026-05-08T09:55:56","slug":"checking-the-plan-cache-warnings-for-a-sql-server-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/checking-the-plan-cache-warnings-for-a-sql-server-database\/","title":{"rendered":"SQL Server Plan Cache Warnings: Detecting PlanAffectingConvert, MissingIndexes, and More"},"content":{"rendered":"

SQL Server’s query optimiser annotates query plans with warnings when it identifies issues it cannot resolve automatically: PlanAffectingConvert (implicit conversion that prevents index seeks), ConvertIssue (similar, for non-SARGable conversions), NoStatsFound (statistics missing on referenced columns), MissingIndexes (the optimiser’s guess at indexes that would help), unmatched indexes, and others. <\/b><\/p>\n

These warnings appear inside the cached query plan XML and are visible by inspecting individual plans in SSMS, but extracting them across the entire plan cache requires querying sys.dm_exec_cached_plans and parsing the plan XML. <\/b><\/p>\n

This article provides a table-valued function that filters cached plans by database, then queries to extract specific warning types from the filtered set – making it practical to scan an entire database’s worth of cached plans for a specific issue. Use these queries in development and test environments; running them against a large production plan cache is expensive.<\/b><\/p>\n

Edited: 2nd March 2015<\/p>\n

\n

In this article I will continue to suggest easy ways that one can explore the cached query plans via SQL and XML as explained in ‘Exploring Query Plans in SQL<\/a>‘. I will demonstrate some more tricks, such as how to find query plan warnings inside the cached query plans.<\/p>\n

If you are not checking for warnings in your query plans during development or when testing, then you are missing some valuable information that could make a considerable difference to the performance of your queries. You can, for example, detect implicit conversion, missing statistics, and missing indexes. The table-valued functions I show you here will allow you to see, without having to get involved in XML,\u00a0 all the warnings in plan cache for a particular database, or allow you to select particular types. This method would only be suitable for use in the test or development environment. In production, or where the plan cache is large, it would cause a significant performance hit.<\/p>\n

Query plan Warnings<\/b><\/h2>\n

Query plan Warnings are included inside the query plan to alert you to problems that the query optimizer can’t solve for you. For example, if the query tries to compare one string field with a decimal value, the database engine will use auto-convert to execute the query but will include a warning (PlanAffectingConvert <\/code>warning) because the query could run better if the value was a string instead of a decimal value.<\/p>\n

Until SQL Server 2008, the warnings didn’t appear in the query plan. This meant that you had to use SQL Profiler to see them. Since SQL Server 2008, the warnings are displayed in query plans.<\/p>\n

Another warning example is MissingIndex<\/code>: this warning advises us that the query would run much better with an index that doesn’t exist yet.<\/p>\n

These query plan warnings are valuable information that points to ways that you can make your queries, applications and environment better. Let’s talk about how to find them in the query plan cache.<\/p>\n

First things First: Filtering by Database<\/b><\/h2>\n

Before we get too deeply into selecting the Query-plan Warnings, we will need a way of selecting just the plans for a particular database.<\/p>\n

In the article ‘Exploring Query Plans in SQL<\/a>‘, I demonstrated how to find details about the query plans in the cache, but the cache contains plans for all the databases in the server. What if we would like to see information about only one database? How could we filter the queries by one specific database?<\/p>\n

The query plans in the cache are in XML format following the query plan schema (http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan<\/a>), so if you inspect this you’ll notice that we can use XML queries to find the ‘ColumnReference<\/code>‘ element that has ‘Database'<\/code> attribute. We can filter the query plan by this combination to select the pans of a particular database. The query for this will be:<\/p>\n

with xmlnamespaces\n(default 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\nselect qp.query_plan,qt.text, total_worker_time from sys.dm_exec_query_stats\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\nwhere qp.query_plan.exist('\/\/ColumnReference[@Database=\"[AdventureWorks2012]\"]')=1\norder by total_worker_time desc\n<\/pre>\n
The  xmlnamespaces<\/code> declaration in the beginning of the query simplifies the syntax of the xquery expression, removing the need for the namespace declaration from the xquery expression.<\/p>\n
Creating a table-valued function to filter by database<\/h2>\n
This is an ideal query to transform into a function: The query will became easier to use and we can use the function to create more complex queries filtered on a particular database. For each function we create the need to get too familiar to XML decreases, because the function hides these details.<\/p>\n
The function will be like this:<\/p>\n
-- =============================================\n-- Author:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dennes Torres\n-- Create date: 01\/23\/2015\n-- Description:\u00a0\u00a0\u00a0\u00a0\u00a0 return the query plans in cache for a specific database\n-- =============================================\nCREATE FUNCTION [dbo].[planCachefromDatabase] \n(\u00a0\u00a0\u00a0\u00a0\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Add the parameters for the function here\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @DatabaseName varchar(50)\n)\nRETURNS TABLE \nAS\nRETURN \n(\n\u00a0 with xmlnamespaces\n\u00a0 (default 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\nselect qp.query_plan,qt.text, \n\u00a0 statement_start_offset, statement_end_offset,\n\u00a0 creation_time, last_execution_time,\n\u00a0 execution_count, total_worker_time,\n\u00a0 last_worker_time, min_worker_time,\n\u00a0 max_worker_time, total_physical_reads,\n\u00a0 last_physical_reads, min_physical_reads,\n\u00a0 max_physical_reads, total_logical_writes,\n\u00a0 last_logical_writes, min_logical_writes,\n\u00a0 max_logical_writes, total_logical_reads,\n\u00a0 last_logical_reads, min_logical_reads,\n\u00a0 max_logical_reads, total_elapsed_time,\n\u00a0 last_elapsed_time, min_elapsed_time,\n\u00a0 max_elapsed_time, total_rows,\n\u00a0 last_rows, min_rows,\n\u00a0 max_rows \nfrom sys.dm_exec_query_stats\n\u00a0 CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\n\u00a0 CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\n\u00a0 where qp.query_plan.exist('\/\/ColumnReference[fn:lower-case(@Database)=fn:lower-case(sql:variable(\"@DatabaseName\"))]')=1\n)\n\u00a0\nGO\n<\/pre>\n
As well as the use of the ‘WITH  xmlnamespaces'<\/code> clause, I also used the \u00a0lower-case<\/code> function, so the parameter becomes case-insensitive. This can prevent all manner of confusion.<\/p>\n
We can now adopt this function for a whole range of database-specific queries. A simple query using the function might be:<\/p>\n
\u00a0select query_plan,[text],total_worker_time\n\u00a0\u00a0 from dbo.planCacheFromDatabase('[AdventureWorks2012]')\n\u00a0\u00a0 order by total_worker_time desc\n<\/pre>\n
Look. No XML at all. Now it has become a lot easier to return from the plan cache only the queries from a single database. However, as you’ll see in a moment, there is some extra magic that can be used with this query by means of XQuery on the table-valued output of this function.<\/p>\n
\"2146-clip_image002.png\"<\/p>\n
Finding Warnings in the Plan Cache<\/h2>\n
Test Environment<\/b><\/h3>\n
I will use the AdventureWorks2012 database to test the functions, but with additional tables created by Adam Machanic in his blog post ‘Thinking Big (Adventure)’<\/a><\/p>\n
 Finding PlanAffectingConvert Warning<\/b><\/h3>\n
Grant Fritchey has written about PlanAffectingConvert. <\/code>\u00a0in his article ‘The Seven Sins against TSQL Performance<\/a>‘, it’s a query plan warning about there being an implicit convertion in the plan.<\/p>\n
Implicit conversion happens when we try to compare information of different types. For example, if we try to compare a string field with an integer SQL Server will convert the information implicit, but this has a cost and can cause problems to the query, so we receive a warning about the conversion.<\/p>\n
Let’s use the same sample query used by Grant in his article, we can notice the warning in the query plan:<\/p>\n
SELECT\u00a0 e.BusinessEntityID,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.NationalIDNumber\n\u00a0 FROM\u00a0\u00a0HumanResources.Employee AS e\n\u00a0 WHERE e.NationalIDNumber = 112457891;\n<\/pre>\n
\"2146-clip_image004.png\"<\/p>\n
After running this query, the plan will be in the cache and it has the PlanAffectingConvert<\/code> warning in it. So, let’s create a query using our new ‘planCacheFromDatabase<\/code>‘ function to find PlanAffectingConvert <\/code>in the plan cache:<\/p>\n
with xmlnamespaces\n\u00a0 (default 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\nselect query_plan,text, total_worker_time \n\u00a0 from dbo.planCacheFromDatabase('[AdventureWorks2012]')\n\u00a0 where query_plan.exist('\/\/PlanAffectingConvert')=1\n\u00a0 order by total_worker_time desc\n<\/pre>\n
As you can see, all I had to do was use the previous created function, planCacheFromDatabase<\/code> and filter the query plans to find which one has the PlanAffectingConvert <\/code>warning.<\/p>\n
Finding Warnings<\/b><\/h3>\n
 PlanAffectingConvert<\/code> is only one of many warnings that can appear in query plans. We need to retrieve from the cache the query plans that have warnings, with the information of which and how many warnings each query plan has. With this information we can analyze and change the query to solve the problems.<\/p>\n
It’s important to notice that there are different kinds of warnings. These warnings can appear in any element of the query plan, so there are many ‘warnings’ elements in the query plan schema.<\/p>\n
One example is the ColumnWithNoStatistics<\/code> warning. While ‘PlanAffectingConvert'<\/code> appeared over the ‘Select<\/code>‘ element in the plan, ‘ColumnWithNoStatistics'<\/code> will appear over ‘scan<\/code>‘ elements if we do a query over a column with no statistics.<\/p>\n
Statistics are very important, even in fields with no index, because they help the query optimizer to choose the best plan for the query. When we do a query over a field with no statistics and the query could be improved with them, the warning ‘ColumnWithNoStatistics'<\/code> is included in the query plan.<\/p>\n
It’s important to notice that SQL Server databases have the configuration ‘Auto Create Statistics<\/i>‘ with the default as true<\/code>. This means that we will never see this warning, because SQL Server will automatically create the statistics when needed.<\/p>\n
This is the best setting in the production environment, even though it will affect all the clients while SQL Server creates the statistics over big tables.<\/p>\n
For the Test or development environment, it is possible to disable ‘Auto Create Statistics’<\/i> and to look for ‘ColumnWithNoStatistics<\/code>‘ in the cache to find where you would need to create statistics (or even indexes).<\/p>\n
To simulate this warning we need to temporarily disable ‘Auto Create Statistics’<\/i> in AdventureWorks database, it will be like this:<\/p>\n
alterdatabase adventureworks2012 setauto_Create_statistics off<\/pre>\n
After disabling the ‘auto create statistics’<\/i> we can generate this warning with the following query:<\/p>\n
select * from bigtransactionHistory\n\u00a0 where TransactionDate >'2006\/01\/01' and transactiondate <'2006\/02\/20'\n<\/pre>\n
\"2146-clip_image006.png\"<\/p>\n
Make sure that, once you’ve run your tests, you re-enable\u00a0\u00a0‘Auto Create Statistics’<\/i>\u00a0.<\/p>\n
Now that we see the importance of warnings like ‘ColumnWithNoStatistics<\/code>‘, ‘PlanAffectingQuery<\/code>‘ and many others, let’s create a query to find which and how many warnings each query plan in the cache has:<\/p>\n
with xmlnamespaces\n\u00a0 (default 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan'),\n\u00a0 qry as\n\u00a0 (\n\u00a0\u00a0\u00a0 select [text],\n\u00a0\u00a0\u00a0 cast(nos.query('local-name(.)') as varchar) warning, total_Worker_time\n\u00a0\u00a0\u00a0 from dbo.planCacheFromDatabase('[AdventureWorks2012]')\n\u00a0 CROSS APPLY query_plan.nodes('\/\/Warnings\/*') (nos)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\nselect [text],warning,count(*) qtd,max(total_worker_time) total_worker_time \n\u00a0 from qry \n\u00a0 group by [text],warning\n\u00a0 order by total_worker_time desc\n<\/pre>\n
Let’s see the tricks I used in the above query:<\/p>\n
    \n
  • To combine in the same query the xmlnamespaces<\/code> and one CTE (Common Table Expression) I used only one ‘with’ clause and a comma to separate the xmlnamespaces<\/code> and the CTE<\/li>\n
  • The xquery expression doesn’t include the path of the ‘Warnings’ element because the ‘Warnings’ element can appear in different paths in the schema.<\/li>\n
  • Below the ‘Warnings’ element each warning is a different element and the name of the element is the name of the warning. The ‘local-name<\/code>‘ function retrieves the name of the element (the warning name) for the query.<\/li>\n<\/ul>\n
    Each query plan can have many warnings of many different kinds, so the above query counts how many warnings of each kind each plan has.<\/p>\n
    \"2146-clip_image008.png\"<\/p>\n
    The above query is a good candidate to become a function, so here it is:<\/p>\n
    \t\t-- =============================================\n\t\t-- Author:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dennes Torres\n\t\t-- Create date: 01\/24\/2015\n\t\t-- Description:\u00a0\u00a0\u00a0\u00a0\u00a0 Return the warnings in the query plans in cache\n\t\t-- =============================================\n\t\tCREATE FUNCTION [dbo].[FindWarnings] \n\t\t(\u00a0\u00a0\u00a0\u00a0\u00a0 \n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- Add the parameters for the function here\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @DatabaseName varchar(50) \n\t\t)\n\t\tRETURNS TABLE \n\t\tAS\n\t\tRETURN \n\t\t(\n\t\t\u00a0 with xmlnamespaces\n\t\t\u00a0\u00a0\u00a0 (default 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan'), \n\t\t\u00a0\u00a0 qry as\n\t\t\u00a0\u00a0\u00a0 (select [text],\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cast(nos.query('local-name(.)') as varchar) warning, total_Worker_time\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from dbo.planCacheFromDatabase(@DatabaseName)\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY query_plan.nodes('\/\/Warnings\/*') (nos)\n\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\t\tselect [text],warning,count(*) qtd,max(total_worker_time) total_worker_time \n\u00a0 from qry \n\t\t\u00a0 group by [text],warning\n\t\t)\n\t\t\u00a0\n\t\tGO\n<\/pre>\n
    After the function has been created, we can retrieve all the warnings in the cache for the AdventureWorks database, and it is as simple as the following query:<\/p>\n
    \t\tselect * from dbo.FindWarnings('[AdventureWorks2012]')\n\t\t\u00a0 order by total_worker_time desc\n<\/pre>\n
    … And once again, no XML in sight!<\/p>\n
    \u00a0Summary<\/b><\/h1>\n
    In this article we talked about the importance of warnings in query plan cache, and how we can use this information in development, or while testing, to make our environment better. You saw queries to find query plans with warnings in the plan cache and we created functions so you can use these techniques without the need to create XML queries at the time that you are investigating warnings.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    Detect query plan warnings in the SQL Server plan cache – PlanAffectingConvert (implicit conversion), ConvertIssue, NoStatsFound, MissingIndexes, and others – using a table-valued function that filters cached plans by database. Includes complete T-SQL implementations and guidance on which warnings indicate genuine performance problems.…<\/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":[143531],"tags":[4168,5084,4179,4150,4151,4252],"coauthors":[6810],"class_list":["post-1954","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-optimiser","tag-source-control","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1954","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=1954"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1954\/revisions"}],"predecessor-version":[{"id":110392,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1954\/revisions\/110392"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1954"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1954"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1954"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1954"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}