ROWLOCK Hint and Slow Performance in SQL Server

A ROWLOCK hint can make a query slower, because every row it reads needs its own lock. The hint looks like a way to lock less. For a scan it does the opposite.

What the Hint Does

SQL Server can lock a row, a page or a whole table. It normally chooses for you. The ROWLOCK hint tells it to use row locks, even where a page lock would do. It doesn’t make a query lock fewer rows. It changes the size of each lock, and that multiplies the number of locks.

The test needs a table big enough to show it. The setup script creates 200,000 rows. There is no index on CustomerID, so a search on that column reads every row.

SET NOCOUNT ON;
IF DB_ID(N'RowLockHintDemo') IS NULL CREATE DATABASE RowLockHintDemo;
GO
USE RowLockHintDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (
    OrderID    int           NOT NULL PRIMARY KEY,
    CustomerID int           NOT NULL,
    Total      decimal(10,2) NOT NULL,
    Note       char(100)     NOT NULL DEFAULT 'x'
);
INSERT INTO dbo.Orders (OrderID, CustomerID, Total)
SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), ABS(CHECKSUM(NEWID())) % 1000, 10
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c;

A Procedure That Counts Locks

To see locks you need to count them as they are taken. The procedure below starts an Extended Events session for the current connection. The session counts every lock_acquired event by resource type. The procedure runs your query and returns the key and page counts. It removes the session afterwards. The query must return one number.

CREATE OR ALTER PROCEDURE dbo.CountLocks @Query nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'RowLockHintDemoSession')
        DROP EVENT SESSION RowLockHintDemoSession ON SERVER;
    DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION RowLockHintDemoSession ON SERVER
        ADD EVENT sqlserver.lock_acquired (WHERE sqlserver.session_id = ' + CONVERT(nvarchar(10), @@SPID)
        + N' AND database_id = ' + CONVERT(nvarchar(10), DB_ID()) + N')
        ADD TARGET package0.histogram (SET filtering_event_name = N''sqlserver.lock_acquired'', source = N''resource_type'', source_type = 0);';
    EXEC (@sql);
    ALTER EVENT SESSION RowLockHintDemoSession ON SERVER STATE = START;
    DECLARE @result TABLE (v decimal(18,2));
    INSERT @result EXEC (@Query);
    SELECT t.ResourceType, ISNULL(h.LocksAcquired, 0) AS LocksAcquired
    FROM (VALUES (N'KEY'), (N'PAGE')) AS t(ResourceType)
    LEFT JOIN (SELECT m.map_value AS ResourceType, s.n.value('@count', 'int') AS LocksAcquired
               FROM (SELECT CAST(tg.target_data AS xml) AS x
                     FROM sys.dm_xe_session_targets AS tg
                     JOIN sys.dm_xe_sessions AS se ON se.address = tg.event_session_address
                     WHERE se.name = N'RowLockHintDemoSession') AS d
               CROSS APPLY d.x.nodes('HistogramTarget/Slot') AS s(n)
               JOIN sys.dm_xe_map_values AS m ON m.name = N'lock_resource_type' AND m.map_key = s.n.value('(value)[1]', 'int')
              ) AS h ON h.ResourceType = t.ResourceType
    ORDER BY ISNULL(h.LocksAcquired, 0) DESC;
    ALTER EVENT SESSION RowLockHintDemoSession ON SERVER STATE = STOP;
    DROP EVENT SESSION RowLockHintDemoSession ON SERVER;
END;

Change Every Row First

The effect depends on what else is happening on the server, and the busy case comes later. This first run is on an idle server, with one query window and no other open transaction. The update touches every row, so every page holds a recent change.

UPDATE dbo.Orders SET Total = Total + 1;

Scan Without the Hint on an Idle Server

The first call runs the scan with no hint. SQL Server picks the lock size.

EXEC dbo.CountLocks N'SELECT SUM(Total) FROM dbo.Orders WHERE CustomerID < 100;';

SQL Server chose page locks. The table fills about 3,100 pages, so one lock per page covers all 200,000 rows.

Scan With the ROWLOCK Hint on an Idle Server

The second call adds the hint.

EXEC dbo.CountLocks N'SELECT SUM(Total) FROM dbo.Orders WITH (ROWLOCK) WHERE CustomerID < 100;';

On an idle server the hint cost almost nothing. The scan took a handful of key locks, not one per row. SQL Server can skip row locks on a page that holds no uncommitted change. It skips them with the hint too. With no other open transaction, it can tell. The counts differ by a few between runs.

The time shows the same. This script runs each version of the scan 20 times and reports the total in milliseconds.

DECLARE @i int, @t0 datetime2, @v decimal(18,2);
DECLARE @r TABLE (Version varchar(20), TotalMs int);
SET @i = 1; SET @t0 = SYSDATETIME();
WHILE @i <= 20 BEGIN SELECT @v = SUM(Total) FROM dbo.Orders WHERE CustomerID < 100; SET @i += 1; END;
INSERT @r VALUES ('Default', DATEDIFF(MILLISECOND, @t0, SYSDATETIME()));
SET @i = 1; SET @t0 = SYSDATETIME();
WHILE @i <= 20 BEGIN SELECT @v = SUM(Total) FROM dbo.Orders WITH (ROWLOCK) WHERE CustomerID < 100; SET @i += 1; END;
INSERT @r VALUES ('ROWLOCK', DATEDIFF(MILLISECOND, @t0, SYSDATETIME()));
SELECT Version, TotalMs FROM @r;

The gap is small. A test like this one, on a quiet development server, makes the hint look harmless. That is the trap.

The Same Scan on a Busy Server

Production servers always have open transactions. The next test needs a second query window. Window 2 starts a transaction and leaves it open. It must start before the update in window 1. A page changed after an older transaction began could hold that transaction’s change. So SQL Server can’t skip the row locks.

Run this in window 2, and don’t close it yet. It isn’t a block to run in the same window as the rest.

USE RowLockHintDemo;
DROP TABLE IF EXISTS dbo.SideNote;
CREATE TABLE dbo.SideNote (NoteID int NOT NULL PRIMARY KEY, Val int NOT NULL);
INSERT INTO dbo.SideNote (NoteID, Val) VALUES (1, 1);
BEGIN TRANSACTION;
UPDATE dbo.SideNote SET Val = 2 WHERE NoteID = 1;

Now run this in window 1. It repeats the update, both counts and the timing. It needs window 2’s open transaction, so the demo run doesn’t repeat it.

UPDATE dbo.Orders SET Total = Total + 1;
GO
EXEC dbo.CountLocks N'SELECT SUM(Total) FROM dbo.Orders WHERE CustomerID < 100;';
EXEC dbo.CountLocks N'SELECT SUM(Total) FROM dbo.Orders WITH (ROWLOCK) WHERE CustomerID < 100;';
GO
DECLARE @i int, @t0 datetime2, @v decimal(18,2);
DECLARE @r TABLE (Version varchar(20), TotalMs int);
SET @i = 1; SET @t0 = SYSDATETIME();
WHILE @i <= 20 BEGIN SELECT @v = SUM(Total) FROM dbo.Orders WHERE CustomerID < 100; SET @i += 1; END;
INSERT @r VALUES ('Default', DATEDIFF(MILLISECOND, @t0, SYSDATETIME()));
SET @i = 1; SET @t0 = SYSDATETIME();
WHILE @i <= 20 BEGIN SELECT @v = SUM(Total) FROM dbo.Orders WITH (ROWLOCK) WHERE CustomerID < 100; SET @i += 1; END;
INSERT @r VALUES ('ROWLOCK', DATEDIFF(MILLISECOND, @t0, SYSDATETIME()));
SELECT Version, TotalMs FROM @r;

Busy server, scan without the hint:

Busy server, scan with the hint:

With an older transaction open, the hinted scan took one key lock for every row in the table. Rows that don’t match the filter still need a lock while they are read. The page locks that remain are the intent locks that go with row locks. The time followed the lock count, at about 5.6 times as long. Then end the transaction in window 2 and close it.

ROLLBACK;

What This Means for the ROWLOCK Hint

The hint can force one lock per row. It does so whenever SQL Server can’t skip row locks, which is normal on a busy server. On an idle database the cost can disappear, so a quiet test hides the problem. Measure on a copy of the table that is under real write load, or with an open transaction as above.

Seeks behave the same way. With window 2’s transaction open, a seek on 3,000 rows took about 3,000 key locks, hint or not. On the idle server it took 5 and 1 key locks. The hint changed nothing in either case.

EXEC dbo.CountLocks N'SELECT SUM(Total) FROM dbo.Orders WHERE OrderID <= 3000;';
EXEC dbo.CountLocks N'SELECT SUM(Total) FROM dbo.Orders WITH (ROWLOCK) WHERE OrderID <= 3000;';

The hint isn’t a way to prevent lock escalation either. SQL Server can still turn many row locks into one table lock. To stop that, use the table’s LOCK_ESCALATION option.

ROWLOCK and UPDLOCK Are Different

One question keeps coming up: how does ROWLOCK differ from UPDLOCK? ROWLOCK sets the size of a lock. UPDLOCK sets its mode. A read with UPDLOCK takes an update lock. Two sessions then can’t both read a row and try to change it.

BEGIN TRANSACTION;
SELECT Total FROM dbo.Orders WITH (UPDLOCK) WHERE OrderID = 10;
SELECT resource_type, request_mode, COUNT(*) AS Locks
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID()
  AND resource_type IN (N'KEY', N'PAGE', N'OBJECT')
GROUP BY resource_type, request_mode
ORDER BY resource_type;
ROLLBACK;

One key lock in U mode, with intent locks above it. The size was chosen by SQL Server, and the mode came from the hint.

What to Remember

You could argue that row locks always reduce blocking. They can, when many sessions update neighboring rows on a busy page. Test that case with a real workload before you add the hint. For reads and scans, remove the hint and let SQL Server choose. If you must keep it, measure the lock counts on a busy copy of the table, not a quiet one. A quiet table hides the cost, and production isn’t quiet.

Run the cleanup script when you finish.

USE master;
GO
IF DB_ID(N'RowLockHintDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RowLockHintDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RowLockHintDemo;
END;

A lock hint is not a speed setting, it is a promise about how much you will lock.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.