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.
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.
-- 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.
USE AdventureWorks2014; GO -- Execute the query and see -- Since no ON clause, warning is displayed SELECT * FROM Production.Product, Production.ProductSubcategory;