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:
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)
