More Denali Execution Plan Warning Goodies
Posted
by Dave Ballantyne
on SQL Blogcasts
See other posts from SQL Blogcasts
or by Dave Ballantyne
Published on Wed, 19 Oct 2011 17:39:00 GMT
Indexed on
2011/11/11
18:08 UTC
Read the original article
Hit count: 233
In my last blog, I showed how the execution plan in denali has been enhanced by 2 new warnings ,conversion affecting cardinality and conversion affecting seek, which are shown when a data type conversion has happened either implicitly or explicitly.
That is not all though, there is more . Also added are two warnings when performance has been affected due to memory issues.
Memory spills to tempdb are a costly operation and happen when SqlServer is under memory pressure and needs to free some up. For a long time you have been able to see these as warnings in a profiler trace as a sort or hash warning event, but now they are included right in the execution plan. Not only that but also you can see which operator caused the spill , not just which statement. Pretty damn handy.
Another cause of performance problems relating to memory are memory grant waits. Here is an informative write up on them, but simply speaking , SQLServer has to allocate a certain amount of memory for each statement. If it is unable to you get a “memory grant wait”. Once again there are other methods of analyzing these, but the plan now shows these too.
Don't worry that’s not real production code
There is one other new warning that is of interest to me, “Unmatched Indexes”. Once I find out the conditions under which that fires ill blog about it.
© SQL Blogcasts or respective owner