SQL Plan Warnings Quick Reference Guide

SolarWindsRegistered SQL Plan Warnings is a free tool to help discover the queries and plans that include embedded warnings from the SQL Server optimizer. Plan warnings are SQL Server’s way of letting you know about potential performance-degrading activities happening during query execution. The SQL Plan Warnings free tool displays the warnings based on the impact to your database server, which allows you to quickly focus on the plan warnings with the most impact. The tool supports all flavors of SQL Server, whether it runs on-premises or in the Azure or Amazon cloud platforms.

Key Features

  • Simple to download and install
  • Shows list of SQL Server cached plans containing performance-affecting warnings
  • Bubbles the most impactful warnings to the top
  • Filters plans by database and warning types
  • Supports SQL Server 2008 R2 and higher
  • Works with SQL Server running on Windows or Linux
  • Works with Azure SQL Database (managed instance, elastic pool, and single database) and Amazon RDS for SQL Server platforms
  • Runs on most Windows and Mac platforms

Download and Install

  1. Download the free tool here: SQL Plan Warnings download
  2. Unzip the tool and run the SQL-Plan-Warnings executable to install and run the application
  3. Accept the terms of the license agreement
  4. The following icon will be added to your environment for subsequent executions:

pastedImage_0.png

Overview

A welcome screen is displayed when the SQL Plan Warnings tool executes for the first time. From here, you can connect to SQL Server instances or import an execution plan from the filesystem. Subsequent executions of the tool will display the instance overview page with plan warnings for the selected instance:

pastedImage_1.png

The following is a description for each numbered item above:

  1. List of saved connections. Clicking on an instance will show the plan warning details for the instance
  2. Connect to a new instance or import an execution plan from the filesystem
  3. Shows the instance connection name along with instance details, such as SQL Server version, instance name, and username
  4. Edit or delete a SQL Server connection
  5. Filter the plan warnings by warning type
  6. Re-run the query to refresh the plan warnings
  7. List of plan warnings found in the instance
  8. Show details about a specific plan warning
  9. Show the plan and/or download the XML plan to the filesystem
  10. Search the text within the plan warnings. This can be used to filter specific words within the SQL text or filter by a database
  11. Modify tool settings and get help

Plan Warning Types

The SQL Plan Warnings free tool can find six types of plan warning issues:

  1. Key/RID Lookup – occurs when an index is used to retrieve data, but the query references columns not included within the index. The rest of the required data is retrieved from the clustered index (Key Lookup) or table (RID Lookup). The lookups are performed for each row retrieved from the index and can cause significant performance impacts with larger data sets. Solutions for lookup warnings typically involve:
    1. Altering an existing non-clustered index and adding the query’s columns as part of the key or as INCLUDE columns.
    2. Removing unnecessary columns from the query not contained in the index.
    3. Adding a clustered index. The RID Lookup warning is an indication that a clustered index doesn’t exist for the table.
  2. Missing Index –SQL Server’s way of telling you a new index may help the query’s performance. Along with the warning, SQL Plan Warnings will show the expected impact of the index as well as the DDL for creating it.
  3. No Join Predicates – the query does not have proper join predicates, typically resulting in a Cartesian product. Many would term this an “error” rather than a “warning” since it is usually caused by a flaw in the design of the query. This issue can cause performance issues, and the query is likely returning incorrect results. Solutions typically include adjusting the query to ensure all tables are properly joined.
  4. Plan Affecting Conversion – there are two types of warnings that are considered within this type:
    1. Seek Plan – this warning typically points to an implicit datatype conversion on one of the columns in the WHERE clause of the query. This will often cause the SQL Server optimizer to perform a full index or table scan. Rewrite the query so data types match within the WHERE clause.
    2. Cardinality Estimate – this type of warning is typically not as severe as the Seek Plan warning and often points to conversions in the field list of a query, i.e., not within the WHERE clause.
  5. Missing Column Statistics – indicates column statistics that could have been helpful to the SQL Server optimizer are not available. This can cause the optimizer to make bad guesses about the data and choose an inefficient query plan.
  6. Spill to TempDB – the most common plan operators that cause this type of warning are sorts and joins (hash, merge, etc.). It indicates the plan was not granted enough memory to complete one or more operations and the data spilled to TempDB. Reading from disk is usually much slower than memory, which can dramatically affect the query’s performance. Review object statistics to determine if they’re out of date and causing the optimizer to make bad memory estimates.

SQL Server Required Permissions

The SQL Plan Warnings free tool retrieves the warnings by executing queries against several DMVs. When adding a connection to a new SQL Server instance, the user should have read permissions against these objects. A simple way to achieve this is to grant the user VIEW SERVER STATE role or SELECT privileges against these views:

  • sys.dm_exec_query_stats
  • sys.dm_exec_text_query_plans
  • sys.dm_exec_sql_text

Configuration

By default, the top 100 plans with embedded warnings are displayed and ordered by CPU time. This and other configuration values can be modified via the Settings page:

pastedImage_2.png

Filter Settings

  • Maximum number of plans analyzed – modifies the number of plans displayed in the tool
  • Filter plans by – modifies the filter used to retrieve the Top X plans to be based on CPU time, elapsed time, or executions. Based on this setting, the item immediately below it will be shown accordingly:
    • Minimum CPU time – minimum seconds of CPU time for the queries since instance startup
    • Minimum elapsed time – minimum seconds of elapsed time for the queries
    • Minimum executions – minimum executions for the queries
  • Exclude databases – a comma separated list of databases to be ignored

Import Plan Settings

  • Maximum import plan size – maximum size for an imported plan

Reset Settings

  • Reset settings – resets the values to the system defaults

Getting Support

Support for this tool is handled via the SQL Plan Warnings product forum on THWACK. Reply to the main post to ask your question and someone will get back to you.

Limitations and Restrictions

  • Large SQL Server Plan Cache – if your SQL Server instance has a large amount of memory assigned to the plan cache, the higher number of plans the tool will be required to parse for warnings. Care should be taken when executing this tool on SQL Server instances with greater than a 1GB plan cache
  • Azure Active Directory is not supported for connections to Azure SQL Databases
  • AWS Directory Service is not supported for connections to Amazon RDS for SQL Server instance
  • Windows authentication is not supported for connecting to SQL Server instances on Linux

What Did You Find?

Our product teams at SolarWinds have used SQL Plan Warnings free tool to uncover several problematic queries. On one occasion, the Database Performance Analyzer (DPA) team found a query performing an implicit data conversion and causing web page timeouts. Once this issue was uncovered by the tool, the query was analyzed within SolarWinds Database Performance Analyzer (DPA) to get additional tuning assistance. If you find interesting stories in your environment, let us know by commenting on this post.

We would love to hear feedback about the following:

  • Does this improve your workflow for finding plan warning issues?
  • How much time does it save you?
  • Are there issues in your databases that you uncovered using this free tool?
  • Have you downloaded DPA to get an even more in-depth analysis of your SQL Server environment?