Printed 2016/12/06 01:27PM

Extended Events - inaccurate_cardinality_estimate


Extended events have been a bit of a personal “Elephant in the room” for me.  I know they are there and I should really get on a start using them but never *quite* have a compelling enough reason. 

So now i really do,  after comparing the events in sys.dm_xe_objects between 2008r2 and 2012 I found one that really peaked my interest,  inaccurate_cardinality_estimate.  This is described as “Occurs when an operator outputs significantly more rows than estimated by the Query Optimizer. Use this event to identify queries that may be using sub-optimal plans due to cardinality estimate inaccuracy. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.

IMO cardinality estimation errors are the number one cause of performance problems.  If sqlserver deduces ( or even guesses) an incorrect row estimation then all bets are off,  if you get anything approaching a decent plan , its by luck not judgement.

So, lets see what if we can cause this event to fire.  Firing up management studio, we have the new extended events manager,


which sounds like a fun tool to play with Smile So starting a new session and going to the events library


and filtering by ‘Card’


Oh , nothing found.  Its simply not there , here is a connect item for this issue.

So , we will have to do this a more ‘manual’ way

CREATE EVENT SESSION inaccurate_cardinality_estimate ON SERVER
ADD EVENT sqlserver.inaccurate_cardinality_estimate
( ACTION (sqlserver.plan_handle, sqlserver.sql_text) )
ADD TARGET package0.asynchronous_file_target
( SET FILENAME = N'c:\temp\inaccurate_cardinality_estimate.xel',
metadatafile = N'c:\temp\inaccurate_cardinality_estimate.xem' );

Ok , session defined , lets start it.

ALTER EVENT SESSION inaccurate_cardinality_estimate ON SERVER STATE = START

To demonstrate the actual event we need to create and populate a temporary table :

drop table #newids
create table #NewIds
id char(36)
insert into #NewIds
select top(100)
cast(newid() as char(36))
from sys.all_columns a cross join sys.all_columns b
If we now execute
declare @v varchar(10)
Select @v='%XX%'
select COUNT(*) from #NewIds where id like @v

We will get an estimated row count of 5.37528.
I can control the exact actual row count by updating a number of rows to start ‘XX’ and thereby create a cardinality estimation error.
For starters lets update all the rows ( Im wrapping the updates in a transaction and rolling back , for sake of brevity this is not shown)
set id = 'XX'+left(id,20)

declare @v varchar(10)
Select @v='%XX%'
select COUNT(*) from #NewIds where id like @v
Then plan for the select shows a cardinality error, as expected

100 actual , 5.37528 expected.
Stop the extended events session
ALTER EVENT SESSION inaccurate_cardinality_estimate ON SERVER STATE = STOP

and all being well , in the c:\temp folder you will see an extended event log file.
Open that in management studio

There is the event, nice.  But hold on one cotton picking minute, look at the row counts.  Estimated = 5 , actual = 26 !?!

What happens if we repeat this operation but doubling the rows in the temp table

If we double the amount of rows in our temp table to 200,  our estimate rows in the plan will show as 10.7506 and actual as 200. In the extended event we see :


So the estimated count is shown as floor(row estimate) and the event is fired when the actual row count goes over 5*plan estimate, which is why actual is shown here as 53 not 200.  Notice that we also have the plan_handle and the node_id if we wish to tie this back to an exact operator in our system.

Quite why this is an extended event and not a plan warning , i really have no idea,  still its nice to know its there.

Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.