SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

October 2011 - Posts

More Denali Execution Plan Warning Goodies

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 Smile.  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.

image

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.

image

Don't worry that’s not real production code Smile

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.

Implicit Conversions warning

After adding an index,  ensuring that an index is actually used is probably the biggest win you will get in terms of performance in SqlServer.  There are many ways that an query can be non-sargable, and therefore not using an available index,  a common one is implicit (or even explicit) type conversion.

Within Denali ( or now SqlServer 2012 to give it its correct name) two new warnings have been added to the execution plan to help highlight this issue.  “Type conversion in expression ColumnExpression may affect "CardinalityEstimate" in query plan choice” and “Type conversion in expression ColumnExpression may affect "SeekPlan" in query plan choice” are now shown if a type conversion has happened and it is affecting , or could be , affecting performance.

To demonstrate both I will need to populate a small table

drop table nums
go
create table nums
(
IntCol Char(10)
)
go

insert into nums(IntCol)
Select top(10) row_number() over(order by (select null)) from sys.columns


Notice that the IntCol column is defined as a char(10) type.  If i now execute the following query
 
select * from nums where IntCol = 8
 
In the execution plan viewer, I will see this
image
 
There is a Yellow Exclamation Mark warning on the query it self and the warning is that the type conversion may affect CardinalityEstimate.  Its pretty obvious now what our potential problem is.
 
Additionally, if an index is created:
 
create index Idx1 on nums(IntCol)

and the Select statement re-executed,  this is shown..
 
 image
Even better , In layman's terms “You have an index, but i cant use it”.