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 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.
SORT iterator warning
Operator used tempdb to spill data during execution with spill level 1
HASH MATCH (Aggregate) warning
Operator used tempdb to spill data during execution with spill level 1
The ever-popular Index Seek Iterator warning
Columns With No Statistics: [database].[schema].[table].Column
JOIN Iterator (appears pre-Denali too)
No join predicate
And 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:
Type conversion in expression (CONVERT_IMPLICIT(nchar(13),[database].[schema].[table].Column,0)) may affect "CardinalityEstimate" in query plan choice,
Type conversion in expression (CONVERT_IMPLICIT(nchar(13),[database].[schema].[table].Column,0)=[@1]) may affect "SeekPlan" in query plan choice
And indeed it did affect the plan. Drastically.
Also in the SELECT iterator:
SpatialGuess – True
I'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…
I'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.
@bobbeauch