\nSort and hash warnings can be found using SQL Server profiler, but they don't appear in query plans like "Columns With No Statstics" warnings in SQL Server 2008 R2 and below. In SQL Server Denali, they DO appear in query plans. No profiler needed. I'm not sure if all the pertainant events from the trace events "Errors and Warnings" (in Denali, I'd actually look at Extended Events Category Errors and Category Warnings), but I have seen some of them.\n<\/p>\n
\nSORT iterator warning
\n Operator used tempdb to spill data during execution with spill level 1\n<\/p>\n
\nHASH MATCH (Aggregate) warning
\n Operator used tempdb to spill data during execution with spill level 1\n<\/p>\n
\nThe ever-popular Index Seek Iterator warning
\n Columns With No Statistics: [database].[schema].[table].Column\n<\/p>\n
\nJOIN Iterator (appears pre-Denali too)
\n No join predicate\n<\/p>\n
\nAnd I've also seen some warnings in Denali query plans that don't appear in Extended Events Errors\/Warnings. These appear on the SELECT Iterator:\n<\/p>\n
\nType conversion in expression (CONVERT_IMPLICIT(nchar(13),[database].[schema].[table].Column,0)) may affect "CardinalityEstimate" in query plan choice,
\nType conversion in expression (CONVERT_IMPLICIT(nchar(13),[database].[schema].[table].Column,0)=[@1]) may affect "SeekPlan" in query plan choice\n<\/p>\n
\nAnd indeed it did affect the plan. Drastically.\n<\/p>\n
\nAlso in the SELECT iterator:
\n SpatialGuess – True\n<\/p>\n
\nI've been troubleshooting a spatial query plan, and this one has me baffled, so far. I assumed that it meant that the QP couldn't sniff the value of a spatial parameter, but I pass it in trying sproc parameter and using sp_executesql and still the warning persists. Hmmm…\n<\/p>\n
\nI'm not sure you can get 'em all, so if you happen to see some that I haven't yet seen (especially if it DOESN'T appear as a trace\/extended event error\/warning), just drop me a line.\n<\/p>\n
\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
Sort and hash warnings can be found using SQL Server profiler, but they don't appear in query plans like "Columns With No Statstics" warnings in SQL Server 2008 R2 and below. In SQL Server Denali, they DO appear in query plans. No profiler needed. I'm not sure if all the pertainant events from the trace […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,31],"tags":[],"class_list":["post-525","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server-2012"],"yoast_head":"\n