SQL Server 2014 – Understanding Warning Signs on Select Operator

execution-planperformancequery-performancesql serversql server 2014stored-procedures

while comparing the execution plan of 2 stored procedures, on the second one I get a warning sign (marked by the red arrow).

what does it mean?

enter image description here

Best Answer

These are query planner warnings. In the actual execution plan (perhaps not the estimated one) I would expect to see a "warnings" section listing what the planner/engine is concerned about.

A common warnings are when an operation spills data to disk or was wanting index statistics that were unavailable. The most common warning is for apparently missing indexes, which is where SSMS gets the information it shows in green text for some statements.

(search for "showplan warning" and "query plan warnings" for a number of articles with more detail)

Other places to pick up on the plan warnings are via extended events (see here and other articles that pop up on searching for "query warning extended events") which allows you to monitor for problems in your running applications and in the output included when you use SET SHOWPLAN_XML ON or SET SHOWPLAN_ALL ON (which give you the information SSMS and other tools such as SQL Sentry's Plan Explorer analyse to show warnings).