Finding & Fixing Statistics Without Histograms
Men Without Hats - now those guys were cool:
https://www.youtube.com/watch?v=nM4okRvCg2g
Statistics Management
Creating, updating, inspecting, and understanding optimizer statistics.
40 associated posts37 primary posts
Performance Tuning
It’s Okay If You Don’t Create Statistics.
Along with the ability to create indexes (which you most definitely should be doing), SQL Server gives you the ability to create statistics. This helps SQL Server guess how many rows will come back for your searches, which can help it make better decisions on seeks vs scans, which tables to process first, and how much memory a query will need.
Performance Tuning
What Kind Of Statistics Updates Invalidate Plans?
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?
[Video] Office Hours 2018/6/6 (With Transcriptions)
This week, Brent, Erik, Tara, and Richie discuss index maintenance, stats updates, the acquisition of GitHub, using linked servers to move tables across, talking to developers as a DBA, the best cloud option, setting the number of tempdb files, and their favorite SQL Server features.
Here’s the video on YouTube:
Good Reasons to Rebuild or Reorganize Indexes
No, not that
It's not the F word.
Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when it fails.
Performance Tuning
SQL Server 2017: Statistics Information Comes To Query Plans
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.
[Video] Office Hours 2017/04/12 (With Transcriptions)
DISCLAIMER: this week, we learn that a naked Erik Darling looks like a bear covered in squid ink. Thankfully, we don't learn that via the webcams. Aside from that, Brent, Richie, and Erik also discuss updating stats and rebuilding indexes, feature request for Paste the Plan, included columns' effect on DML, CLR store procedures, SQL command and…
[Video] Office Hours 2017/03/22 (With Transcriptions)
This week, Tara, Erik, and Richie discuss statistics updates, in-place upgrades, row vs page compression, deadlocks, replacing SQL backup vendors, server load, ETL, and whether they think that database mail should be used by user applications to send their emails.
Here’s the video on YouTube:
Performance Tuning
Filtered Statistics Follow-up
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.
Hidden in SQL Server 2017 CTP v1.1: sys.dm_db_stats_histogram
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.
Performance Tuning
Can Indexes My Query Doesn’t Use Help My Query?
This came up during Office Hours
And I love questions like this, because they reveal some interesting underpinnings of how the cardinality estimator works. It was something along the lines of "I have a slow query, and when I add an index the query goes faster even though it doesn't use the index I created."
Performance Tuning
Why Not Just Create Statistics?
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…
Stats Week: Only Updating Statistics With Ola Hallengren’s Scripts
I hate rebuilding indexes
There. I said it. It's not fun. I don't care all that much for reorgs, either. They're less intrusive, but man, that LOB compaction stuff can really be time consuming. What I do like is updating statistics. Doing that can be the kick in the bad plan pants that you need to get things running smoothly again.
Stats Week: Messin’ With Statistics
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…
Why most of you should leave Auto-Update Statistics on
Oh God, he's talking about statistics again Yeah, but this should be less annoying than the other times. And much shorter. You see, I hear grousing. Updating statistics was bringin' us down, man. Harshing our mellow. The statistics would just update, man, and it would take like... Forever, man. Man. But no one would actually…
Changes to auto update stats thresholds in SQL Server 2016
TL;DR
As of CTP 3.3, it's the same behavior as Trace Flag 2371, in 2008 R2 SP1 and onward. That basically means that the bigger your table is, the fewer rows need to be modified before an automatic statistics update occurs.
Slightly longer...
The change was announced over here. At first I thought, woah, cool, they thought about this and made big changes. But no, much like Trace Flags 1117 and 1118 being enabled for tempdb, it's just...
Performance Tuning
Are Index ‘Included’ Columns in Your Multi-Column Statistics?
When you create an index in SQL Server with multiple columns, behind the scenes it creates a related multi-column statistic for the index. This statistic gives SQL Server some information about the relationship between the columns that it can use for row estimates when running queries.
But what if you use 'included' columns in the index? Do they get information recorded in the statistics?
Does Updating Statistics Cause a Recompile if No Data Has Changed?
tl;dr -- Not necessarily.
One of our students asked me a great question: if you update statistics on every table in the database, is that effectively the same as dumping the procedure cache on the instance? Will every execution plan have to be recompiled the next time it is run? I thought it was a great question and it spurred an interesting discussion about maintenance and recompilation.
Performance Tuning
Statistics Matter on Temp Tables, Too
Temp tables are like real tables, just a little tricker.
When you're starting out writing TSQL, it's easy to want to do all your work in a single query. You learn about derived sub-queries, CROSS APPLY statements, and common table expressions. Suddenly, each of your queries is so complex that you hardly know what you're doing.
UPDATE STATISTICS: the Secret IO Explosion
I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps:
Rebuild all the indexes in the database - this took 10 minutes each night.
Update statistics - this took 2-3 hours each night.
