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‘. I will demonstrate some more tricks, such as how to find query plan warnings inside the cached query plans.

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,  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.

Query plan Warnings

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 warning) because the query could run better if the value was a string instead of a decimal value. 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. Another warning example is MissingIndex: this warning advises us that the query would run much better with an index that doesn’t exist yet. 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.

First things First: Filtering by Database

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. In the article ‘Exploring Query Plans in SQL‘, 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? The query plans in the cache are in XML format following the query plan schema (http://schemas.microsoft.com/sqlserver/2004/07/showplan), so if you inspect this you’ll notice that we can use XML queries to find the ‘ColumnReference‘ element that has ‘Database' attribute. We can filter the query plan by this combination to select the pans of a particular database. The query for this will be: The xmlnamespaces 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.

Creating a table-valued function to filter by database

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. The function will be like this: As well as the use of the ‘WITH xmlnamespaces' clause, I also used the  lower-case function, so the parameter becomes case-insensitive. This can prevent all manner of confusion. We can now adopt this function for a whole range of database-specific queries. A simple query using the function might be: 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.

Finding Warnings in the Plan Cache

Test Environment

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)’

Finding PlanAffectingConvert Warning

Grant Fritchey has written about PlanAffectingConvert.  in his article ‘The Seven Sins against TSQL Performance‘, it’s a query plan warning about there being an implicit convertion in the plan. 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. Let’s use the same sample query used by Grant in his article, we can notice the warning in the query plan: After running this query, the plan will be in the cache and it has the PlanAffectingConvert warning in it. So, let’s create a query using our new ‘planCacheFromDatabase‘ function to find PlanAffectingConvert in the plan cache: As you can see, all I had to do was use the previous created function, planCacheFromDatabase and filter the query plans to find which one has the PlanAffectingConvert warning.

Finding Warnings

PlanAffectingConvert 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. 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. One example is the ColumnWithNoStatistics warning. While ‘PlanAffectingConvert' appeared over the ‘Select‘ element in the plan, ‘ColumnWithNoStatistics' will appear over ‘scan‘ elements if we do a query over a column with no statistics. 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' is included in the query plan. It’s important to notice that SQL Server databases have the configuration ‘Auto Create Statistics‘ with the default as true. This means that we will never see this warning, because SQL Server will automatically create the statistics when needed. 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. For the Test or development environment, it is possible to disable ‘Auto Create Statistics’ and to look for ‘ColumnWithNoStatistics‘ in the cache to find where you would need to create statistics (or even indexes). To simulate this warning we need to temporarily disable ‘Auto Create Statistics’ in AdventureWorks database, it will be like this: After disabling the ‘auto create statistics’ we can generate this warning with the following query: Make sure that, once you’ve run your tests, you re-enable  ‘Auto Create Statistics’ . Now that we see the importance of warnings like ‘ColumnWithNoStatistics‘, ‘PlanAffectingQuery‘ and many others, let’s create a query to find which and how many warnings each query plan in the cache has: Let’s see the tricks I used in the above query:

• To combine in the same query the xmlnamespaces and one CTE (Common Table Expression) I used only one ‘with’ clause and a comma to separate the xmlnamespaces and the CTE
• The xquery expression doesn’t include the path of the ‘Warnings’ element because the ‘Warnings’ element can appear in different paths in the schema.
• 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‘ function retrieves the name of the element (the warning name) for the query.

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. The above query is a good candidate to become a function, so here it is: 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: … And once again, no XML in sight!

 Summary

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.