SQL SERVER – Introduction of Showplan Warning

Vinod Kumar M is my very good friend renowned SQL Server Expert. Vinod Kumar has worked with SQL Server extensively since joining the industry over a decade ago. Before joining Microsoft, he was a Microsoft MVP for SQL Server for more than 3 years. He now works with MTC as a Technology Architect. He is a well-known speaker at all major Microsoft and third party technical conferences. Here is a very interesting blog post he sent on the subject of Executon Plan. Don’t forget to visit a Vinod’s blog and follow him on twitter.

SQL Server is a cost based optimizer and can guide us at every step. Now assume you wanted to learn about Performance tuning and you had a teacher to teach this. It would be much easier for us to learn and most importantly we will learn the concepts, behavior much more quickly. On the same lines, in this post we are using “SQL Server” as our teacher and guide.

Previous Warnings

In versions prior to SQL Server 2012 we already have had a few warnings in the plan that can help us diagnose the slow query issue. Primarily there are two types of warnings.  They are query level warnings and operator level warning. In this blog post we will discuss some of the enhancements available with SQL Server 2012.

A typical example of Query level warning includes missing index we are used to seeing inside Management Studio after executing a query.

Operator level warnings are raised at operator level.  When SSMS sees this type of warning, it puts a yellow exclamation mark on the operator icon.  The figure below shows this type of warnings.

Prior to 2012,   there are two warnings at operator level.  “No Join Predicates” and “Missing Column Statistics”.   If you enable SET STATISTICS XML ON we can get “Missing Column Statistics” warning as:

 <ColumnReference Database="[missingstats]" Schema="[dbo]"
 Table="[missing_stats]" Column="name" />

New SQL Server 2012 Warnings

When we troubleshoot / tune a single query because it is slow, we normally just get the query plan. However, there are several trace events that have been very helpful in determining why the queries are slow.  “Hash Warning” and “Sort Warnings” trace events are very helpful in determining why a query is slow.

In SQL 2012, the ShowPlan will produce warnings if hash join or sort spill to tempdb because of low memory conditions. In this case, we don’t have to capture trace events just to see if there are any sort warnings or hash warning on the query. To understand this warning in a typical setup, here is the script. First let us get the objects required for this demo.

USE tempdb
-- Preparing data
name VARCHAR(25), website CHAR(50))
-- Pumping in dummy Data
@counter INT
@counter = 1
WHILE @counter <= 100000
Plan_Warning(id, name, website)
VALUES (@counter, 'Vinod Kumar' + CONVERT(VARCHAR(10), @counter),
SET @counter = @counter + 1
'Load completed ...'

Now execute the next query to look at the execution Plan.

SELECT * FROM Plan_Warning
WHERE id >= 7000

This executes the query and shows us the execution plan as below. Click on the Showplan resultset. Alternatively we can also use the Ctrl+M to get the actual execution plan if required.

The execution plan reads like this. And we can see a small warning symbol with the Sort Operator. Also in the warning section we can see it says the spill did happen to tempdb. This is fundamentally because we have queried more than 93k rows and it was not able to fit in our laptop memory.

If we open the XML file we can find the below node. This is the same shown visually.

 <SpillToTempDb SpillLevel="1" />

This can happen for Hash Spills too. In this case we will see the warning on the Hash Join node with the same error of spill of data to tempdb. The SpillLevel for Hash Joins will be 2 in our XML.

The new warnings don’t get populated to legacy execution plans. In other words, if we set statistics profile on, we won’t get the plan with these details.

Final words

Though SQL Server shows the warnings, we have not explained what to do if you get warnings.

  • Spills are happening because we have selected more data in a single query than expected and this can be the case for reporting queries.
  • Try to add additional where clause to reduce the data from the big table.
  • When it is not possible to add additional where clause, we highly recommend to tune and monitor TempDB growth and contentions.

Hope you had fun learning something that SQL Server taught us. It is critical we keep exploring and learning looking for these fine prints with every single release of SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

3 thoughts on “SQL SERVER – Introduction of Showplan Warning

  1. In SQL Server 2008 R2 I had the “No Join Predicates” warning in a complex plan and it was very hard to notice with a lot of the same colours being used for other icons.

    There seemed to be no indication telling you there’s a problem.
    (You can search the xml version for “warnings”)

    Is this addressed in 2012?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s