Recently I was at a customer who had created an indexed view which spanned multiple tables as part of a release. Although the index was already created, it wasn’t being used by the application yet. Short after the release, they noticed large blocking chains. When large(r) operations were executed on one of the underlying tables, no modifications could be made to any of the other tables that were part of the indexed view. After investigating the issue, it became clear that lock escalation was the issue. The indexed view wasn’t ready for reading, therefore there were very few transactions to block the lock escalation process. Because you can’t (temporarily) disable lock escalation for an indexed view as you can for a table, I needed to find another solution.
To comprehend the principle of lock escalation you first need some background information about lock hierarchy in SQL Server. The following picture depicts the lock hierarchy in SQL Server.
On top of the hierarchy you can find database locks. Whenever you run a query against a database, there will always be a shared lock on the database. This lock will prevent the database from being dropped or overridden while it’s being used.
Below the database, there are 4 other levels: Table, Partition (if enabled), page and row. On each of these levels, SQL Server can place different kind of locks: Exclusive, Update, Shared, Intent and a combination of the foregoing. An in depth analysis of the different kind of locks and their compatibility is beyond the scope of this article. If you want more information about this subject, there is plenty of information to find online.
What is lock escalation
Each lock that’s being held by the lock manager uses 96 bytes of memory. Whenever running a lot of concurrent queries, locks can take up a significant portion of the available memory. Imagine running a select query against 6000 records: Each record is 400 bytes in size, which means we need 300 pages to store our data. Without lock escalation, this query would at least require 1 shared database lock, 1 intent-shared table lock, 300 intent-shared page locks and 6000 shared row locks. This would give us a total of 6302 locks or 600kB of memory for a simple select query. To prevent this kind of situation, SQL Server uses a mechanism called “Lock Escalation“. Whenever escalating locks, SQL Server will try to replace its lower level locks (page/row) for a table or partition lock. Partition locks are only possible for partitioned tables where LOCK_ECALATION has been set to AUTO. A common misunderstanding is that row locks first escalate to page locks, this is not the case. Row locks always escalate to either partition or table locks!
There are two reasons for SQL Server to trigger lock escalation. The first reason is the number of locks on a single reference of a table or partition. SQL Server will try to escalate whenever a single T-SQL statement acquires at least 5000 locks on a non-partitioned table, index or partition. The second trigger to escalate is based on the total amount of memory used lock objects. If the locks option (which sets the maximum number of available locks) hasn’t been configured, the threshold is set on 40% of the memory used by the Database Engine. If the locks option has been configured, the threshold is set to 40% of the configured number of locks (or even less in the case of memory pressure). If lock escalation fails because other transactions have locks on the object, SQL will wait for another 1250 locks and try again.
Locking on indexed view
Whenever data gets modified in tables that are part of an indexed view, those modifications will be applied to the view as well. Let’s use a short demo to demonstrate this behavior.
First, let’s create a new view in the AdventureWorks database.
CREATE VIEW vw_Sales WITH SCHEMABINDING AS SELECT SalesOrderID, SUM(OrderQty) As OrderSty, SUM(LineTotal) as TotalPrice, COUNT_BIG(*) as Cnt FROM [Sales].[SalesOrderDetail] GROUP BY SalesOrderID GO CREATE UNIQUE CLUSTERED INDEX [CIX_vw_Sales_SalesOrderID] ON [dbo].[vw_Sales] ( [SalesOrderID] ASC ) GO
Next, we’ll make the executive board of AdventureWorks happy and increase some order quantities in [Sales].[SalesOrderDetail].
BEGIN TRAN ; WITH cte AS ( SELECT TOP (10000) * FROM [Sales].[SalesOrderDetail] ) UPDATE cte SET OrderQty = OrderQty + 1 ROLLBACK
As you can see in the execution plan, SQL Server first updates the data in the table and then uses a table spool to fetch and update the corresponding records in the indexed view.
To illustrate the locking that occurs on the indexed view, I’ve temporary disabled lock escalation. In the image below you can see the locks on [Sales].[SalesOrderDetail] and [dbo].[vw_Sales]. The total amount of memory needed for the locks on both objects would be around 3,8MB.
If we re-run the previous statement with lock escalation enabled, SQL will try to escalate its page and row locks to a table lock. This process is triggered by the high number of locks on the objects. As you can see in the following excerpt of an extended event, SQL escalated the locks on both the table and view to a higher level after 6246 locks.
The following XML shows the locks on both objects after the locks have been escalated. As you can see there is an exclusive lock on both objects. If there is a combination of different lock types the most restrictive type will always be escalated to. This means that no other query can use either of both objects. Imagine having an indexed view using multiple tables. No insert, update or delete on any of the underlying tables would be possible anymore! In my case, the customer already created the indexed view (which spanned multiple tables) but it wasn’t being used by the application yet. Therefore, there were very few transactions to block the lock escalation process. As a result, exclusive locks were taken on the indexed view object which blocked all DML statements on any of the underlying tables. This caused long blocking chains and irresponsiveness of the application.
Prevent lock escalation on indexed views
The best way (by far!) to prevent lock escalation, is splitting up your transactions into multiple smaller transactions. By using more and smaller batches, each batch will use less data which will prevent lock escalation by keeping the number of locks below the threshold of 5000. Another advantage is that locks are kept for a shorter period than when using a single large transaction. If any blocking occurs it will be resolved faster. I’ve executed the update statement again for 1000 records. As you can see, no exclusive object locks are taken.
Disable lock escalation for a table
It’s possible to disable lock escalation for a particular table. For indexed views on the other hand this command doesn’t work. When enabling the setting for the underlying table(s) of an indexed view, it won’t be applied to the view.
In the next example I’ve disabled lock escalation for Sales.SalesOrderDetail by executing following command:
ALTER TABLE Sales.SalesOrderDetail SET (LOCK_ESCALATION = DISABLE)
As you can see, the locks on the table don’t get escalated anymore. The locks on the indexed view on the other hand still get replaced by a single object lock.
Disable lock escalation in an instance
Another (aggressive) way to prevent lock escalation is by disabling it entirely for a SQL instance. There are two trace flags that can be used to serve this purpose: 1211 & 1224.
Trace flag 1211 is the most aggressive trace flag of both. It disables lock escalation completely, both based on number of locks as on memory pressure. This may cause an excessive amount of locks, ultimately causing memory allocations for lock objects to fail or to slow down the system.
Trace flag 1224 will only disable lock escalation based on the amount of locks, memory pressure can still activate the mechanism. This trace flag is safer to enable then 1211, although it is still not recommended.
Both trace flags can be used for individual sessions as well.
Intent lock on indexed view
In the case of the customer we needed a quick temporary solution, without too much changes on the database or in the application code. As stated before, one of the reasons that we witnessed lock escalation was the lack of activity on the view. As a result, almost no escalation attempts were blocked.
This situation made look for a way to block the lock escalation without causing any performance impact. During my search I came across following Microsoft support article: https://support.microsoft.com/af-za/help/323630/how-to-resolve-blocking-problems-that-are-caused-by-lock-escalation-in . As described in the article, I needed a lock type at the object level that would cause as few blocking as possible. This lead me to intent exclusive locks (IX). An IX lock is compatible with each other type of intent lock, thus with each key or page lock taken by another transaction. The IX lock isn’t compatible with any other “regular” lock. This makes it impossible for a more granular lock to escalate to the table or object level.
Returning to our example, I need to run the query below to place an IX lock on the indexed view. By using the NOEXPAND hint, I made sure that no lock would be placed on the underlying table. I know this solution isn’t pretty, but hey its temporary and it works. It’s hard to explain to your customer that you prefer them to be unable to work, just because you think some solution isn’t pretty. Furthermore, the risks were limited since the indexed view wasn’t being used directly by the application.
BEGIN TRAN SELECT 1 FROM [dbo].[vw_Sales] WITH (NOEXPAND, UPDLOCK, HOLDLOCK) WHERE 1=0 WAITFOR DELAY '0:05:00' COMMIT TRAN
If we look at the locks taken by the script we only see an IX lock on the indexed view.
While the script is running we update the top 10000 records of [Sales].[SalesOrderDetail] again. Using our extended event we can see that there was an escalation event for [Sales].[SalesOrderDetail], but not for the indexed view! Since the transaction can’t place an exclusive lock on the view, the escalation gets blocked and the transaction continues to use row and page locks. In the case of my customer, they were able to modify data in the underlying tables without blocking each other.
If possible, always try to split your transactions into multiple shorter transactions. This will prevent lock escalation and reduce the duration of the locks. But if you are, just as I was, in great need of a temporary quick fix then you can always try to block lock escalation by placing an IX lock on the indexed view object. Don’t forget to monitor the impact on your database after implementing this solution!