Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
Let's just get one thing out of the way first.
Yes, I understand that you, dear reader, probably hate maintenance plans. You associate them with everything dumb that you did early on in your career, like setting up a single plan that reorganized indexes, then rebuilt them, then updated their statistics. You are older and wiser now, and you swear by tools like Ola Hallengren's maintenance scripts or Minion Reindex.
This week, Brent, Erik, Jessica, Richie, and Tara discuss database modeling tools, how to learn about database corruption, the new cardinality estimator, and the only question that will be on our certification exams.
Here's the video on YouTube:
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.
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…
This post isn't going to solve any problems for you It was just something I stumbled on that struck me as funny, while working on a demo for something else. Brent didn't believe it at first, so I thought I'd share with the class. Blog. You, Your Name Here. Hiya! So there I was, creating…
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…
Confession time This started off with me reading a blurb in the release notes about SQL Server 2016 CTP 3.3. The blurb in question is about statistics. They're so cool! Do they get fragmented? NO! Stop trying to defragment them, you little monkey. Autostats improvements in CTP 3.3 Previously, statistics were automatically recalculated when the…
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...
I recently ran into a performance problem that had me scratching my head. How could a stored procedure's plan have changed, and yet it not be reflected in sys.dm_exec_procedure_stats?
So here's what happened and what I knew:
Here's a handy flowchart for whenever you find yourself worrying about fragmentation:
*Talk to your doctor about whether fragmentation is a problem for you. Seek help for index maintenance lasting longer than four hours. Serious side effects may occur if all index maintenance is ignored completely.
Scalar functions in computed columns cause all queries that hit that table to execute serially. But it gets worse!
Scalar functions in computed columns
cause index maintenance to go single-threaded.
If you're running Expensive Edition, index rebuilds can be both online and parallel. That's pretty cool, because it keeps all your gadgets and gizmos mostly available during the whole operation, and the parallel bit usually makes things faster.
Everybody wants to know about index fragmentation
It is an inescapable vortex of malaise and confusion. Like that swamp in The Neverending Story that killed the horse. Sorry if you haven't seen that movie. The horse wasn't that cool, anyway.
Neither is index fragmentation, but it's not worth losing sleep over. Or a horse.
I love stuff like this!
Even though it's not on my list of dream features, it's pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2012
I found a quirky thing recently While playing with filtered indexes, I noticed something odd. By 'playing with' I mean 'calling them horrible names' and 'admiring the way other platforms implemented them'. I sort of wrote about a similar topic in discussing indexing for windowing functions. It turns out that a recent annoyance could also…
Hey, remember 2005?
What a great year for... not SQL Server. Mirroring was still a Service Pack away, and there was an issue with spinlock contention on OPT_IDX_STATS or SPL_OPT_IDX_STATS. The KB for it is over here, and it's pretty explicit that the issue was fixed in 2008, and didn't carry over to any later versions. For people still on 2005, you had a Trace Flag: 2330.
BUT FRANCE HAS A FREE PONY That's right. PostgreSQL is basically free. The MIT license is like the Church of England of licenses. They do not care what you do. But I care what they do! If you read the first part of this article, you saw some Oracle features that I wish SQL Server…
An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let's test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…
Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it's "unsafe" to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn't seem like it should have to be there. The weather might be cloudy.
Our new Triage Specialist will be using tools, forms, and methodologies that we build for our SQL Critical Care® clients, but we also make a lot of those tools available to the public for free in our First Responder Kit.
Here's how to do your own quick health assessment for free:
It's tempting to think that table partitioning will improve query performance. After all, it's an Enterprise Edition feature-- it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…
