(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan, where you are getting week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.)

In todays blog posting I want to talk about Lock Escalations in SQL Server. Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server. Let’s start in the first step with the description of the so-called Lock Hierarchy in SQL Server, because that’s the reason why the concept of the Lock Escalations exists in a relational database like SQL Server.

Lock Hierarchy

The following picture shows you the lock hierarchy used by SQL Server:

As you can see from the picture, the lock hierarchy starts at the database level, and goes down to the row level. You always have a Shared Lock (S) on the database level itself. When your query is connected to a database (e.g. USE MyDatabase), the Shared Lock prevents the dropping of the database, or that backups are restored over that database. And underneath the database level, you have locks on the table, on the pages, and the records when you are performing an operation.

When you are executing a SELECT statement, you have an Intent Shared Lock (IS) on the table and page level, and a Shared Lock (S) on the record itself. When you are performing a data modification statement (INSERT, UPDATE, DELETE), you have an Intent Exclusive or Update Lock (IX or IU) on the table and page level, and a Exclusive or Update Lock (X or U) on the changed records. SQL Server always acquires locks from top to bottom to prevent so-called Race Conditions, when multiple threads trying to acquire locks concurrently within the locking hierarchy. Imagine now how the lock hierarchy would look like, when you perform a DELETE operation on a table against 20.000 rows. Let’s assume that a row is 400 bytes long, means that 20 records fit onto one page of 8kb:

You have one S Lock on the database, 1 IX Lock on the table, 1.000 IX locks on the pages (20.000 records are spread across 1.000 pages), and you have finally 20.000 X locks on the records itself. In sum you have acquired 21.002 locks for the DELETE operation. Every lock needs in SQL Server 96 bytes of memory, so we look at 1.9 MB of locks just for 1 simple query. This will not scale indefinitely when you run multiple queries in parallel. For that reason SQL Server implements now the so-called Lock Escalation.

Lock Escalations

As soon as you have more than 5.000 locks on one level in your locking hierarchy, SQL Server escalates these many fine-granularity locks into a simple coarse-granularity lock. By default SQL Server *always* escalates to the table level. This mean that your locking hierarchy from the previous example looks like the following after the Lock Escalation has been successfully performed.

As you can see, you have only one big lock on the table itself. In the case of the DELETE operation, you have one Exclusive Lock (X) on the table level. This will hurt the concurrency of your database in a very negative way! Holding an Exclusive Lock on the table level means that no other session is able anymore to access that table – every other query will just block. When you are running your SELECT statement in the Repeatable Read Isolation Level, you are also holding your Shared Locks till the end of the transaction, means you will have a Lock Escalation as soon as you have read more than 5.000 rows. The result is here one Shared Lock on the table itself! Your table is temporary readonly, because every other data modification on that table will be blocked!

There is also a misconception that SQL Server will escalate from the row level to the page level, and finally to the table level. Wrong! Such a code path doesn’t exist in SQL Server! SQL Server will by default *always* escalate directly to the table level. An escalation policy to the page level just doesn’t exist. If you have your table partitioned (Enterprise Edition only!), then you can configure an escalation to the partition level. But you have to test here very carefully your data access pattern, because a Lock Escalation to the partition level can cause a deadlock. Therefore this option is also not enabled by default.

Since SQL Server 2008 you can also control how SQL Server performs the Lock Escalation – through the ALTER TABLE statement and the property LOCK_ESCALATION. There are 3 different options available:

• TABLE
• AUTO
• DISABLE

-- Controllling Lock Escalation
ALTER TABLE Person.Person
SET
(
	LOCK_ESCALATION = AUTO -- or TABLE or DISABLE
)
GO

The default option is TABLE, means that SQL Server *always* performs the Lock Escalation to the table level – even when the table is partitioned. If you have your table partitioned, and you want to have a Partition Level Lock Escalation (because you have tested your data access pattern, and you don’t cause deadlocks with it), then you can change the option to AUTO. AUTO means that the Lock Escalation is performed to the partition level, if the table is partitioned, and otherwise to the table level. And with the option DISABLE you can completely disable the Lock Escalation for that specific table. But disabling Lock Escalations is not the very best option, because the Lock Manager of SQL Server can then consume a huge amount of memory, if you are not very carefully with your queries and your indexing strategy.

Conclusion

Lock Escalation in SQL Server is mainly a nightmare. How will you delete more than 5.000 rows from a table without running into Lock Escalations? You can disable Lock Escalation temporarily, but you have to be very careful here. Another option (that I’m suggesting) is to make your DELETE/UPDATE statements in a loop as different, separate transactions: DELETE/UPDATE less than 5.000 rows, so that you can prevent Lock Escalations. As a very nice side-effect your huge, big transaction will be splitted into multiple smaller ones, which will also help you with Auto Growth issues that you maybe have with your transaction log.

Thanks for reading

-Klaus

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan, where you are getting week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I want to talk about a very specific topic in SQL Server – Instant File Initialization. If you have Instant File Initialization for your SQL Server Instance enabled, you can have a tremendous performance improvement – under specific circumstances. Instant File Initialization defines how the SQL Server engine interacts with the Windows OS, when allocating new space in data files.

The Problem

When you are allocating new space in a data file in the default configuration of SQL Server, SQL Server has to call internally Win32 API functions that are zero-initializing the new allocated NTFS clusters. This mean that every byte of new allocated space is overwritten with zero values (0×0). This behavior prevents the problem of accessing old data, that was previously stored physically in the same NTFS clusters. The zero-initialization takes place during the following SQL Server operations:

• Creating a new database
• Auto growing a database
• Restore of a database backup

When you are creating a database file with 50 GB, SQL Server has to initialize in the first step that new block of data with 50 GB of zero values. And this can take a lot of time. Let’s have a look on the following CREATE DATABASE statement.

-- Create a new 50 GB database
CREATE DATABASE TestDatabase ON PRIMARY
( 
	NAME = N'TestDatabase',
	FILENAME = N'G:\SQL\DATA\TestDatabase.mdf' , 
	SIZE = 51200000KB , 
	FILEGROWTH = 1024KB
)
LOG ON 
(
	NAME = N'TestDatabase_log', 
	FILENAME = N'G:\SQL\Log\TestDatabase_log.ldf' ,
	SIZE = 1024KB , 
	FILEGROWTH = 10%
)
GO

As you can see from the code, I’m creating here a database file of 50 GB. In my default SQL Server configuration this statement takes around 16 seconds, because SQL Server writes through a Win32 API function 50 GB of zeros to the storage. Imagine what happens if you have a corrupt database (e.g. 50 GB of size), and you want to restore a backup? What people are normally doing in the first step, is to delete the corrupted database. This means that the database files are gone, and SQL Server has to recreate in the first step during the restore operation the files:

1. SQL Server creates in a first step an “empty” database of 50 GB, where the data file will be zero-initialized in the NTFS file system
2. As the final step the backup is restored, and SQL Server writes again 50 GB of data into the data files

As you can see, you are writing with this approach 100 GB of data to your storage! If you are just restoring your backup *over* the existing files, SQL Server can skip the first step, and just writes 50 GB of data to your storage – you have achieved an performance improvement of 100%!

Instant File Initialization

If you don’t want that SQL Server is doing the zero-initialization of your data files, you can reconfigure SQL Server. If you grant the service account, under which SQL Server is running – the privilege Performance Volume Maintenance Task, SQL Server will skip the zero-initialization of the data files, if you have restarted SQL Server afterwards. As I have said this only applies to data files – log files are ALWAYS zero-initialized in SQL Server! There is NO WAY around that!!! Without the zero-initialization of the log file, the crash recovery process would have no idea where to stop, when the log file was wrapped around. Crash Recovery stops where it finds zero values in the header of the next log record to be processed.

You can grant the permission Performance Volume Maintenance Task through secpol.msc to the service account of SQL Server.

After a restart, SQL Server is now able to skip the zero-initialization of data files. When I’m running the CREATE DATABASE statement from about again, it takes around 250ms – that’s a huge difference! The side-effect? You are able to retrieve the old content that was stored in the allocated NTFS clusters through the DBCC PAGE command:

-- Enable DBCC trace flag 3604
DBCC TRACEON(3604)
GO

-- Dump out a page somewhere in the data file
-- A hex dump is working here
DBCC PAGE (TestDatabase, 1, 1000, 2)
GO

As you can see I’m just dumping out a page somewhere in my data file. In that case, it can now happen that SQL Server just returns you some garbage data – data that was previously stored in the new allocated NTFS clusters – data that has no relevance to SQL Server:

By granting this permission to SQL Server, you are mainly opening a security hole: users (with the right permissions) are able to retrieve old data, that was previously stored in the file system. So you have to think very carefully about that, if you grant the permission to SQL Server, or not. 

If you want to know, if your SQL Server is running with this permission, or not, you can enable the trace flags 3004 and 3605. With these trace flags enabled, SQL Server reports in the error log which files are zero initialized. When you afterwards create a new database, and the permission wasn’t granted to SQL Server, you can see from the error log, that data AND log files were zero-initialized:

If SQL Server has the permission Perform Volume Maintenance Task, you can see from the error log, that ONLY the log file was zero-initialized:

The Windows Internals

But what happens now under hood in the Windows OS, when you grant the permission Perform Volume Maintenance Task to the service account, under which SQL Server is running? With this permission enabled (it’s internally called SE_MANAGE_VOLUME_NAME by the Win32 API), SQL Server is able to call the Win32 API function SetFileValidData. As you can see from the documentation, the process who is calling that function, has to have the permission SE_MANAGE_VOLUME_NAME. When that function is called by SQL Server, the function itself just sets the so-called High Watermark of the file – the file is just expanded WITHOUT overwriting the old content in the underlying NFTS clusters! As the documentation says:

“The SetFileValidData function allows you to avoid filling data with zeros when writing nonsequentially to a file. The function makes the data in the file valid without writing to the file. As a result, although some performance gain may be realized, existing data on disk from previously existing files can inadvertently become available to unintended readers.”

…

“If SetFileValidData is used on a file, the potential performance gain is obtained by not filling the allocated clusters for the file with zeros. Therefore, reading from the file will return whatever the allocated clusters contain, potentially content from other users. This is not necessarily a security issue at this point, because the caller needs to have SE_MANAGE_VOLUME_NAME privilege for SetFileValidData to succeed, and all data on disk can be read by such users.”

As I have said earlier, it’s mainly a security concern if you are enabling that specific permission for your SQL Server instance, or not.

Summary

Should you now enable Instant File Initialization for your SQL Server instance, or not? It dep… When you are the SQL Server AND Windows administrator, it’s a good idea to grant that permission, because as a Windows admin, you always have access to the file system. But when you have dedicated Windows and SQL Server admins, it can be the case, that the Windows admin doesn’t trust you, and that you are not getting that permission for your SQL Server instance. In that case SQL Server will always zero-initialize the data and log files…

Thanks for reading

-Klaus

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan, where you are getting week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.)

In todays blog posting I want to show you quickly about what happens in SQL Server when we are deleting records from a table. Let’s create in the first step a simple table, where 4 records fit onto one page of 8kb.

-- Create a simple table where 4 records fit onto 1 page
CREATE TABLE TestTable
(
	Col1 INT IDENTITY(1, 1),
	Col2 CHAR(2000)
)
GO

Afterwards we insert 4 records, so that we can fill completely one page:

-- Insert 4 records
INSERT INTO TestTable VALUES
(
	REPLICATE('1', 2000)
),
(
	REPLICATE('2', 2000)
),
(
	REPLICATE('3', 2000)
),
(
	REPLICATE('4', 2000)
)
GO

To dig into the details of our heap table, we will use the DBCC PAGE command to dump the allocated page out. Therefore we also have to enable the trace flag 3604 so that SQL Server returns us the result from the DBCC PAGE command directly into our session window in SQL Server Management Studio:

-- Enable the Trace Flag 3604
DBCC TRACEON(3604)
GO

We can use the DBCC IND command to return all the allocated pages to a specific table or index.

-- Retrieve all pages of the table
DBCC IND(DataModifications, TestTable, -1)
GO

As you can see from the output, 2 pages are belonging to our table: the data page itself, and the IAM (index allocation map) page.

I’m taking here the page id of 145, and dumping out the page through the DBCC PAGE command:

-- Dump out one specific page
DBCC PAGE (DataModifications, 1, 145, 2)
GO

When you are using as 3rd parameter the dump option 2, SQL Server returns you a hex dump of the page including the so-called Row Offset Array at the end of the page without interpreting the data in any way.

The Row Offset Array just points to the physical location on the page, where every record is stored. The 1st record is always stored directly after the page header at offset 96 (0x60h). As you can also see that the Row Offset Array always grows backwards. Let’s delete now the 2nd record from the table:

-- Delete a record from the table
DELETE FROM TestTable
WHERE Col1 = 2
GO

Normally you will expect here that the record is deleted from the page. But this is not really the case: when you execute the DBCC PAGE command again, you will see that the content of the old record is still available on the page. The only thing that SQL Server has done during the DELETE operation was the invalidation of the corresponding slot in the Row Offset Array at the end of the page.

As you can see, the 2nd slot has an offset of 0×0, which is invalid, means our record is deleted. At the beginning of a page, you will always find the page header of 96 bytes. Let’s delete now the other 3 remaining records from the table.

-- Delete all the remaining records from the table
DELETE FROM TestTable
GO

When you look again on the page with the DBCC PAGE command, you can see that the whole content of the page is unchanged: EVERY data of EVERY record is still physically present on the page itself! But every records points in the Row Offset Array to the offset 0×0, means every record is deleted. It doesn’t matter if you are using here tables with or without a Clustered Index – the old data is always still present on the page. 

The question is now, when SQL Server will reinitialize the page? When you are now inserting a new record, SQL Server will overwrite the old content of the page. But in our case, only the physical part, where the 1st record was stored. You are still able to see the contents of the other 3 “deleted” rows. But when you look at the Row Offset Array at the end of the page, you can see that it was completely reinitialized by SQL Server, which means you have now only 1 slot in it:

Just think about that behavior the next time, when you are granting applications sysadmin privileges. With the appropriate commands, these application will be able to see old “deleted” data

Thanks for reading

-Klaus

You are a developer and/or DBA and want to learn in a very compact way the essential things about performance tuning & troubleshooting SQL Server installations? In our new free 6 month long SQLpassion Performance Tuning Training Plan we give you week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.

As a Thank-You for your signup, you will also receive a coupon code for the SQLpassion Online Academy where you will get 3 online training videos for the price of 2. If you are interested in a high quality face-2-face performance tuning training, please also check out our famous SQL Server Performance Tuning & Troubleshooting Workshop, that we are running twice a year around Europe.

Read more…

-Klaus

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan, where you are getting week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.)

You know the problem: the “Estimated Number of Rows” in an operator in the Execution Plan is 42, but you know that 42 isn’t the correct answer to this query. You have also heard some time ago that SQL Server uses statistics to make that estimation. But how can you interpret these statistics to understand from where the estimation is coming from?

Today I want to talk about statistics in SQL Server, and how SQL Server internally stores the estimation values in the so-called Histogram and the Density Vector.

Histogram

Let’s have a first look on the histogram. The idea behind the histogram is to store the data distribution of a column in a very efficient, compact way. Every time when you create an index on your table (Clustered/Non-Clustered Index), SQL Server also creates under the hood a statistics object for you. And this statistics object describes the data distribution in that specific column. Imagine you have an order table, and a column named Country. You have some sales in US, in the UK, in Austria, in Spain, and Switzerland. Therefore you can visualize the data distribution in that column like in the following picture.

What you are seeing in that picture is a histogram – you are just describing the data distribution with some bars: the higher the bar is, the more records you have for that specific column value. And the same concept and format is used by SQL Server. Let’s have now a look on a more concrete example. In the AdventureWorks2012 database you have the table SalesOrderDetail and within that table you have the column ProductID. That column stores the ID of the product that was tight to that specific sale. In addition there is also an index defined on that column – means that SQL Server has also created a statistics object that describes the data distribution within that column.

You can have a look into the statistics object by opening the properties window of it within SQL Server Management Studio, or you can use the command DBCC SHOW_STATISTICS to return the content of the statistics object in a tabular format:

-- Show the statistics for a given index
DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', IX_SalesOrderDetail_ProductID)
GO

As you can see from the previous screen shot, the command returns you 3 different result sets:

• General information about the statistics object
• Density Vector
• Histogram

Within this blog posting we will concentrate on these 3 parts, and how they are used for the so-called Cardinality Estimation (the calculation of the estimated number of rows). Let’s run now a very simple query against the table SalesOrderDetail. As you can see from the following listing we are restricting for a specific ProductID:

-- SQL Server uses the EQ_ROWS to make the estimation, because the value is directly
-- available in the histogram.
-- 3083 are estimated for the the Filter operator
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 707
GO

The query itself returns 3.083 rows out from 121.317. Because we haven’t defined a Covering Non-Clustered Index (which isn’t here possible, because of the SELECT *), the query itself is over the Tipping Point, and you can see from the Execution Plan that SQL Server has chosen a Clustered Index Scan operator.

In the Execution Plan you can see at the end (or the beginning) a Filter operator, where SQL Server restricts the result set on the ProductID of 707. And the estimation is here 3.083 rows. It seems that we have here very accurate statistics. But the question is from where this estimation comes from. When you look at the histogram, you can see that we have multiple rows (up to 200 so-called Steps), which are describing the data distribution for the column ProductID. Every row in the histogram has the following columns:

• RANGE_HI_KEY
• RANGE_ROWS
• EQ_ROWS
• DISTINCT_RANGE_ROWS
• AVG_RANGE_ROWS

As you can see from the column RANGE_HI_KEY, there is one record with the ProductID 707. This means that we have for that specific ProductID an exact match in the histogram. In that case SQL Server is using the value from the column EQ_ROWS (Equality Rows) to produce the result of the Cardinality Estimation – in our case 3.083 rows. That’s the estimation that you can see in the Filter operator in the Execution Plan.

Let’s have a look on the following query.

-- The value "915" isn't available directly in the histogram, therefore SQL Server uses
-- the AVG_RANGE_ROWS to make the estimation.
-- There are 150 records between the values 910 and 916 with 4 distinct values (DISTINCT_RANGE_ROWS).
-- Therefore SQL Server estimates 150/4 = 37,5 rows for the Non-Clustered Index Seek.
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 915
GO

Here we are restricting on the ProductID of 915. But when you look into the histogram, you can see that there is no value for that specific ProductID. The histogram stores the values 910 and 916, and we have a gap between both values. This gap consists of 150 rows (RANGE_ROWS), excluding the values 910 and 916. And within these 150 rows we have 4 distinct values (DISTINCT_RANGE_ROWS). This means that our selectivity for a value between 910 and 916 is 37,5 (AVG_RANGE_ROWS = 150 / 4).

So in that case SQL Server estimates for the value 915 37,5 rows as you can see from the Execution Plan. In reality the Non-Clustered Index Seek operator returns 41 rows. Not a bad estimation.

As you can see from this example, SQL Server is also able to calculate a Cardinality Estimation when you have a non exact match in the histogram. For this reason you have the columns RANGE_ROWS and DISTINCT_RANGE_ROWS in the histogram. The histogram itself isn’t very hard to understand as you can see from this explanation. One very important thing about the histogram is the fact that SQL Server generates you the histogram only for the first index key column that is part of your index. For all subsequent index key columns SQL Server only stores the selectivity of the column in the Density Vector. Therefore you should always make sure that the column with the highest selectivity is the first one in your composite index key.

Density Vector

Let’s have a look on this mysterious density vector. When you have a look on the Non-Clustered Index IX_SalesOrderDetail_ProductID, the index is only defined on the column ProductID. But in every Non-Clustered Index SQL Server also has to stored the Clustered Key at least as a logical pointer in the leaf level of the index. When you have defined a Non-Unique Non-Clustered Index, the Clustered Key is also part of the navigation structure of the Non-Clustered Index. The Clustered Key on table SalesOrderDetail is a composite one, on the columns SalesOrderID and SalesOrderDetailID.

This means that our Non-Unique Non-Clustered Index consists in reality of the columns ProductID, SalesOrderID, and SalesOrderDetailID. Under the hoods you have a composite index key. This also means now that SQL Server has generated a density vector for the other columns, because only the first column (ProductID) is part of the histogram, that we have already seen in the earlier section. When you look at the output of the DBCC SHOW_STATISTICS command, the density vector is part of the second result set that is returned:

SQL Server stores here the selectivity, the density of the various column combinations.For example, we have an All Density Value for the column ProductID of 0,003759399. You can also calculate (and verify) this value at your own by dividing 1 by the number of distinct values in the column ProductID:

-- The "All Density" value for the column ProductID: 0,0037593984962406015
SELECT 1 / CAST(COUNT(DISTINCT ProductID) AS NUMERIC(18, 2)) FROM Sales.SalesOrderDetail
GO

The All Density Value for the column combinations ProductID, SalesOrderID and ProductID, SalesOrderID, SalesOrderDetailID is 8,242867858585359018109580685312e-6. You can again verify this, by dividing 1 by the number of distinct value in the columns ProductID, SalesOrderID, or ProductID, SalesOrderID, SalesOrderDetailID. In our case these values are unique (because they are part of the Clustered Key), so you calculate 1 / 121.317 = 8,242867858585359018109580685312e-6. The question is now, how SQL Server is using these density vector values for the cardinality estimation itself?

Let’s have a look on the following query:

-- SQL Server uses the reciprocal in a GROUP BY to make an estimation how
-- much rows are returned:
-- Estimation for the Stream Aggregate: 266
SELECT ProductID FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO

As you can see we are performing a GROUP BY on the column ProductID. In that case SQL Server is using the reciprocal of the density vector value of the column ProductID to estimate the number of rows for the Stream Aggregate operator: 1 / 0,0037593984962406015 = 266. When you have a look on the Execution Plan in the following picture, you can see the estimation of 266 rows for the Stream Aggregate operator.

When you are dealing with local variables in your T-SQL statements, SQL Server can’t sniff any parameter values anymore, and falls back to the density vector to perform the cardinality estimation. Let’s have a look on the following query.

-- SQL Server also uses the Density Vector when we are working with local variables
-- and equality predicates.
-- SQL Server estimates for the Non-Clustered Index Seek 456 records: 121317 * 0,003759 = 456
-- Every variable value gives us the same estimation.

-- Estimated: 456
-- Actual: 3083
DECLARE @i INT = 707

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @i

SQL Server estimates for the Filter operator 456 rows (121.317 * 0,0037593984962406015), and in reality we are returning only 44 rows.

When you are dealing with local variables in combination with inequality predicates, SQL Server isn’t using the density vector values anymore, and just assumes that 30% of the rows are returned.

-- When we are using an inequality predicate (">", "<") SQL Server assumes 30% for the
-- estimated number of rows.
-- Estimated: 36.395 (121.317/36.395 = 3,33)
-- Actual: 44
DECLARE @i INT = 719

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID > @i
GO

As you can see from the following Execution Plan, SQL Server always estimates 36.395 rows, because this is 30% of the rows from the table (121.317 * 0,30)

Summary

In this blog posting you have learned how SQL Server is using the underlying statistics objects to perform the cardinality estimation for our queries. As you have seen, a statistics object always has 2 very important parts: the histogram, and the density vector. Within the histogram SQL Server can estimate very easy, how many rows on average are returned for a specific query. Because the histogram is only created for the first column in a composite index key, SQL Server stores in addition in the density vector the selectivity of the other columns. And as you have seen these selectivity values are also used at various stages during the cardinality estimation.

Thanks for reading

-Klaus

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan, where you are getting week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.)

In todays blog posting I want to talk about myths & misconceptions about the various Transaction Isolation Levels that exists in SQL Server. I mainly want to talk about the following topics:

Transaction Isolation Levels – What?

NOLOCK never blocks!?

Read Committed doesn’t holds locks!?

Key Range Locks are specific to Serializable!?

So let’s get started by first laying out the foundation about Transaction Isolation Levels in SQL Server.

Transaction Isolation Levels – What?

Everytime when I’m on customer side, and troubleshooting crazy SQL Server problems (or performing SQL Server Health Checks), the root cause sometimes lies in the locking/blocking behavior of the database. When you have one bad query (maybe you are missing a very important index), the performance of your whole database can go down.

Have you ever tried starting a new transaction (without ever committing it) that acquires an exclusive lock the main table of your production database? Trust me: the performance and the throughput of your database will just die – in front of your eyes!!! So my Pro Tip to you: don’t try it

When we are talking about locking & blocking in any relational database, we also have to talk about the various Transaction Isolation Levels that the DBMS supports (in our case – SQL Server). SQL Server supports 2 kinds of concurrency models: the “old” pessimistic one, and the “new” optimistic one that was introduced with SQL Server 2005 – almost 9 years ago…

Today I want to concentrate on the “old” one. In the old pessimistic concurrency model, SQL Server supports 4 different Transaction Isolation Levels:

Read Uncommitted

Read Committed

Repeatable Read

Serializable

I don’t want to go into the deep internals about every Transaction Isolation Level, but I want to give you a brief overview what every Isolation Level is doing internally, because we need that information for the subsequent sections of this blog posting.

When we are looking on a high level view onto SQL Server, you have transactions that are reading data (SELECT queries), and you have transactions that are changing data (INSERT, UPDATE, DELETE, MERGE). Every time when you are reading data, SQL Server has to acquire Shared Locks (S). Everytime when you change data, SQL Server has to acquire Exclusive Locks (X). And both locks are incompatible to each other, means readers are blocking writers, and writers are blocking readers.

With the Transaction Isolation Level you can control now, how long a reader will hold its S locks. Writers will always acquire X locks, and you can’t influence them. By default SQL Server uses the Isolation Level Read Committed, which means SQL Server acquires a S lock, when you read a record, and and when the record was read, the S lock is released. When you are reading row by row, you are acquiring and releasing the needed S locks row by row.

When you don’t want that readers are acquiring S locks (which isn’t really recommended), you can use the Isolation Level Read Uncommitted. Read Uncommitted just means you are able to read dirty data – data that isn’t yet committed. It’s blazingly fast (no one else can block you), but on the other hand it’s very dangerous, because it’s uncommitted data. Just think about, what happens if the uncommitted transaction is aborded after you have read the data: you have data in your hand that doesn’t even logically existed in your database. Your hands are very dirty now. I’m seeing a lot of cases during my consulting engagements where people are using Read Uncommitted or the NOLOCK query hint to get rid of blocking situations in SQL Server. But that’s not the preferred way to handle blocking. And as you will see later, even NOLOCK can block…

In Read Committed you can have so-called Non Repeatable Reads, because someone else can change the data, when you are reading the data twice in your transaction. If you want to avoid Non Repeatable Reads, you can use the Isolation Level Repeatable Read. In Repeatable Read SQL Server holds the S locks until you end your transaction with a COMMIT or ROLLBACK. Means nobody else can make changes to your read data, and you are getting Repeatable Reads for your transaction.

In every discussed Isolation Level so far, you are also always able to get so-called Phantom Records – records that can appear and disappear in your result set. If you want to get rid of these phantom records, you have to use the Isolation Level Serializable, which is the most restrictive one. In Serializable SQL Server uses a so-called Key Range Locking to eliminate phantom records: you are locking complete ranges of data, so that no other concurrent transactions can insert other records to prevent phantom records.

As you can see from this description, the more restrictive your Isolation Level will be, the more it will hurts the concurrency of your database. So you have to choose the right Isolation Level very wisely. Running always in Read Committed doesn’t make sense, running always in Serializable doesn’t make sense. It depends, as usual

By now I have laid out the foundation about Transaction Isolation Levels in SQL Server, and now I will show you 3 different cases, where my descriptions from above will not match. In some specific cases SQL Server has to guarantee the correctness of your transactions by changing the Transaction Isolation Level for specific SQL statements under the hood – without any intervention from you. Let’s start…

NOLOCK never blocks!?

As I have described in the introduction, the NOLOCK query hint also prevents for a specific SQL statement that S locks are acquired during the reading of records. This will make your SQL statements very fast, because the SELECT query can’t be blocked by any other transaction. I’m also referring to NOLOCK as the Turbo-Booster in SQL Server.

But unfortunately NOLOCK can also block when you are dealing with concurrent DDL statements (Data Definition Language) like ALTER TABLE. Before we understand this behavior we need to have a more detailed look on DDL statements, and what happens internally in SQL Server, when we are running a simple SELECT query.

When you are changing a table with the ALTER TABLE DDL statement, SQL Server acquires a so-called Schema Modification Lock (Sch-M) on that table. When you are running now at the same time a simple SELECT query against the same table, SQL Server has to compile in the first step a physical execution plan. And during the compilation phase SQL Server needs a so-called Schema Stability Lock (Sch-S).

And both lock (Sch-M and Sch-S) are just incompabile to each other! This means that even a NOLOCK statement can block, because in the first step you have to compile an execution plan. So your NOLOCK statement blocks even before SQL Server knows the physical execution plan. Have you ever thought about that behavior when you have upgraded your database schema in production? Just think about it… Let’s demonstrate this behavior with a simple example. In the first step I’m creating a new table and inserting some records into it:

-- Create a new test table
CREATE TABLE TestTable
(
	Column1 INT,
	Column2 INT,
	Column3 INT
)
GO

-- Insert some test data
DECLARE @i INT = 0

WHILE (@i < 10000)
BEGIN
	INSERT INTO TestTable VALUES (@i, @i + 1, @i + 2)
	SET @i += 1
END
GO

And afterwards we are just starting a new transaction and and adding a new column to our table by executing a ALTER TABLE DDL statement:

-- Begin a new transaction and do some work
BEGIN TRANSACTION

-- Add a new column
-- DDL statements require a Sch-M lock on the objects that are modified.
-- In this case, the table "TestTable" gets a Sch-M lock (Schema modification lock)!
ALTER TABLE TestTable ADD Column4 INT

As you can see from the previous code, the transaction is still ongoing, and isn’t yet committed. So let’s open a different session in SQL Server Management Studio and execute the following SQL statement:

-- The statement is now blocking, even with the NOLOCK query hint!
-- SQL Server has to compile the query, and requests a Sch-S lock (Schema Stability lock).
-- This lock is incompatible with the Sch-M lock!
SELECT * FROM TestTable WITH (NOLOCK)
GO

As you will see immediately, the SQL statement will not return a result, because it’s blocked by the other active transaction – even with the NOLOCK query hint! You can further troubleshooting this blocking situation by querying the DMV sys.dm_tran_locks for both sessions. As you can see the Sch-M lock blocks the Sch-S lock.

Using the NOLOCK query hint or the Transaction Isolation Level Read Uncommitted is no guarantee that your SQL statement will complete immediately. In the past I have worked with a lot of customers who have struggled with that specific problem.

Read Committed doesn’t hold locks!?

As you have learned earlier in the introduction about the Read Committed Isolation Level, a S lock is only held during the reading phase of the record. This means that the lock is immediately released as soon as the record is read. So when you reading data from a table, you have at any point in time only one S lock – for the record that is currently processed. This description is only true when as long as your execution plans doesn’t has blocking operators – like a sort operator. When your execution plan has such a operator, it means that SQL Server has to create a copy of your data.

After the copy of the data is done, the original table/index data doesn’t needed to be retained anymore. But creating a copy of the data only scales and performs very well when you are dealing with a small amount of data. Imagine you have a table definition with a column of the VARCHAR(MAX) data type. In that case every row of the column can store up to 2 GB of data. Creating a copy of that data would not really scale, and blow your memory and TempDb away.

To avoid this scalability problem, SQL Server just holds the S locks until the end of your statement. Therefore it’s not possible that someone changes the data in the mean time (S lock blocks X lock), and SQL Server just references the original, stable, unchanged data. As a result, your transaction behaves like running in the Isolation Level Repeatable Read, which hurts the scalability of your database. Let’s create the following database schema to demonstrate this behavior:

-- Create a new table
CREATE TABLE TestTable
(
	ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	Col2 INT,
	Col3 VARCHAR(MAX)
)
GO

-- Insert some records into the table
INSERT INTO TestTable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi')
GO

-- Begin a new transaction, so that we are blocking some records in the table
BEGIN TRANSACTION

UPDATE TestTable SET Col2 = 1 WHERE ID = 3

As you can see from the script, I’m just creating a simple Clustered Table with 3 records with the Clustered Key values of 1, 2, and 3. In the next step I’m starting now a new transaction, and we are locking the 3rd record in the Clustered Index. This is now just the setup of this demo. In a separate session we try now to read from the table – of course the SELECT statement will block:

-- This statement only acquires a key lock on the current record
SELECT Col3 FROM TestTable
GO

When we look into the DMV sys.dm_tran_locks you can see very nicely that the SELECT statement waits for the 3rd lock on the Clustered Key value of 3. This is just the traditional behavior of the Read Commited Isolation Level, because we have no blocking operator in our execution plan. But as soon as we are introducing a blocking operator (and reading a LOB data type), things will change:

-- This statement only acquires a key lock on the current record
SELECT Col3 FROM TestTable
ORDER BY Col2
GO

As you can see, we are using now the ORDER BY clause, which gives us a sort operator in the execution plan. Of course, the SELECT statement will block again. But when we look into the DMV sys.dm_tran_locks, you will see a completely different behavior of the Read Committed Isolation Level: SQL Server has now acquired the S locks on the first 2 rows (Clustered Key values 1, and 2), but hasn’t released them anymore! SQL Server holds these locks until our SELECT statement completes to prevent concurrent changes to the underlying data.

Effectively our SELECT statement is running in the Isolation Level REPEATABLE READ. Just think about that side-effect when you are designing your next table schema, and you want to include LOB data types in one of your main transactional tables…

Key Range Locks are specific to Serializable!?

A few weeks ago I have worked with a customer (during a SQL Server Health Check) where they have encountered Key Range Locks in the default Isolation Level Read Committed. As you know from the beginning of this blog posting, SQL Server only uses Key Range Locks in the Isolation Level Serializable. So the question is from where these Key Range Locks were coming from.

When we have further analyzed the database schema, we have found tables with foreign key constraints where Cascading Deletes were enabled. As soon as you have Cascading Deletes enabled for a foreign key, SQL Server uses Key Range Locks at the child table, when you delete records from the parent table. This makes sense, because the Key Range Locks are preventing the insertion of new records during the Cascading Delete operation. And therefore the referential integrity of the tables are retained. Let’s have a look on a more concrete example. Let’s create in the first step 2 tables and define a foreign key between both tables where Cascading Deletes are enabled.

-- Create a new parent table
CREATE TABLE Parent
(
	Parent1 INT PRIMARY KEY NOT NULL,
	Parent2 INT NOT NULL
)
GO

-- Create a new child table
CREATE TABLE Child
(
	Child1 INT PRIMARY KEY NOT NULL,
	Child2 INT NOT NULL,
	-- The following column will contain a Foreign Key constraint
	Parent1 INT NOT NULL
)
GO

-- Create a foreign key constraint between both tables,
-- and enable Cascading Deletes on it
ALTER TABLE Child
ADD CONSTRAINT FK_Child_Parent
FOREIGN KEY (Parent1) REFERENCES Parent(Parent1)
ON DELETE CASCADE
GO

-- Insert some test data
INSERT INTO Parent VALUES (1, 1), (2, 2), (3, 3)
INSERT INTO Child VALUES (1, 1, 1), (2, 2, 1), (3, 3, 1)
GO

When you now begin a new transaction, and you delete delete the record with the value of 1 from the parent table, SQL Server also has to delete the corresponding rows from the child table (3 rows in our case), because of the Cascading Delete:

-- Start a new transaction and analyze the acquired locks
BEGIN TRANSACTION

-- This statement deletes the record from the parent table,
-- and the 3 records from the child table
DELETE FROM Parent
WHERE Parent1 = 1

-- SQL Server uses 3 RangeX-X locks, even with the default
-- Isolation Level of Read Committed
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

COMMIT
GO

When you look during that transaction into the DMV sys.dm_tran_locks, you can see that your session has acquired 3 RangeX-X locks – Key Range Locks! These locks are needed by SQL Server to prevent the insertion of new records during the delete operation. As you can see SQL Server has transparently promoted your Isolation Level to Serializable to guarantee the correctness of your transactions.

Summary

As you have seen in this blog posting, don’t take anything as granted in SQL Server. I’m always asking people if they know in detail the locking behavior of the various Isolation Levels in SQL Server. If they are answering with yes, I’m just confronting them with the various phenomena we have seen in this blog posting.

The most important outcome from this is, that SQL Server is able to promote the Isolation Level for a given SQL statement. So don’t think that SQL Server is always running your queries in the Isolation Level that you have set.

Thanks for reading

-Klaus