Normally when I tell people about SQL Server's optimistic concurrency isolation levels, Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI), I have to give them a little speech about how they need to test their queries because the results can change. Recently, though, I was working with a client who was getting the wrong query…
We're heading towards graduation time.
In just a few short months, your high school kids are going to be packing up their bags and heading off to college. It's a time of adventure and discovery, and the first time they'll be away from your parenting and guiding influences.
I'm kinda weird. I get excited when I'm troubleshooting a SQL Server problem, and I keep hitting walls.
I'll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:
You're investigating your SQL Server's top wait stats, and you're noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you're just not getting complaints from your end users, and you're wondering what's causing it.
It might be overzealous index rebuilds.
When you find yourself using NOLOCK, consider NOSQL instead.
NOLOCK has 4 big problems:
Hey, this seems harmless.
Seriously, don't SET DEADLOCK_PRIORITY HIGH.
In yesterday's post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn't fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online's summary of…
When you put NOLOCK in your query, SQL Server will:
Read rows twice
Skip rows altogether
Show you changes that never actually got committed
Let your query fail with an error
Some of y'all, bless your hearts, are really, really, really in love with NOLOCK.
I've shown you how you get incorrect results when someone's updating the rows, and I've shown how you get wrong-o results when someone's updating unrelated rows. It doesn't matter - there's always one of you out there who believes NOLOCK is okay in their special situation.
No, not NOLOCK. You get the wrong query results, even when your query results aren't supposed to be changing.
I'm talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm.
Say for some reason you can't use identity columns or sequences, or maybe your application was built a long, long time ago - and you've got a table with a list of IDs to use next:
[crayon-6a697e17dae67803817682/]
When I'm troubleshooting a blocking emergency, the culprit is usually the query at the head of a blocking chain. Somebody did something ill-advised like starting a transaction and then locking a whole bunch of tables.
But sometimes, the lead blocker isn't the real problem. It's query #2.
To fix blocking & deadlocks, you have 3 tools:
Have enough indexes to make your queries fast, but not so many that they slow down delete/update/insert operations. (I cover that in the Mastering Index Tuning class.)
Use the right isolation level for your app's needs. (I cover that in the Mastering Server Tuning class.)
Tuning your T-SQL to work through tables in a consistent order and touch them as few times as possible.
Locking isn't a problem - queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won't show up as a problem. (That's one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…
I've already covered how NOLOCK gives you random results when you're querying data that's changing, and that's a really powerful demo to show folks who think NOLOCK is safe to use in production. However, I've gotten a question from several users: But I'm querying data that isn't changing - sure, OTHER rows in the table…
Implicit transactions are a hell of a bad idea in SQL Server: they require you to micromanage your transactions, staying on top of every single thing in code. If you miss just one little DELETE/UPDATE/INSERT operation and don't commit it quickly enough, you can have a blocking firestorm.
The ideal answer is to stop using implicit transactions, and only ask for transactions when you truly need 'em. (Odds are, you don't really need 'em.)
Legendreary
In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.
I didn't even get to the point where I wanted to test all those relationships.
Slapping WITH (NOLOCK) on your query seems to make it go faster - but what's the drawback? Let's take a look.
We'll start with the free StackOverflow.com public database - any one of them will do, even the 10GB mini one - and run this query:
[crayon-6a697e17ddc0b947552865/]
We're just setting everyone's web page to ours. Then in a separate window, while that update is running, run this:
[crayon-6a697e17ddc16669938385/]
The results? A picture is worth a thousand words:
Frankenblog
This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments.
A long while back, Conor Cunningham wrote:
This same condition applies to indexed view maintenance, but I’ll save that for another day :).
AFAIK he hasn't written about it or typed an emoji since then.
Say you've got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy - just do TRUNCATE TABLE - but things get much harder when you need to delete a small percentage of them, say 5%.
It's especially painful if you need to do regular archiving jobs, like deleting the oldest 30 days of data from a table with 10 years of data in it.
