Do you ever feel SQL Server is hiding something from you? That’s what I thought when I learned about plan warnings. For me, the warnings seem to appear somewhat randomly within a graphical plan view as small yellow warning signs, but only if I happen to stumble upon them. If SQL Server already knows about these issues, and it does, why does it keep them a secret?
I’m happy to announce a free tool, SQL Plan Warnings from SolarWinds, that can eavesdrop on SQL Server and let you know about these secret warnings. It can quickly parse out plan warnings from cache and rank them by the ones that are the most problematic for your queries. No more secret warnings in your instance.
SolarWinds SQL Plan Warnings is easy to use. Simply download and run the executable (no installation required). To get started, use the “Connect to instance” button and add as many connections to SQL Server instances as you like—there are no limits. As you connect to each instance, the tool will save the connection details so it’s there for you the next time you use the tool. SQL Plan Warnings supports SQL Server running almost anywhere, so don’t hesitate to add any instances you have in your environment.
When you click on a saved connection in the left pane, the tool will automatically retrieve the list of plan warnings it discovers via plan parsing and ranks them by CPU time. In the filters pane, you can choose the types of warnings the tool displays. All warnings can potentially be important, but a couple of my favorites to review are missing indexes and key/RID lookups. Fixing these issues can often lead to significant performance gains. Also, if you have “no join predicate” warnings, this is often an error in the way the query is structured and is a big red flag, especially if it ranks near the top of the warnings list. For more information about all warning types, including high-level suggestions for fixing them, check out the SQL Plan Warnings Quick Reference Guide.
To view the details of a specific warning, hover over the warning and use the show warning detail icon. This will provide more information about the warning SQL Plan Warnings found, and where it appears in the plan. To view the graphical plan, use the Show plan link and use Show SQL to view the query associated with the warning.
To explore this problem further, note the plan step the warning is associated with (NodeID=1) and click on the plan. Selecting the step with the warning will display a properties panel to right with more information. A quick review of the query (not shown) shows me that the developer missed a join clause on one of the tables. Without this tool, we may not have known about this issue, or at least known about it as quickly.
What Did You Find?
Our development team uses SQL Plan Warnings to help make sure our code performs well. The story above was a real-life experience from our Database Performance Analyzer (DPA) team and it saved a huge amount of time by pointing them to a problem area in minutes. We found other examples with missing indexes and implicit data conversions (called Plan Affecting Conversion in the tool) as well. Customers that tried the tool in beta were amazed that the tool found so many things they didn’t know about.
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:
- Did this free tool help you find problems in your environments?
- What kinds of issues did you find that were previously unknown to you?
- Were there issues that it did not discover, or did you have any problems using the tool?
If you haven’t done so already, you can download the tool here: SQL Plan Warnings - Free Download | SolarWinds. If you’re already using the tool and need some help, check out the Quick Reference Guide or visit the SQL Plan Warnings free tool forum and create a topic to ask your questions.