A Fine Slice Of SQL Server
Andy Brownsword removes a blocker:
Blocking in SQL Server will reduce throughput. Excessive blocking can be cause bottlenecks on our environments so helping to mitigate it. Here we’re going back to basics to look at how it happens and how having effective indexes can reduce it.
The word “effective” is the critical one there.
Comments closedI found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an “online” index rebuild of a disk-based rowstore clustered index (basically a normal, everyday table) isn’t actually “online”. In fact, it’s very OFFLINE, and it blocks both read and write queries against the table for long periods.
If you manage to make it through a rebuild successfully, the problem goes away for future rebuilds of that clustered index – likely leaving you bruised and bewildered.
Click through for the details, including repo scripts and explanation of what should happen in this case.
Comments closedBefore I go on about how adding an index can fix your blocking and deadlocking problems, let me warn you about how indexes can cause you problems.
Without fail, whenever I suggest adding indexes to clients, someone gets real worried about write speeds.
Like, the server can be burning down, spurting blood everywhere, and cursing our mothers in ancient Aramaic, but no, let’s stop and worry about write speeds going up by a few milliseconds.
Fully agreed: the marginal cost of an index is rarely noticeable and the marginal benefit of a well-designed index serving an existing customer base is significantly higher than its marginal cost. However, when I click that plus sign next to the Indexes folder and SSMS starts spinning because it needs to enumerate the dozens and dozens of indexes on that table, it’s a bad sign. Especially when 90% of them have “_dta_” in the name.
Click through for more thoughts on indexing, scans, and transactions.
Comments closedErik Darling doesn’t trust the system:
SQL Server’s missing index requests (and, by extension, automatic index management) are about 70/30 when it comes to being useful, and useful is the low number.
The number of times I’ve seen missing indexes implemented to little or no effect, or worse, disastrous effect… is about 70% of all the missing index requests I’ve seen implemented.
This has been pretty close to my experience as well. Click through for a much better approach to the task.
Comments closedErik Darling sends it to the moon:
Probably the most fascinating thing about Eager Index Spools to me is how often the optimizer will insert them into execution plans, often to a query’s detriment.
In a sane world, a non-loop join plan would be chosen, a missing index request would be registered that matches whatever would have been spooled into an index, and we’d all have an easier time.
Read on for a few examples of the problem and two separate ways you can fix it. Remember, kids: friends don’t let friends eagerly spool.
Comments closedErik Darling makes the donuts:
Okay, deep breath. Deep, deep breath.
Would you believe I still have to fix queries with a bunch of functions in joins and where clauses? Like every day? All day?
Where things get tough is when there’s some third party vendor app where code can’t be changed, but customers have become so fed up with performance that they’re willing to make other changes to help things along.
Read on for an example of one scenario in which reversing an index can help improve performance without touching the code.
Comments closedKey lookups represent a choice made by the optimizer between:
- Clustered index scan
- Nonclustered index seek/scan + Clustered index lookup
Lookups can be quite useful to avoid creating quite wide nonclustered index, but the optimizer is heavily biased against them. It does not like random I/O.
Read on for Erik’s thoughts on the matter and how you may be able to improve the performance of a query by removing a key lookup—or sometimes, how you may safely ignore a key lookup because hey, it’s a 1 millisecond difference on a query that runs once a day.
Comments closedErik Darling starts a new series:
Some of the best questions I get some clients, conference attendees, and random email, are about how to design indexes.
A lot of developers out there have a rather foggy picture of exactly how indexes work. They’re all seen phone books, and drawings of B-Tree indexes, but some common things still escape them.
In this post, I’m going to talk about a few things like I’m speaking to someone who has never created a table before.
The problem with the phone book analogy is that there’s an entire generation of people who haven’t used phone books.
Also, Erik has his own spin on the classic NUSE for cluster indexing.
2 CommentsEtienne Lopes takes us through a scenario:
Let me start this post with a question, “Do you think that it can be beneficial to have a single column index for the foreign key column in the child table?“
Well, I believe I can ear three types of answers to this question:
- Always!
- Never!
- It Depends…
Click through for Etienne’s answer. I’d still prefer these indexes to have multiple uses, which generally means having enough columns on the index to act as a covering index for one or more important queries. But Etienne does show a good use case for this single-column index.
Comments closedAaron Bertrand counts more efficiently:
That’s great when you want to count the whole table without size-of-entire-table reads. It gets more complicated if you need to retrieve the count of rows that meet – or don’t meet – some criteria. Sometimes an index can help, but not always, depending on how complex the criteria might be.
For me, counting more efficiently typically means I take off my shoes.
One other note is, if you just need a guesstimate, or if the cardinality of that column you’re splitting by is fairly low, you could also look at the histogram, especially if there’s a statistic on the column (or columns) you’re interested in. It’s rare that I think to go that way, but it is one of the tools the optimizer itself uses, so it’s fair game.
Comments closed