Jeffry Schwartz takes us through an odd case:
Recently, a customer requested that we tune a query that took 13 seconds to return 11 rows. SQL Server 2017 suggested an index to improve performance, so we added it in a development environment. The improvement made the query run 647 seconds, almost 50 TIMES longer than the original! This naturally caused much consternation, so we decided to determine what and why it happened as well as how we could still achieve the original objective, i.e., make the query run faster. This article discusses what caused the original performance problem in addition to the new one that was caused by the introduction of an index, and illustrates how we were able to make the query run significantly faster than it did originally. We will cover reading query plans, examining the specific details of query plan operators, the effects of index statistics on missing index recommendations, using query plan XML to enable simpler query plan comparison, and the effects of using functions in where clauses.
Click through to understand how this could be the case.
With last page insert contention, as the number of insert threads increases, the queue for the page latch increases which in turn increases latency. Throughput will also decrease, but if something slows down one of the threads that is holding the latch, this can trigger a convoy and throughput suddenly falls off a cliff. This typically happens when a page fills up and a new page must be added to the index (also known as a page split). The insert that triggers the new page will naturally have to hold the latch for longer than normal while the new page operation completes. This causes the queue to build up behind the latch. Adding a new page also requires an exclusive latch on the parent page, which can cause latch requests to queue at that level as well. At this point, throughput falls off a cliff.
OPTIMIZE_FOR_SEQUENTIAL_KEY aims to do two things – control the rate at which new threads are allowed to request the latch, and favor threads that are likely to keep the throughput high. These techniques will not prevent the contention or reduce latency, but they will help keep throughput consistent as concurrency increases.
Read on to learn more about the process and by implication some places where this won’t work well at all for you.
Erik Darling looks at how available indexes may contribute to parameter sniffing problems:
When you’re troubleshooting parameter sniffing, the plans might not be totally different.
Sometimes a subtle change of index usage can really throw gas on things.
It’s also a good example of how Key Lookups aren’t always a huge problem.
Both plans had them, just in different places.
The plans had a small change, but that made a big difference.
Erik Darling is fusing together queries like Dr. Frankenstein in his lab:
You may have noticed that both queries get pretty bad estimates. You might even be thinking about leaving me a comment to update stats.
The thing is that I created these indexes, which means they get stats built with a full scan, and it’s a demo database where nothing changes.
We just get unfortunate histograms, in this case. If I create very specific filtered statistics, both plans perform a key lookup.
I snipped this part because it’s so ingrained in DBAs that when performance is bad, updating statistics is a panacea.
Erin Stellato takes us through a bit of indexing strategy:
In the sp_SQLskills_helpindex output you’ll notice that OrderLineID is the only column in columns_in_tree, and both OrderLineID and OrderID_and_OrderLineID are in columns_in_leaf.
Remember that when you identify a nonclustered index as unique, it will not add the clustering key to the tree level, it just adds it to the leaf level. But if the nonclustered index is not declared as unique, then SQL Server adds the clustering key to the tree level to uniquely identify the row.
This post deserves a careful reading.
Erik Darling gives us multiverse indexing:
Missing index request column order is pretty basic.
Instead, we’re gonna add these:
CREATE INDEX ix_spaces
ON dbo.Posts(ParentId, Score);
CREATE INDEX ix_tabs
ON dbo.Posts(Score, ParentId);
SQL Server is capable of using both intelligently. This is something I generally don’t like to do, but if you have queries which absolutely need differently-ordered index columns, it can make sense to do this. Just don’t expect SQL Server’s missing index DMV to tell you which order they should be in.
Hasan Savran revises some indexing recommendations based on changes to Cosmos DB:
Lazy indexing used to be an option. It’s not in any CosmosDB documentation anymore. By using Lazy indexing, you could save 20 to 30 percent for Request Units. Just like anything else in life, you get what you pay for when it comes to Lazy indexing. By selecting Lazy indexing, you are saying that eventually Indexes will be updated. If Indexes are not updated, that means your queries might not return all the data since all data might not be indexed yet. Lazy indexing is still an option, nobody talks about it for a good reason. In my opinion, it should be listed as obsolete feature or it should have a better documentation about how it works or why it might not be a good option for your solutions.
If you use Lazy Indexing to reduce Request Units in your solution, change it to consistent now unless you have a really good reason!
Read on for more advice in this vein.
Erik Darling wants to pump your indexes up:
Now, there’s an Extended Event that… Used to work.
These days it just stares blankly at me. But since I’ve worked with this before, I know the problem.
It’s that Key Lookup — I’ll explain more in a minute.
Adaptive joins won’t do all the work for you, so Erik explains how you can set yourself up for success.
Denis Gobo violates Betteridge’s Law of Headlines:
This question came up the other day from a co-worker, he said he couldn’t change a query but was there a way of making the same query produce a better plan by doing something else perhaps (magic?)
He said his query had a WHERE clause that looked like the followingWHERE RIGHT(SomeColumn,3) = '333'
I then asked if he could change the table, his answer was that he couldn’t mess around with the current columns but he could add a column
Click through to see how Denis was able to solve this problem.
Tables were very narrow with just a few columns and my expectations for data growth were very modest. However, after just a little while I was very surprised when my database showed huge unexpected growth and size of the data became multiple times higher than I’ve expected.
After very little research I’ve found and fixed the problem. In this post I’ll describe how I’ve done it.
Read on to learn how Slava figured this out and how a clustered index fixed the problem.
Kevin Feasel
2019-08-20
Indexing, Query Tuning
No Comment