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:
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.
Nah, not necessarily. SQL Server's query optimizer behavior keeps changing with every freakin' version. Let's illustrate it with a simple query against the Stack Overflow Users table:
[crayon-6ababbc273eb4966406379/]
It's hard to find good 400-500 level SQL Server training at any price, let alone free. Dave Ballantyne's past sessions from SQLBits are great examples of those.
Today, I'm going to review Dave's most recent session on Estimation and Statistics. It's from 2015, but it's still very relevant today.
I need to be up front with you, dear reader, and tell you that you're probably never going to need to know this. I try to blog about stuff people need to know to get their job done - things that will be genuinely useful in your day-to-day performance tuning and management of SQL Server.…
SQL Server uses statistics to guess how many rows will match what your query is looking for. When it guesses too low, your queries will perform poorly because they won't get enough memory or CPU resources. When it guesses too high, SQL Server will allocate too much memory and your Page Life Expectancy (PLE) will nosedive.
When I'm reviewing plans with folks, I get this response a lot:
But SQL Server should know there's no data that matches! It has statistics!
When you run a query, how does SQL Server estimate the number of rows? I'll explain in this half-hour video demo using playing cards, then showing the same issues in SQL Server Management Studio:
https://youtu.be/uqkf1VVZulg
In the last post, I talked about how we don't get accurate estimates because SQL Server's statistics only have up to 201 buckets in the histogram. It didn't matter much in that post, though, because we were using the small StackOverflow2010 database. But what happens as our data grows? Let's move to a newer Stack…
I'll start with the smallest Stack Overflow 2010 database and set up an index on Location: [crayon-6ababbc2777e2439445207/] There are about 300,000 Users - not a lot, but enough that it will start to give SQL Server some estimation problems: When you create an index, SQL Server automatically creates a statistic with the same name. A…
In my free How to Think Like the Engine class, I explain that SQL Server builds execution plans based on statistics. The contents of your tables inform the decisions it makes about which indexes to use, whether to do seeks or scans, how many CPU cores to allocate, how much memory to grant, and much more.
In our last episode, we saw how SQL Server estimates row count using statistics. Let's write two slightly different versions of our query - this time, only looking for a single day's worth of users - and see how its estimations go:
[crayon-6ababbc279241744169346/]
Both of those queries are theoretically identical in that they accomplish the same result by producing exactly the same rows - but their execution plans are different. On this one, you'll probably want to click to zoom in, and play spot-the-differences:
In our last episode, SQL Server was picking between index seeks and table scans, dancing along the tipping point to figure out which one would be more efficient for a query.
One of my favorite things about SQL Server is the sheer number of things it has to consider when building a query plan. It has to think about:
Men Without Hats - now those guys were cool:
https://www.youtube.com/watch?v=nM4okRvCg2g
Basics
If you update statistics when the underlying objects haven't been modified, plans won't be invalidated.
That makes total sense if your statistics update doesn't also change the statistics. But what about when they do?
Warm fuzzies
I think this is how my mom would have felt if I went to college.
You see, once you stop cargo culting around index fragmentation, and disk queue length, you start to realize which things are actually important to query performance.
During our pre-con in Seattle
A really sharp lady brought up using filtered statistics, and for a good reason. She has some big tables, and with just 200 histogram steps, you can miss out on a lot of information about data distribution when you have millions or billions of rows in a table. There's simply not enough room to describe it all accurately, even with a full scan of the stats.
It's Friday night, so I'm waiting for new CTP Releases
As soon as I got the email, I started reading the release notes. Some interesting stuff, of course.
Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more queries to run on systems that are otherwise blocking on memory.
Here at Brent Ozar Unlimited We have a proud tradition of not blaming index fragmentation for everything. There are points you should deal with it, but they're probably not 5% and 30% and 1000 pages. But that's not what this blog post is about. I'm hoping to clarify why we're more interested in up to…
If there's one thing living in Texas has taught me It's that people are very paranoid that you may Mess With it. Even in Austin, where the citizenry demand weirdness, they are vehemently opposed to any form of Messing, unless it results in mayonnaise-based dipping sauce. Me? I like Messing With stuff. Today we're going…
