Thursday, October 27, 2011

SQL Server Denali: New warnings in Actual Execution Plan

I’ve played with SQL Server Denali and found some new features and improvements in Query Execution plan. My intention was to test Extended Events and new events: sort_warning and hash_warnig. In SQL Server 2008R2, we can create SQL Trace to capture sort and hash warnings but there is no easy way to find out which statement cause it. I hoped that with new XEvent in Denali we can do that easily.
I reduced maximum server memory for SQL Server, ran some queries with ORDER BY and here is the Actual Execution Plan for one query.

Do you see something new? There is a warning on Sort operator (1) and on SELECT (2).
If we look at the Sort operator and SELECT we can see more information about warnings.









In Denali we see Sort warnings in query execution plan. It seems that some waits are also visible. Is the MemoryGrant only wait type that will appears in query plan?
I don’t know what kinds of warnings are included in Denali actual execution query plan but I think that these two warnings are not the only one.

No comments:

Post a Comment