0

I was considering using incremental statistics in my data warehouse but I read an article by Erin Stellato that says the query optimizer doesn't use incremental statistics. The article was written in May of 2015 but I haven't seen anything recanting her position in the following 6 years. There are a number of articles in different communities that show how to set it up, but if it's not useful, why bother? Does anyone know if subsequent versions of the query optimizer in 2016, 2017, or 2019 support the use of incremental statistics? If not, should we even use them? If they won't help the engine make a good decision about how to query a table with 10 billion records what good is it? Thanx for any help!

1 Answer 1

5

Presumably your older partitions are not changing as frequently (or at all).

You bother because with incremental, when you update stats, you only update the current/active partition(s), which reduces the time you spend updating statistics (Erin talks about this in a follow-up article here). Yes, those get squished and folded into the larger histogram, and yes, I too had hoped the optimizer would be using them by now. But alas, it does not.

If you didn't use incremental, you're updating stats for the whole 10 billion row table (which takes more time) and what have you gained? Roughly the same histogram.

They're more useful for partition elimination (which I hope is useful for your 10 billion row table!) than for cardinality within, say, the latest partition.

If you want to take advantage of more histogram steps inside an individual partition, you could also maintain filtered statistics, maybe only on your most actively queried partitions. (These are useful for non-partitioned tables, too, where 200/201 steps is just not enough.)

From the CREATE STATISTICS documentation:

WHERE <filter_predicate> Specifies an expression for selecting a subset of rows to include when creating the statistics object. Statistics that are created with a filter predicate are called filtered statistics. The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

Here are some examples of filter predicates for the Production.BillOfMaterials table:

  • WHERE StartDate > '20000101' AND EndDate <= '20000630'
    
  • WHERE ComponentID IN (533, 324, 753)
    
  • WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL
    

Some info on filtered stats:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.