While most DBAs are familiar with how locking works with tables, few DBAs are familiar with how SQL Server 2005 applies locking to indexes. That’s right, indexes, just like tables, are subject to locking. And as most DBAs already know, inappropriate locking (in tables or indexes) can negatively affect the performance of SQL Server.
Just as with table locking, the SQL Server query optimizer dynamically manages index locking, doing its best to balance the needs of locking with the performance needs of the server. It is important to remember here that while locking can be a performance burden on SQL Server, it is a very important and critical function of SQL Server. After all, locking is used to prevent multiple users from changing the same data at the exact same time. The query optimizer does its best to balance the amount and type of locking it does in order to ensure data integrity, while at the same time not negatively impacting performance more than it has to.
One of the ways that the query optimizer does this is through what is called lock escalation. In other words, the query optimizer does its best only to lock the minimum amount of rows it needs to. For example, the most granular locking occurs at the individual row level. If only one row needs to be locked, this is great as it minimizes the odds that another user will be blocked if accessing the same index. But in some cases, due to the nature of the index modification that needs to be made, the query optimizer might have to lock thousands of rows. The more individual rows that SQL Server has to lock, and keep track of the locks for, the more resources it takes to accomplish this task. So, in some cases, especially if there are lots of locks that need to be made, SQL Server will escalate the locking from row level to the page level. By locking and tracking pages instead of individual rows, fewer resources are used. On the other hand, it also increases the likelihood that blocking will occur as more rows are affected. This is the balancing decision the query optimizer has to make.
At the very extreme, if a huge amount of rows needs to be locked for modification, the query optimizer may lock the entire index instead of just locking individual data pages or rows. Again, this is done because it is less resource intensive to lock an entire index than it is to lock and manage hundreds, if not thousands of individual pages or rows. But again, while fewer resources are used when an entire index is locked, this may prevent anyone else from accessing the index until the index lock is removed. This depends on what kind of lock is being used.
The process of lock escalation I have just described is called escalation because the query optimizer generally first tries to use row locking, then page locking, and then full index locking. In other words, the query optimizer might start out at the row level, and then end up locking the entire index. As you can imagine, while this escalation process is indeed a worthwhile process, it in itself is time consuming and resource intensive. In other words, it would be more efficient if a full index lock were needed, that it be acquired immediately instead of having to first go through the escalation process of row and page locking. Unfortunately, the query optimizer isn’t always smart enough to know to do this.
Most of the time, the query optimizer does a great job of managing index locking, but there are some circumstances where the query optimizer may not perform index locking as ideally as it could.
For example, say that a particular database has a table that is rarely changed, but used quite often. A lookup table is a good example. It is possible that this lookup table is being used tens of thousands of times every day. Each time the table is accessed, the query optimizer will go through the lock escalation process. Because this is a lookup table, and rarely changed, it would make more sense, from a resource and performance standpoint, if a shared index lock were used each time the table was accessed instead of potentially going through a lock escalation process each time it is accessed.
Before SQL Server 2005, the DBA had no control over index locking escalation. They had to live with any decisions made by the query optimizer. But in SQL Server 2005, the DBA now has some ability to affect how the query optimizer uses lock escalation. For example, if we want, we can now tell the query optimizer that we want to skip row and/or page locking in an index and go immediately to an entire index lock. If we did this for our lookup table example above, we would always use shared index locking, reducing overall resource usage of SQL Server, and helping to boost overall performance.
Deciding whether you should intervene in how the query optimizer performs lock escalation on indexes is not an easy decision. It should only be done by experienced DBAs who fully understand all the implications of changing the query optimizer’s behavior.
But if are inclined to give this a try, here is how you modify index locking behavior in SQL Server 2005. Within the CREATE INDEX and the ALTER INDEX statements are these two new options:
- ALLOW_ROW_LOCKS (default is ON): Determines whether row locks are used in accessing index data.
- ALLOW_PAGE_LOCKS (default is ON): Determines whether page locks are used in accessing index data.
As you can see, the default for these two new options is that they are on. This means that the query optimizer is free to use row, page, or entire index locking at its discretion. As a DBA, you can choose to tell the query optimizer to turn row locking off, to turn page locking off, or to turn both row and page locking off. In this last case, then the only option the query optimizer has is to use full index locking.
As you can see, this is done on an index-by-index basis, which means that if you decide to use these new index escalation control options, you really need to fully understand your database, your data, your indexes, and how the data is accessed by your application. If you don’t, then electing to use either one of these options only means you are asking for trouble.
]]>