SQL SERVER – XEvents to Find Cardinality Estimation Issues

Ever since SQL Server 2014 brought the new Cardinality estimates, there have been a number of blogs to talk about them. Even here, I wrote a quite a few blogs about this very subject.

SQL SERVER – How to Force New Cardinality Estimation or Old Cardinality Estimation

SQL SERVER – Simple Demo of New Cardinality Estimation Features of SQL Server 2014

The world of cardinality estimates is complex and sometime needs quite some time to understand. In one of the recent chat around this very topic, I was asked if there are ways to find if there were problems in the cardinality estimation. And even if we had one, is there a way to find out?

This got me thinking and the outcome is this blog post. I figured out the Xevent, Query_optimizer_estimate_cardinality. This XEvent is primarily used to help troubleshoot cardinality estimate issues and how cardinality of each operator is derived. In the past, it was very hard to find out how the code estimates cardinality. This event class now lets us see precisely which class in the code produces the estimate in addition to useful information such as which statistics is used to derive the cardinality estimate without requiring code access.

As you configure Extended Events, in addition to standard global actions, this event has its own fields as shown below:

SQL SERVER - XEvents to Find Cardinality Estimation Issues optimizer-estimate-cardinality-01

Calculator:  The strategy used for the estimate.

Create_time: Time at which the query was compiled

Input_relation: The input relation on which the cardinality is being estimated. This is the operator on which the estimate is used.

Stats_collection: The stats collection generated for the input.

Stats_collection_id: ID of the stats collection generated for the input

If you enabled the XEvent, there will be StatsCollectionID property/attribute added to the properties pane. When you get estimated or execution plan, you need to hover over a particular operator and choose “Properties”.  Then there will be “Properties” window show up. Right click a step in execution plan and click properties to find them.

If you ever get a chance to play around with this, let me know what interesting output have you got? What did you troubleshoot as part of this XEvent? Let me know via the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Extended Events
Previous Post
SQL Server – T-SQL Enhancement “Drop if Exists” clause
Next Post
SQL SERVER – Could Not Register Service Principal Name Persists – Notes from the Field #105

Related Posts

Leave a Reply