Production DBA
Database Design Choices To Consider When You’re Worried About Scale
Tipping the Kilter
I'm intentionally avoiding physical/hardware choices in this post, like how much hardware to buy, and scaling out vs. scaling up.
Locking, Isolation, and Read Consistency
Isolation levels, NOLOCK, row versioning, RCSI, and lock behavior.
36 associated posts36 primary posts
Performance Tuning
Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.
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-6a6ed21b5c660700282045/] We're just setting everyone's web page to ours.…
Performance Tuning
Locks Taken During Indexed View Modifications
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.
Performance Tuning
The Many Levels Of Concurrency
"Concurrency is hard"
When designing for high concurrency, most people look to the hardware for answers. And while it's true that it plays an important role, there's a heck of a lot more to it.
Start Optimistic
Like, every other major database vendor is optimistic by default. Even the free ones. SQL Server is rather lonesome in that, out of the box, you get the Read Committed isolation level.
Performance Tuning
Heaps, Deletes, and Optimistic Isolation Levels
The Humble Heap
If you don't know this by now, I'm going to shovel it at you:
If you have a table with no clustered index (a Heap), and you delete rows from it, the resulting empty pages may not be deallocated.
Performance Tuning
Partition Level Locking: Explanations From Outer Space
It's not that I don't like partitioning
It's just that most of my time talking about it is convincing people not to use it.
They always wanna use it for the wrong reasons, and I can sort of understand why.
Performance Tuning
Locking When There’s Nothing To Lock
Demo Day
We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it's pretty awesome.
News & Opinion
CTEs, Views, and NOLOCK
This is a post because it surprised me
It might also save you some time, if you're the kind of person who uses NOLOCK everywhere. If you are, you're welcome. If you're not, thank you. Funny how that works!
I was looking at some code recently, and saw a CTE. No big deal. The syntax in the CTE had no locking hints, but the select from the CTE had a NOLOCK hint. Interesting. Does that work?!
Performance Tuning
Is NOLOCK Ever The Right Choice?
I've poked a lot of fun at NOLOCK
I mean, it sucks. You can get incorrect data, and it can throw errors if your data is particularly volatile. Generally, outside of testing a query or grabbing some sample data, I'm going to avoid it.
But what if...
What if it's about all you can handle? Or your server can handle?
Performance Tuning
Does Creating an Indexed View Require Exclusive Locks on an Underlying Table?
An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let's test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…
Performance Tuning
Staging Data: Locking Danger with ALTER SCHEMA TRANSFER
Developers have struggled with a problem for a long time: how do I load up a new table, then quickly switch it in and replace it, to make it visible to users?
There's a few different approaches to reloading data and switching it in, and unfortunately most of them have big problems involving locking. One method is this:
Performance Tuning
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)
When learning how Read Committed Snapshot Isolation works in SQL Server, it can be a little tricky to understand how writes behave. The basic way I remember this is "Readers don't block writers, writers don't block readers, but writers still block writers." But that's not so easy to understand. Let's take a look at a simple test…
Performance Tuning SQL Server Change Tracking
Change Tracking is a developer tool introduced in SQL Server 2008 to help you sync data between SQL Servers, or between SQL Servers and devices. It's designed to help you answer the request, "I need to get the most recent value for rows that have been modified since I last grabbed data." First, I'll give you…
Performance Tuning
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
A client said the coolest thing to me the other day. He said, "We talked before about why we would want to start using optimistic locking in our code. How do we get there?" If you're not a SQL Server geek, that comment probably doesn't even make sense. But to some of us, when you…
Performance Tuning
SQL Server Locking and You!
Did you know that SQL Server's locking has a name? It's called two-phase locking. If we're really getting specific about it, SQL Server uses what's called strong strict two-phase locking or SS2PL. We'll get there in a few minutes, right now we're going to take a look at what makes up two-phase locking.
But First, Some History
Performance Tuning
It’s a Lock: Due Diligence, Schema Changes, and You
It's morning standup, and someone says, "It's no big deal, we just need to add a couple of columns. It's already in the build, it works fine."
The next time this happens, stop and say, "Let's take a closer look at that."
