Category: Statistics

Guy Glantser goes looking for stats:

A few weeks ago, I published a blog post titled “When Statistics Stay Empty Forever: A Hidden Edge Case with Empty Tables”

In that post, I described a scenario where statistics are created on an empty table, resulting in a statistics object without a histogram. If AUTO_UPDATE_STATISTICS is disabled and your maintenance solution doesn’t explicitly update statistics whose modification_counter is NULL, those statistics may never get a histogram – even after the table grows to millions or billions of rows.

Recently, I ran into another case involving missing histograms.

In this case, Guy found a table with 7 million rows and no histograms. Click through to see how that’s possible.

Comments closed

Brent Ozar digs into multi-column statistics:

The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don’t already exist), and uses those two statistics together – but they’re not really correlated.

For the longer answer, let’s take a large version of the Stack Overflow database, create a two-column index on the Users table, and then view the resulting statistics:

Click through for a dive into row estimation when you have multiple columns in a WHERE clause and how statistics come into play.

Comments closed

Radim Marek imports production statistics:

In the previous article we covered how the PostgreSQL planner reads pg_class and pg_statistic to estimate row counts, choose join strategies, and decide whether an index scan is worth it. The message was clear: when statistics are wrong, everything else goes with it.

But there was one thing we didn’t talk about. Statistics are specific to the database cluster that generated them. The primary way to populate them is `ANALYZE` which requires the actual data.

Click through to see how Postgres handles this. It’s quite similar to SQL Server’s DBCC CLONEDATABASE in practice, it seems.

Comments closed

Guy Glantser takes us through an edge case:

Many SQL Server DBAs rely on automated statistics maintenance solutions such as Ola Hallengren’s maintenance scripts. These scripts typically update statistics only when the modification counter exceeds a threshold.

But there is a corner case that can cause statistics to remain empty forever, and many DBAs are not aware of it.

Read on to see how you can end up with no statistics at all on a table.

Comments closed

Brent Ozar threads the needle:

Normally, when SQL Server updates statistics on an object, it invalidates the cached plans that rely on that statistic as well. That’s why you’ll see recompiles happen after stats updates: SQL Server knows the stats have changed, so it’s a good time to build new execution plans based on the changes in the data.

However, updates to system-created stats don’t necessarily cause plan recompiles.

Read on for the rare situation in which invalidation doesn’t happen.

Also, Brent has me wondering if the lemon popcorn is more sweet/tart (like a lemon bar) or savory (like lemon pepper) and I may have to try both styles.

Comments closed