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.
There are some more improvements and I shall talk about them in my next posts.