Why Are Transactions Blocked All Of A Sudden?

Have you ever had a query that runs perfectly fine one day and the then all of a sudden starts getting bombarded with blocking transactions?  Believe it or not this is not than uncommon an occurrence and more interestingly can occur when no changes occur in the schema at all!  Unbeknownst to most, you are susceptible to an imaginary data distribution tipping point that can go south at any point in time, if your application creates a specific type of workload.   Let’s dig deeper to find out what causes this problem.

I will start off by creating some sample data.

USE [tempdb]
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
BEGIN
    DROP TABLE dbo.[TestData];
END
GO

CREATE TABLE dbo.TestData(
RowNum INT PRIMARY KEY CLUSTERED,
SomeBit INT,
SomeCode CHAR(2)
);
GO

INSERT INTO dbo.TestData
SELECT TOP 5000 
    ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
    CASE WHEN ROW_NUMBER() OVER (ORDER BY t1.NAME) %5 = 0 THEN 1 ELSE 0 END AS SomeBit, 
    'A' AS SomeCode
FROM 
    Master.dbo.SysColumns t1,
    Master.dbo.SysColumns t2
GO

Nothing new here, just a table with some data.  Now I will begin a transaction and run a simple UPDATE statement.

BEGIN TRANSACTION

UPDATE dbo.TestData
SET SomeCode = 'B'
WHERE somebit = 0

Now run a simple select statement against the table in a new query window.

SELECT RowNum FROM dbo.TestData WHERE RowNum = 1000

/*
RowNum
-----------
1000
*/

The query returned a resultset, just as we thought it would.  What I wanted to show here is that we currently do not have a blocking problem and users can still access the rows that do not have a SomeBit value of 0.   SQL Server will try to take the lowest or most granular lock possible when satisfying a query, such that other queries can still do what they need to.  Obviously there are limitations to this and SQL Server reacts differently based on system responsiveness and pressures.    You can verify that you cannot access a row with a SomeBit value of 0 by changing the predicate to a number that is not divisible by 5.

Okay…. big deal…. you probably already know this, but lets suppose that your manager tells you to migrate data from an existing system into this table.    The flat file has a measly 3000 rows is it, so its overall impact should really have no implications on our system right???? Let’s find out.  Please note that this problem can manifest itself by hitting a tipping point of data also… Meaning it does not take a huge influx of data to cause this problem, and this is why this problem can appear seemingly out of nowhere.

I will load the data with the same insert statement to mimic our data migration.

INSERT INTO dbo.TestData
SELECT TOP 3000 
    CASE WHEN ROW_NUMBER() OVER (ORDER BY t1.NAME) %5 = 0 THEN 1 ELSE 0 END AS SomeBit, 
    'A' AS SomeCode
FROM 
    Master.dbo.SysColumns t1,
    Master.dbo.SysColumns t2
GO

Now let’s run our same update transaction again.

UPDATE dbo.TestData
SET SomeCode = 'B'
WHERE somebit = 0

Now run the query below in a new window.

SELECT RowNum FROM dbo.TestData WHERE RowNum = 1000

What you should see is the query is now blocked.  Keep in mind that nothing changed on this server, except new data was inserted into the table.  Any ideas why this problem is now occurring?  If you haven’t figured it out yet, this problem is caused by lock escalation, http://msdn.microsoft.com/en-us/library/ms184286.aspx.  When SQL Server meets certain thresholds or memory pressure exists, SQL Server will escalate locks.  Lock escalation unfortunately goes from very granular locks to not so granular locks.  Lock escalation will go straight to a table lock from a rid/key or page lock.  What does this mean?  It means that SQL Server can save memory by acquiring a less granular lock, oppose to a lot of granular locks.  You can look at the transaction locks for each of the UPDATE statements to verify lock escalation is occurring.

Note: I removed intent locks as those locks from the resultset.

SELECT * 
FROM sys.[dm_tran_locks] 
WHERE [request_session_id] = xx
AND [request_mode] = 'X'
AND [request_mode] NOT LIKE 'I%'

 

Initial Update Query

 

Lock Escalated Update Query

If you run a lot of big DML transactions in your environment and still require concurrency, you may want to pay careful attention to lock escalation; otherwise, you may experience an abnormally large number of blocks.  While lock escalation  is great in most cases, in others it is less than ideal.

Here are the thresholds as described by BOL, http://msdn.microsoft.com/en-us/library/ms184286.aspx

Lock Escalation Thresholds

---

Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

• A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.

• A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.

• The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.

Now that we have identified the problem, what can we do to fix it?  There are a number of options that can be used  to solve this problem.  One solution is to use the nolock hint in your query or the read uncommited isolation level.  This particular solution is not recommend for all OLTP environments and should only be implemented with careful consideration.  The nolock hint and the read uncommitted isolation level can return inconsistent data.  If and only if this is okay, should you consider this as a solution.  Another solution is to use the read committed snapshot isolation level or the snapshot isolation level.  Both of these solutions require tempdb overhead, but do return transactional consistent data.  You can read more about these isolation levels here, http://msdn.microsoft.com/en-us/library/ms189122.aspx.  The other approach is to remove lock escalation.  You can remove lock escalation at the instance level (trace flags 1211 and 1224) or at the table level in SQL Server 2008, using the ALTER TABLE statement.  Obviously, removing lock escalation should be carefully thought out and tested.  For more information on these trace flags, please visit the storage engine’s blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx.

There you have it. I have shown you how simply adding data to your table can literally make a fast application a blocking nightmare overnight.  Before you go off and start adding nolock hints or changing your isolation level, please understand that you should only take these steps if you are experiencing this problem.  The reality is an OLTP system should not be holding onto more than 5000 (a lock escalation tipping point) locks because transactions should be short and efficient.  If you are experiencing this problem, your database is probably OLAP, you are missing indexes, or you queries are not typical OLTP transactions.  For example, you probably have large DML transactions and users trying to query the table concurrently.  

Until next time happy coding.

Optimizing SQL Server Joins - Part 2

In my last post, I talked about covering the query and the join expression, with a single index, http://jahaines.blogspot.com/2010/01/optimizing-sql-server-joins.html. Covering the join expression allows the optimizer to seek the joining column and has the benefit of removing the very costly Key/RID lookup. In this post, I will be talking about optimizing join order.

Let’s start with a very simplistic definition of join order. Join order is the order in which the optimizer processes tables involved in the join clause. When tables are processed, the tables are placed in either the top or bottom input, which dictates how the data is searched and how many rows are affected. There is plenty of misinformation out there stating that the order you specify your joins in can directly impact query performance. While this is partially true, it is more the exception than the rule. This misinformation came about because the optimizer is a cost based optimizer. The optimizer has to determine the best plan in the shortest amount of time. The optimizer does not have all the time in the world to test each and every join order or permutation. Because the optimizer cannot evaluate every possible permutation (in some cases), it is possible for the optimizer to miss a more optimal join order, but this would only happen when a query has an exuberant number of joins. Like I said before this is not a regular occurrence. In almost all cases, the join order is of negligible concern. Let’s see this in action.

First I will create my sample tables and load them with data.

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.Customers') IS NOT NULL
BEGIN
    DROP TABLE dbo.Customers;
END
GO

CREATE TABLE dbo.Customers(
CustId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
FName VARCHAR(50),
LName VARCHAR(50)
);
GO

;WITH 
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  
INSERT dbo.Customers(FName,LName)
SELECT 
    'FName' + CAST(N AS VARCHAR(10)),
    'LName' + CAST(N AS VARCHAR(10))    
FROM Nums
WHERE N<=1000;
GO

IF OBJECT_ID('tempdb.dbo.Orders') IS NOT NULL
BEGIN
    DROP TABLE dbo.Orders;
END
GO

CREATE TABLE dbo.Orders(
OrderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CustId INT
);
GO

;WITH 
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  
INSERT dbo.Orders(CustId)
SELECT 
     ABS(CHECKSUM(NEWID())%250+65)   
FROM Nums
WHERE N<=100;
GO

CREATE NONCLUSTERED INDEX ncl_idx_CustId ON dbo.Orders(CustId);
GO

First and foremost, I really do not recommend using HINTS like FOREPLAN, Plan Guides, or OPTION(FORCE ORDER), unless it is a last resort. In most cases, performance problems can be attributed to non-scalable code, and/or missing indexes; therefore, the first step in solving performance problems is to optimize code. In this post, I will be using FORCEPLAN to illustrate how join order can affect performance. I will start by running a set of simple queries. One query will not use FORCEPLAN and the other will use FORCEPLAN. FORCEPLAN makes the optimizer maintain the JOIN order specified. One of the biggest drawbacks of FORCEPLAN is that a nested loop join will likely be used, which may or may not be efficient for your query plan. The optimizer can still use other types of joins if other hints are used, or the optimizer has to use another join to satisfy the query. You can find more information on FORCEPLAN here, http://msdn.microsoft.com/en-us/library/ms188344.aspx. SQL Server 2005 has more force options available that do not have the same join type limitation, such as OPTION(FORCE ORDER), Plan hints, and plan guides; however, these can be just as bad for performance, if used excessively or inappropriately.

 

SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;
GO

SELECT o.CustId,c.FName,c.LName
FROM dbo.Orders o
INNER JOIN dbo.Customers c
    ON c.CustId = o.CustId
WHERE c.LName = 'LName151'

SET STATISTICS IO OFF;
SET STATISTICS PROFILE OFF;
GO

/*
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT o.CustId,c.FName,c.LName  FROM dbo.Orders o  INNER JOIN dbo.Customers c   ON c.CustId = o.CustId  WHERE   c.LName = 'LName151'
  |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[CustId]))
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Customers].[PK__Customers__7E6CC920] AS [c]), WHERE:([tempdb].[dbo].[Customers].[LName] as [c].[LName]='LName151'))
       |--Index Seek(OBJECT:([tempdb].[dbo].[Orders].[ncl_idx_CustId] AS [o]), SEEK:([o].[CustId]=[tempdb].[dbo].[Customers].[CustId] as [c].[CustId]) ORDERED FORWARD)
*/
GO

 

As you can see, the Customers table is used as the OUTER table and Orders table is the INNER table, which supports my saying the optimizer is free to rearrange the predicate. For each row in the outer table the INNER table has to be scanned. If you use, FORCEPLAN the optimizer will make the OUTER TABLE Orders and the INNER table Customers. The first problem I encounter is the Orders table does not have a where clause, so the index seek on Orders will go away, but the optimizer will be able to seek Customers on CustId. Additionally, I have made the OUTER table (Orders) smaller, which means the optimizer will have less passes on the INNER table (Customers); however, the optimizer will have to scan/seek through a lot more data to return the results. Using FORCEPLAN can really degrade performance and in this case results more than 30 times more reads.

 

SET FORCEPLAN ON;
SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;
GO

SELECT o.CustId,c.FName,c.LName
FROM dbo.Orders o
INNER JOIN dbo.Customers c
    ON c.CustId = o.CustId
WHERE c.LName = 'LName151'

SET FORCEPLAN OFF;
SET STATISTICS IO OFF;
SET STATISTICS PROFILE OFF;
GO

/*
Table 'Customers'. Scan count 0, logical reads 200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SELECT o.CustId,c.FName,c.LName  FROM dbo.Orders o  INNER JOIN dbo.Customers c   ON c.CustId = o.CustId  WHERE c.LName = 'LName151'
  |--Nested Loops(Inner Join, OUTER REFERENCES:([o].[CustId]))
       |--Index Scan(OBJECT:([tempdb].[dbo].[Orders].[ncl_idx_CustId] AS [o]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Customers].[PK__Customers__7E6CC920] AS [c]), SEEK:([c].[CustId]=[tempdb].[dbo].[Orders].[CustId] as [o].[CustId]),  WHERE:([tempdb].[dbo].[Customers].[LName] as [c].[LName]='LName151') ORDERED FORWARD)
*/

As you can see, adding or misusing query hints can really degrade query performance. In my opinion, the worst part about using hints is you prohibit the optimizer from logically deducing the best way to solve a query. Developers often neglect to see the long term impact of using hints, as data distribution, indexes, and business requirements change over time. One of the best thing you can do is leave the optimizer alone and let it go to work. A lot of times developers think they know better than the optimizer or that they can trick it; however, this almost always ends in bad performance. Query hints are quite often band-aids to much larger problems; however, under certain circumstances there can be a benefit in using query hints and this is why they exist. For more information on how to help persuade the optimizer to do what you want, you can use thing following link, http://msdn.microsoft.com/en-us/library/cc917694.aspx. There are a more options in SQL 2005+ that give developers and DBAs more control over query execute plans; however, I still recommend that you solve performance problems by optimizing code and/or adding indexes. Only when you have evaluated all other options, should you consider using hints or guides. All-in-all, the most typical way to optimize join order is to leave it alone.

Until next time happy coding.

Optimizing SQL Server Joins

Today a colleague asked me a question about performance optimization regarding joins.  I gave him a pretty detailed answer over the phone, but I do not think I really made my message stick, which is the reason for this post.  The first and most important thing to remember is the optimizer can only choose one physical operator per table.  The only exception to this is when the optimizer decides to use index intersection, http://www.sqlmag.com/Articles/ArticleID/94116/94116.html?Ad=1 (SQL 2005+).  Index intersection occurs when the optimizer creates its own join and uses two indexes to satisfy the predicate.   While index intersection is still aligned with what I said, index intersection does add an additional table reference to the query plan, which should be noted.  If the same table is referenced multiple times, the execution plan will have more than than one physical operator at varying stages of the execution plan.  When tables are joined together SQL Server creates a constrained Cartesian product, which is nothing more than matching rows based on a given join expression. To do this SQL Server uses a join (Merge, Hash or Nested Loop)  and creates an INNER (Bottom) and OUTER (Top) set.  I use the word set here because the INNER does not have to be a table.  INNER can actually be a constrained Cartesian product. Essentially, the optimizer chooses a base (OUTER) set and will then filter the INNER set based on the results from the outer set.  The optimizer then continues to filter the set for each join, until the query is satisfied.  It is important to know that the optimizer is under no obligation to join tables in the order you have specified.  The optimizer is free to rearrange joins as it sees fit.  Let’s start by creating our sample objects.

USE [tempdb]
GO

SET NOCOUNT ON;
GO

IF OBJECT_ID('tempdb.dbo.State') IS NOT NULL
BEGIN
    DROP TABLE dbo.[State];
END
GO

CREATE TABLE dbo.[State](
State_Cd CHAR(2),
Descr VARCHAR(150)
);
GO

INSERT INTO dbo.[State] ([State_Cd],[Descr]) VALUES ('AL','Alabama');
INSERT INTO dbo.[State] ([State_Cd],[Descr]) VALUES ('LA','Louisiana');
GO

IF OBJECT_ID('tempdb.dbo.City') IS NOT NULL
BEGIN
    DROP TABLE dbo.[City];
END
GO

CREATE TABLE dbo.[City](
State_Cd CHAR(2),
City_Cd VARCHAR(100)
);
GO

INSERT INTO dbo.[City] ([State_Cd],[City_Cd]) VALUES ('AL','Mobile');
INSERT INTO dbo.[City] ([State_Cd],[City_Cd]) VALUES ('LA','New Orleans');
INSERT INTO dbo.[City] ([State_Cd],[City_Cd]) VALUES ('LA','Luling');
GO

IF OBJECT_ID('tempdb.dbo.Zip') IS NOT NULL
BEGIN
    DROP TABLE dbo.[Zip];
END
GO

CREATE TABLE dbo.Zip(
City_Cd VARCHAR(100),
Zip_Cd VARCHAR(10),
);
GO

INSERT INTO dbo.Zip ([City_Cd],[Zip_Cd]) VALUES ('Mobile','36601');
INSERT INTO dbo.Zip ([City_Cd],[Zip_Cd]) VALUES ('New Orleans','70121');
INSERT INTO dbo.Zip ([City_Cd],[Zip_Cd]) VALUES ('Luling','70070');
GO

Now that we have our tables, lets execute a very simplistic query.

Note:   There are no indexes present on any tables, so all queries will result in a  table scan.

--Qry 1
SELECT s.[Descr]
FROM dbo.[State] s

Now let’s add a second table to the query to see what happens.  What we should see is a nested loop join and two table scans. 

--Qry 2
SELECT s.[Descr],c.City_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c    ON s.[State_Cd] = c.[State_Cd]

The screenshot above shows the State table has been chosen as the base query.  As you can see, each table is represented by a single Table scan operator.   Let’s add one more table to the mix to see how the optimizer will react.

--Qry 3
SELECT s.[Descr],c.City_Cd,z.Zip_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c    ON s.[State_Cd] = c.[State_Cd]
INNER JOIN dbo.[Zip] z ON z.[City_Cd] = c.[City_Cd]

 

The optimizer decided to make the first OUTER table State and then decided to make the INNER  a constrained Cartesian product of City and Zip .   This is especially important when you are optimizing code because it is a lot easier to fix problems when you know how indexes and joins really work.  For this example, I see that we are scanning Zip and City, so that tells me that I am missing an index on these two tables. Remember the general rule of thumb is to have indexes on all column participating in the join expression. I will start the optimization process by adding an index to the Zip table, on the City_Cd column.

CREATE NONCLUSTERED INDEX ncl_idx_Zip_City_Cd ON dbo.[Zip](City_Cd);

Wow, look at how the query plan changed.  We now see our index seek on Zip, but we now see a RID lookup.  Lookups can become performance bottlenecks really quickly and can sometimes cause blocking or even worse dead locks.  Key Lookups can cause blocking and deadlocks because the optimizer has to take a shared lock on the Clustered Index to get the data that is missing from the nonclustered  index and this causes a problem when an insert/update/delete occurs because it requires an exclusive lock, on the Clustered Index.  In our example, We have an index on Zip which only contains the column City_Cd; however, we are selecting Zip_Cd.  Because Zip_Cd does not exist in the index, the optimizer has to go back to the heap to get the remaining column data.  To solve this problem we need to add Zip_Cd to the index.  I will be adding the Zip_Cd column via the INCLUDE clause.  I chose INCLUDE because I am not using this column in the predicate and using the INCLUDE clause keeps the index relatively small because the value is only stored at the leaf level of the nonclustered index.  You may want to add the column to the index key if you use the column in a lot of predicates because SQL Server maintains statistics on index key columns, but not columns in the INCLUDE clause.  Statistics are used used by SQL Server to estimate cardinality.  Better cardinality estimates allow the optimizer to make better decisions about what operators are best for the given query.  Essentially, better cardinality estimates can be the difference between a scan and a seek. You have to weigh the cost of index maintenance and performance when deciding which method to choose.  For more information regarding INCLUDE please visit this link, http://msdn.microsoft.com/en-us/library/ms190806.aspx.

Note: If you are not sure what columns need to be added to the index you can hover your mouse over the lookup and look at the output list.  The index that you need to add the columns too will always be the seek operator to the right of the lookup operator.

IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'ncl_idx_Zip_City_Cd')
BEGIN
    DROP INDEX ncl_idx_Zip_City_Cd ON dbo.Zip;
END
GO

CREATE NONCLUSTERED INDEX ncl_idx_Zip_City_Cd ON dbo.[Zip](City_Cd)INCLUDE([Zip_Cd]);
GO

Now Execute the query again.

--Qry 3
SELECT s.[Descr],c.City_Cd,z.Zip_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c ON s.[State_Cd] = c.[State_Cd]
INNER JOIN dbo.[Zip] z ON z.[City_Cd] = c.[City_Cd]

Now that is a little better but lets make this query even faster. Next, I will add an index to the City table, making sure to include City_Cd.

CREATE NONCLUSTERED INDEX ncl_idx_City_State_Cd ON dbo.City(State_Cd)INCLUDE([City_Cd]);
GO

Now execute the query again.

--Qry 3
SELECT s.[Descr],c.City_Cd,z.Zip_Cd
FROM dbo.[State] s
INNER JOIN dbo.[City] c ON s.[State_Cd] = c.[State_Cd]
INNER JOIN dbo.[Zip] z ON z.[City_Cd] = c.[City_Cd]

How about them apples?  By adding the proper indexes in place we can now get index seeks across the board.  It is important to note that we cannot seek State because we have no predicate filter on any columns in the state table, so the optimizer has to scan.  When you are trying to optimize queries the first place to look is the execution plan.  If you see a lot of scans, you have a lot of optimization potential.  Remember that you want to make sure all columns in the select, the join and the where clause are present in your index.  Please do not tell your boss that you need to create indexes to cover every query in your environment. You will not be able to fully cover every query in your environment, but the important thing is to optimize and cover the queries that are really expensive or causing problems. Who knows you maybe able to cover multiple queries by creating or modifying a single index.

That’s it for now.  I hope I have cleared up how the optimizer handles joins and given you greater insight on how to optimize joins. Until next time happy coding.