When optimizing an OLTP database, one of the key performance issues is to isolate the writes from the reads. We’ve been talking about using Immutable data as a way to keep reads from blocking writes, by reading without locks, etc. Today I want to talk about another method that works rather well.
Even if you use database locking, and have repeatable read locks on a table, you can have good performance if you are able to isolate the read locks from write locks, so that they don’t overlap. One way to do that is to create a database where you don’t update data. Ok, that sounds like the whole immutable concept we have been talking about, and it is pretty much the same.
But, I want to share an old war story about how we had to do this in the old days of SQL Server Version 6. In older versions of SQL Server there was no concept of row level locking. The only levels of locking available were page, extent and table. In Version 6 of SQL Server they introduced row level locking on insert transactions only.
I was working for a group that developed a high volume auto auction system based on SQL Server at a time when WinTel systems were not common for heavy loads. Our database was a bottleneck we solved with the help of a couple really smart DBAs (from whom I learned a ton of stuff). They first modified our schema so that we primarily did insert transactions. This had a huge benefit right off the bat. Prior to that, we increased our transactional tables to require an entire page each. Since page level locking was the smallest amount available, having a record size
of a page resulted in virtual page level locking.
Another thing that happens with any locking in a database is the escalation of locks to reduce overhead. If it starts out with row level locking, and the locks increase where the management of those locks takes too many resources, SQL Server will escalate that lock to the next level up. Row level locks escalate to a page, then to an extent, and finally to an entire table. So now you have two goals for optimization.
1) If you are reading a few records, try and design your system such that any locks will be at a row level, reducing the probability of contention with other queries.
2) If you are reading a lot of records, try and design your system such that those reads are not in the same area of disk where data is being written. This way, if the locks escalate to pages or extents, it is not blocking your writes at the same time.
Microsoft recommends having a clustered index on a sequential value for tables. They have optimized the engine for that design. If you think about locking you may find that this design helps with the separation of reads and writes. All new writes are at the end of the table. Most likely reads are for older data, which is earlier in the table, and possibly having no overlap with the new data being inserted. Now, if you design you database to prefer Inserts over Updates, you’ll find much less contention and deadlocks, at least in theory, with your read processes.
What about in memory tables or SSDs? Does that change things? Well it only changes things in that those hardware implementations run faster. However, you still have locking, and lock management. So, if you optimize your data within your SQL Server structures, regardless of the storage implementation, you reduce the amount of contention leading to Blocking or Deadlocks.
Cheers,
Ben