Demo Day
We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome.
Where things get tricky is with locking demos.
Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t sleeping.
Sometimes rollbacks can take a long time. After all, they’re single threaded, and you know how I feel about things that are single threaded.
The No-Row Update
To get around this, I decided to try some updates that didn’t actually update any rows. The WHERE clause excludes everything in the Users table.
I thought that SQL would outsmart me here, but it didn’t. It didn’t even try.
Here are two queries, neither of which will have any qualifying rows in my 2016/03 copy of the database. (If you’re using a more recent export, choose a more recent LastAccessDate.)
|
1 2 3 4 5 6 7 |
SELECT * FROM dbo.Users AS u WHERE u.LastAccessDate >= '20160307' SELECT * FROM dbo.Users AS u WHERE u.Reputation >= 2147483647 |
The only index on the Users table is a PK/CX on Id. That means it’s the one way in or out of the table for any queries, no matter what they’re trying to do.
So if I run an update that will also hit 0 rows, it has to use that.
|
1 2 3 4 5 |
BEGIN TRAN UPDATE dbo.Users SET Reputation = Reputation + 100 WHERE Reputation >= 2147483647 |
While that update is running, I’ll try to run my query with the WHERE clause on LastAccessDate. It’ll get blocked — and we can confirm that by running sp_BlitzWho.
Rollback, Repeat
So look, clearly we need an index.
Maybe we need 75 indexes. If we do, we’ll run DTA and then set our pants on fire in defiance of all humanity.
But in the meantime, let’s try adding one.
|
1 |
CREATE UNIQUE NONCLUSTERED INDEX ix_helper_lad ON dbo.Users (LastAccessDate, Id) |
This gives our select query a separate access path. Sort of. I know what you’re thinking — that SELECT * is a trick! He’s messing with us. The clustered index needs to get updated, so the key lookup will get blocked. Ha ha ha. Nice one, dummy. We’re smarter than you.
So I’ll do this instead.
|
1 2 3 |
SELECT u.Id, u.LastAccessDate FROM dbo.Users AS u WHERE u.LastAccessDate >= '20160307' |
This query only needs columns in our nonclustered index, so it won’t get blocked. Right?
Right?
The answer lies in sp_BlitzWho’s much cooler older brother, sp_WhoIsActive.
|
1 |
EXEC dbo.sp_WhoIsActive @get_locks = 1 |
Our update query is locking the whole darn thing. Even though it changes 0 rows, and the query is done running (though it’s in a BEGIN TRAN), we’re still stuck with this lock.
So what to do?
Our clustered index doesn’t help, and our nonclustered index doesn’t either. Maybe our update needs an index?
It’s simple enough. We’re updating Reputation, and filtering on Reputation. Maybe we need an index on Reputation, too?
We have some options.
A separate index that leads with Reputation, and index that leads with LastAccessDate, and an index that leads with Reputation.
|
1 2 3 4 5 |
CREATE UNIQUE NONCLUSTERED INDEX ix_helper_rep ON dbo.Users (Reputation, Id); CREATE UNIQUE NONCLUSTERED INDEX ix_helper_lad_rep ON dbo.Users (LastAccessDate, Reputation, Id); CREATE UNIQUE NONCLUSTERED INDEX ix_helper_rep_lad ON dbo.Users (Reputation, LastAccessDate, Id); |
For the sake of blog post brevity: the index that leads with LastAccessDate doesn’t fix our blocking scenario.
So what’s better: Two separate indexes on (Reputation, Id) and (LastAccessDate, Id), or one index on (Reputation, LastAccessDate, Id)?
And then what if our UPDATE changes? What if we need to filter on LastAccessDate? Or something else? What if having LastAccessDate as the second key column makes our SELECT queries eat speed bumps?
|
1 2 3 |
UPDATE dbo.Users SET Reputation = Reputation + 100 WHERE LastAccessDate >= '20160307' |
You can’t index for everything, and as we sort-of-glossed-over, leading index key columns seem necessary to get us out of locking jams.
Sure, you could get around things with NOLOCK hints, but if that makes you queasy, it’s time to start looking into other Isolation Levels.
Indexes can be extra tricky. If you’re lucky enough to be going to PASS, be sure to hit up Kendra’s session on indexing.
I’ll probably be there, making sure you don’t start texting.
7 Comments. Leave new
Hi Erick,
[[You can’t index for everything]]
True, but you can index for anything.
I have had some success with “temporary indexes” (create it in the query, drop when query finishes) for situations where the overall squeeze of leaving a permanent index is not worth the juice 🙂
Do you ever try them?
In short, yes, but there are a lot of caveats.
Creating the index has to be faster and cheaper, and I’d tend not to do it on Standard Edition with the lack of online index creation, etc.
yep, needs to be faster and cheaper to be useful. And those qualities could be unpredictable over time as the underlying data changes, but the code does not 🙂
But they have definitely helped me on a number of occasions to go from unacceptable, to acceptable, performance.
And I have been lucky not to have to worry about standard edition since I was a student…
Ah, yeah, as data grows I usually start to think if a filtered index would be more appropriate. There’s usually at least one constant that would make a good candidate.
set read_committed_snapshot on?
Go on.
I use it to make more “fluid” my oltp db, particularly report sections which become independent from running write transactions.