sqlskills-logo-2015-white.png

Troubleshooting the new Cardinality Estimator

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series:

This post, like the previous nine posts on this subject, uses SQL Server 2014 CTP2 as a reference point.  There may be changes by SQL Server 2014 RTM, and if so, I’ll write a post about applicable changes.

Now in terms of troubleshooting the new Cardinality Estimator, what I’m specifically referring to is the introduction of cardinality estimate skews that negatively impact query performance compared to the pre-SQL Server 2014 cardinality estimator functionality.   Ideally performance regressions should be rare, but when they happen, what are our troubleshooting options?

To frame this discussion, let’s first discuss what may or may not warrant action…

No Action (Necessarily) Needed

  • The estimates are identical to old CE functionality and the query plan is unchanged
  • The estimates are skewed compared to the old CE functionality, but the query plan “shape” is identical (and you see no side-effects from the skews, such as sort or hash spills and query runtime degradation)
  • The estimates are skewed compared to the old CE functionality, the plan is different, but performance is equal or improved – or even more stable
  • The estimates are skewed compared to the old CE functionality, the plan is different, and performance is somewhat impacted but not enough to justify changes (totally depends on your SLAs & workload performance requirements of course)

Action Potentially Needed

  • The estimates are skewed, the plan shape is unchanged, but the estimates lead to performance issues such as spills (due to under-estimates) or concurrency issues (due to over-estimates) for memory-intensive operators
  • The estimates are skewed, the plan is changed, and the plan quality leads to performance degradation (a variety of query optimizer plan choices which may lead to issues)

So what troubleshooting methods and options are available to us?

Troubleshooting Options

  • Do nothing (yeah, I know, but this can be a decision you ultimately make, looking at risk/effort/reward)
  • Revert to the pre-SQL Server 2014 CE version (for example, via database compatibility level change)
  • Apply legacy troubleshooting methods, which may fix other issues directly or indirectly related to the skew and thus help close the gap (framing these legacy methods as questions below)
    • Are the statistics old and need updating?
    • Should the statistics sampling be changed?
    • Are multi-column stats needed to help establish a correlation where one currently isn’t seen by the query optimizer?
    • Parameter sniffing troubleshooting needed? (a much larger topic, but indulge me on including this as a method)
    • Is your table variable usage contributing to the skew?
    • Is your multi-statement table-valued function or scalar user-defined function contributing to the skew?
    • Any data-type conversions occurring for predicates (join or filter)?
    • Are you comparing column values from the same table?
    • Is your column reference being buried by a function or embedded in a complex expression?
    • Are hints being used and if so, is their usage appropriate?

The new CE story will unfold as customers start upgrading to SQL Server 2014 and I’ll be curious to see which regression patterns are most common.

Open Questions

  • Regarding the new query_optimizer_estimate_cardinality XE event… Will it be a practical source of information for most SQL Server users in cardinality estimator skew regression scenarios – or will it be something reserved for edge-cases and advanced Microsoft customer support scenarios?  I suspect this XE event will have limited applicability, but I’m reserving judgment for now.
  • Will SQL Server 2014 RTM introduce finer-grained methods for reverting to the pre-2014 cardinality estimator?
  • How will the new CE behave with newer functionality?  For example, Hekaton and clustered columnstore indexes.
  • Will this be it for CE changes for the next few versions?  There is plenty left on the CE-improvement wish list, so I hope not.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course