I often get the question of “Will SQL Server do an index seek with ____?” Another way to phrase it is, “Is this query sargable?” We throw the term “sargable” around to mean that SQL Server can do an index seek to satisfy your search arguments. (We’re specifically talking about index seeks here, not scans – not…
Disclaimer I found this totally by accident, and it even surprised me. You can probably guess the TL;DR on this is yes, huh? Dude, where’s my table variable? I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret. Okay, so it’s just embarrassing. Anyway. I had…
In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views. Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really…
Love/Hate When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, it also has to keep your indexed view indexes in sync now, too. But SQL…
When you’re tuning queries It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor. But does adding missing…
Kid Tested When you work with something a lot, you tend to extrapolate concepts from it out to everything you see. That one person collecting tickets is a serial zone. That food truck line is a HEAP. The empty subway car that smells like a toilet is Replication. Stuff like that. If you have one…
It Is Known That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn’t have data for WHERE Bit = 0. That…
People often don’t give this thought Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it’s a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join…
Promises, Promises I said I’d follow up on this, and here it is. I’m by no means the world’s foremost authority on these, I just played with a simple example until I figured out what worked the best for my query. So you can all play along at home, I’m using XML we all have…
A really odd choice I’ve talked a bit about my annoyance with hardware limits for Standard Edition here and here. If you’re a bunch of SQL-savvy developers who understand the Enterprise Edition features that are now available in 2016 SP1, great. You’re a very small minority of shops. I’m talking about places like StackOverflow and…
I see a lot of indexes Most of them are pretty vanilla. Some are pretty memorable, like one that I saw with 93 INCLUDES. A lot of things that people could do with indexes get overlooked, either because they don’t know about them, or they’re not sure when they’ll help. So, here goes my attempt…
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.”…
This is not about table partitioning If you want to learn about that, there’s a whole great list of links here, and the Best Blogger Alive has a tremendous post on why table partitioning won’t make your queries any faster over here. With that out of the way, let’s talk about partitioned views, and then…
Let’s say, just theoretically, you wanted to demo a query that takes SQL Server a long time to compile. And in order to make SQL Server’s job tougher, you wanted to create the maximum number of indexes possible on a table. You might do something like this: SELECT TOP 999 ‘CREATE INDEX [IX_’ + c1.COLUMN_NAME…
Some of our clients have very high forwarded record counts and aren’t aware of it until they run sp_BlitzFirst and get an alert about high Forwarded Records per Second. Some of these clients are using Ola Hallengren‘s IndexOptimize stored procedure to maintain their indexes. This brought up a question of whether or not rebuilding a…
Be honest, here You don’t care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions… All you’re here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma’am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it….
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…
Even with lots of monitoring in place, we should perform periodic checks of our SQL Servers. Think of this like “Spring Cleaning”, except I would recommend that it be more frequently than just once a year. Doing it monthly might be a bit ambitious due to our busy schedules, but quarterly could be achievable. Below are…
This post is mostly a thought experiment The thought was something along the lines of: I have a table I want to keep temporal history of. I don’t plan on keeping a lot of data in that table, but the history table can accumulate quite a bit of data. I want to partition the history…
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…
