SQL Server 2012 EXECUTION PLAN warnings – Part 1

Hi Friends,

Continuing with my DENALI series, today I would like to talk about the new SQL Server 2012 EXECUTION PLAN warnings. There are many new warnings that have introduced in execution plans, primarily, to help DBAs & developers alike to identify issues in their T-SQL code.

In this post, I want to talk about implicit conversion warnings that may lead to incorrect cardinality estimation:

Let us create a temporary table in tempDB which will have some records from the customers table in Northwind2 database.

USE tempdb
GO
 
-- create a new table in tempdb
 
SELECT * INTO tempCusotmers
from Northwind2.dbo.Customers
where PostalCode = '51100'

Note in the above script; PostalCode column in the customers table is of type varchar and I am only inserting a single record in the new table.

Now select from the new table. Make sure you enable “Include actual execution plan” from the toolbar or press Ctrl + M.

-- when you execute the following code, there are no warnings becuase, there is a single row in the table.
select * from tempCusotmers
where PostalCode = 51100

In the above code, observe that we are doing an implicit conversion of PostalCode from varchar type to int type. Now if you observe the execution plan, you will see no warnings. Primarily because there is a single record so the optimizer does not bother about any cardinality estimation errors.

Let us try again and insert a few records.

-- lets try again with more rows..
DROP TABLE tempCusotmers
 
-- inset more records this time..
 
SELECT * INTO tempCusotmers
from Northwind2.dbo.Customers
where PostalCode IN ('12209', '05021', '05023', '68306', '67000', '28023', '13008', '05022')

After the table is created again with some records, try selecting again. (Include Actual Execution Plan should be ON)

-- now select again with implict conversion
 
select * from tempCusotmers
where PostalCode = 12209

This time you shall find a warning in the SELECT operator of the execution plan, which says that implicit conversion may affect cardinality estimation.

1_SQL_Server2012_DENALI_EXECUTION_PLAN_warnings_Part1

There are some more improvements and I shall talk about them in my next posts.

 

 

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published. Required fields are marked *