Warnings in SQL Server Query Execution Plans

Have you seen Warnings on operators in Query Execution Plans?

Warnings indicate that Query Optimizer has found something that might affect the performance of the query and you need to work on it. There are various reasons for warnings but typically warning is displayed when;

• values used with WHERE or JOIN clauses are not same data type. This might prevent statistics from being used correctly and might display 
• database is with AUTO CREATE STATISTICS OFF and column used with the predicate has no statistics.
• no ON clause (or equivalent) when two or more tables are referenced in the query.

Here are some code samples on them.

The first code segment shows how warning is shown when data types are not match with JOIN.

view plainprint?

1. USE AdventureWorks2014;  
2. GO  
3.   
4. -- create new header table  
5. SELECT *  
6. INTO OrderHeader  
7. FROM Sales.SalesOrderHeader;  
8.   
9. -- create new details table  
10. SELECT *  
11. INTO OrderDetails  
12. FROM Sales.SalesOrderDetail;  
13.   
14. -- change the data type of SalesOrderID in OrdeDetails table  
15. ALTER TABLE OrderDetails  
16. ALTER COLUMN SalesOrderID varchar(10);  
17.   
18. -- Enable Execution Plan  
19. -- Execute the query and see  
20. SELECT *  
21. FROM OrderHeader h  
22.  INNER JOIN OrderDetails d  
23.   ON h.SalesOrderID = d.SalesOrderID  

USE AdventureWorks2014;
GO

-- create new header table
SELECT *
INTO OrderHeader
FROM Sales.SalesOrderHeader;

-- create new details table
SELECT *
INTO OrderDetails
FROM Sales.SalesOrderDetail;

-- change the data type of SalesOrderID in OrdeDetails table
ALTER TABLE OrderDetails
ALTER COLUMN SalesOrderID varchar(10);

-- Enable Execution Plan
-- Execute the query and see
SELECT *
FROM OrderHeader h
 INNER JOIN OrderDetails d
  ON h.SalesOrderID = d.SalesOrderID

The second code shows how warning is shown when no statistics available.

view plainprint?

1. -- create a new database  
2. CREATE DATABASE TestDatabase  
3. GO  
4.   
5. -- turn off auto create statistics  
6. ALTER DATABASE TestDatabase SET AUTO_CREATE_STATISTICS OFF;  
7.   
8. -- connect with the database  
9. USE TestDatabase;  
10. GO  
11.   
12. -- create a new table  
13. SELECT *  
14. INTO OrderHeader  
15. FROM AdventureWorks2014.Sales.SalesOrderHeader;  
16.   
17. -- create a clustered index on newly created table  
18. CREATE CLUSTERED INDEX IX_Sales ON dbo.OrderHeader (SalesOrderID);  
19.   
20. -- Execute the query and see  
21. -- Since no statistics on OrderDate column  
22. -- and it does not create  
23. -- you should see a warning  
24. SELECT *  
25. FROM dbo.OrderHeader  
26. WHERE OrderDate = '2014-01-01';  

-- create a new database
CREATE DATABASE TestDatabase
GO

-- turn off auto create statistics
ALTER DATABASE TestDatabase SET AUTO_CREATE_STATISTICS OFF;

-- connect with the database
USE TestDatabase;
GO

-- create a new table
SELECT *
INTO OrderHeader
FROM AdventureWorks2014.Sales.SalesOrderHeader;

-- create a clustered index on newly created table
CREATE CLUSTERED INDEX IX_Sales ON dbo.OrderHeader (SalesOrderID);

-- Execute the query and see
-- Since no statistics on OrderDate column
-- and it does not create
-- you should see a warning
SELECT *
FROM dbo.OrderHeader
WHERE OrderDate = '2014-01-01';

The third shows how warning is shown when no ON clause for tables referenced.

view plainprint?

1. USE AdventureWorks2014;  
2. GO  
3. -- Execute the query and see  
4. -- Since no ON clause, warning is displayed  
5. SELECT *  
6. FROM Production.Product, Production.ProductSubcategory;  

USE AdventureWorks2014;
GO
-- Execute the query and see
-- Since no ON clause, warning is displayed
SELECT *
FROM Production.Product, Production.ProductSubcategory;