Joe Obbish Decoding the Cardinality Estimator to Speed

Joe Obbish Decoding the Cardinality Estimator to Speed Up Queries

About Me • Business Intelligence Developer for EHR company • Answer questions on dba. stackexchange. com • Blog about SQL Server at https: //orderbyselectnull. com/ • Done performance tuning for thousands of queries

This Slide Intentionally Left Blank

Why are Some Queries Always Slow? • Data model or indexing problems • Unreasonable questions – “It’s slow if I cross join 2 tables with a million rows each” • Cardinality estimate issues

What is a Cardinality Estimate? The estimated number of rows output by an operator in a query plan.

Why is This Important? • Query plans are chosen on cost • Operator costs are mostly determined by estimates • Key estimates need to be close enough • Good performance with bad estimates often comes down to luck or processing power

Poor Estimates Often Lead to Poor Plans • • • Wrong table access type Wrong join order Spills to tempdb And much more!

The Root Cause of Poor Estimates • I try to find the root cause of cardinality estimate issues when I can. • Goal isn’t to understand or memorize every detail, but to have a strong foundation to tackle future problems. • The community can help you.

Basic Principles • • • Use available statistics objects Assume data is uniform Always produce an estimate Never estimate 0 rows Don’t require consistency

Statistics Use statistics instead of looking at table data

Data is Uniform • There’s no data skew • Histogram is the truth, the whole truth, and nothing but the truth • Somewhat relaxed with new CE or with some trace flags

Always Produce an Estimate • CE always produces an estimate, even if it has to guess • Common guesses: • • • 30% - unknown inequality 9% or 16. 4% for BETWEEN (legacy and new CE) 10% for some other cases

Minimum Estimate • You’ll never see a cardinality estimate of 0 rows • Optimizer assumes that you’re always looking for data that exists • Costing plans with 0 row estimates doesn’t really make sense

Inconsistency • Cardinality estimates do not need to be consistent between plans • Filters with > and <= can both give 30%, even though they must add up to 100% (ignoring NULLs)

Academic Demos • All testing done on the new CE in SQL Server 2017 • SQL Server 2008 R 2 and 2012 only have the legacy CE

Our Statistics

Example 1 SELECT * FROM T WHERE ID = 1;

Example 1 Solution SELECT * FROM T WHERE ID = 1;

Example 2 SELECT * FROM T WHERE ID = 2;

Example 2 Solution SELECT * FROM T WHERE ID = 2;

Example 3 SELECT * FROM T WHERE ID = 7;

Example 3 Solution SELECT * FROM T WHERE ID = 7;

Example 4 DECLARE @local_variable BIGINT = 1; SELECT * FROM T WHERE ID = @local_variable;

Example 4 Solution DECLARE @local_variable BIGINT = 1; SELECT * FROM T WHERE ID = @local_variable;

Example 5 SELECT * FROM T WHERE ID > 1;

Example 5 Solution SELECT * FROM T WHERE ID > 1;

Example 6 DECLARE @local_variable BIGINT = 1; SELECT * FROM T WHERE ID >= @local_variable;

Example 6 Solution DECLARE @local_variable BIGINT = 1; SELECT * FROM T WHERE ID >= @local_variable;

Example 7 DECLARE @local_variable BIGINT = 1; SELECT * FROM T WHERE ID < @local_variable;

Example 7 Solution DECLARE @local_variable BIGINT = 1; SELECT * FROM T WHERE ID < @local_variable;

Example 8 Solution SELECT ID FROM T GROUP BY ID;

Example 9 SELECT * FROM T WHERE ABS(ID) = -1;

Example 9 Solution SELECT * FROM T WHERE ABS(ID) = -1;

Filter Independence • How should filters on different columns be combined? • Is there some level of correlation between the columns or are they independent?

“Real World” Demos

Predicting Problems • • Window functions Column correlation Other functions Local variables with no RECOMPILE hint Filtering on aggregates Undeclared relationships in data Data skew

Check For CE Problems • Easiest way is to look at actual plans and use what you know about the data • Is that estimate of 1 row reasonable?

Tricky Plans Nested loop joins

Bitmap filters

Columnstore aggregate pushdown

Review • Try to figure out the root cause of a cardinality estimate issue that causes performance problems • • Recognize patterns Know if the problem might come back

Resources • • • BOL Cardinality Estimation White Paper by Joe Sack on New CE CE Questions on Stack Exchange Blog post on containment Blog post by Paul White on DISTINCT estimates Blog post by Dmitry Pilugin on Join Estimation Internals

Please Fill Out Feedback Forms