Multi-Column Statistics

Raul Gonzalez looks at how the different cardinality estimators handle multi-column statistics:

The thing we can learn from this is that is impossible to be always right when you have to estimate the number of rows if your only resource is statistics, doesn’t matter single or multi-column, there is a set of values out there ready to defeat your logic.

However I think it’s a good idea that SQL Server 2016 gets back to look into multi-column for a simple reason, these are user created stats and therefore gives us (DBA’s, DEV’s) more power over how rows are estimated.

Multi-column stats are probably among the most under-utilized tools in SQL Server.

Related Posts

The Costs of Bad Statistics

Monica Rathbun explains what happens when statistics go wrong: Over Estimations of Rows (Actual > Estimated) leads to: – Selection of parallel plan when a serial plan might be more optimal– Inappropriate join strategy selections– Inefficient Index Navigation (scan verses seek)– Inflated Memory Grants Read the whole thing. The optimizer doesn’t get to look at […]

Read More

Estimates outside the Histogram Range

Josh Darnell shows us how SQL Server calculates estimates for input values outside of the range of your relevant statistic’s histogram: I have the impression that CSelCalcColumnInInterval “fails” if the predicate doesn’t fall within any of the histogram intervals. The estimation logic then chooses to try the CSelCalcAscendingKeyFiltercalculator (a reference to the “ascending key problem”) if the predicate […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930