SQL SERVER – Observation: ROWLOCK Hint and Slow Performance

During my recent Comprehensive Database Performance Health Check Developer showed me a slow query which used ROWLOCK hint. The common understanding is that with the help of ROWLOCK hint SQL Server only locks a single row which eventually leads to performance but in their case, it was actually very much opposite. Let us see this scenario with a simple example.

In this example, I will be using a WideWorldImporters sample database. Here is a blog post which discusses how you can install this sample database.

Test 1: Without RowLock Hint

First, let us run a query without any hint and note the server execution time.

USE WideWorldImporters
GO
SET STATISTICS IO, TIME ON
SELECT *
FROM [Sales].[Orders] o
INNER JOIN [Sales].[OrderLines] ol ON ol.OrderID = o.OrderID
INNER JOIN [Sales].[Invoices] i ON i.OrderID = o.OrderID
WHERE o.SalespersonPersonID = 2 OR o.ContactPersonID = 3045
GO

Once you run the query change to the query message area and see the server execution time.

SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 1226 ms.

Next, run the test with the hint.

Test 2: With RowLock Hint

Run the following query where there is a use of RowLock Hint

USE WideWorldImporters
GO
SET STATISTICS IO, TIME ON
SELECT *
FROM [Sales].[Orders] o WITH (ROWLOCK)
INNER JOIN [Sales].[OrderLines] ol ON ol.OrderID = o.OrderID
INNER JOIN [Sales].[Invoices] i ON i.OrderID = o.OrderID
WHERE o.SalespersonPersonID = 2 OR o.ContactPersonID = 3045
GO

Now go to the messages tab and see the SQL Server Execution Time again.

SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 1442 ms.

You can clearly see that CPU time and elapsed time, in this case, is clearly more than an earlier case when we did not use rowlock hints.

I ran above test multiple times on multiple computers and have got the similar results.

Why ROWLOCK Hint is Slow?

In the example which we have seen, each query is actually returning over 23370 rows. Due to this reasons, SQL Server actually, prefers to use page level lock or table level lock. By explicitly using the ROWLOCK hint, actually, we force the SQL Server optimizer to use just use the suboptimal kind of hint, which eventually leads to slower performance.

Summary

There are a few cases where rowlock will work fine but in the most of the cases, which I have come across, most of the time, it just performs poorly. In the particular case which I have described earlier in the first paragraph, I requested them to test out their query without ROWLOCK Hint and their performance was improved.

Well, there are quite a few such tricks we discuss during Comprehensive Database Performance Health Check. 

Reference: Pinal Dave (https://blog.SQLAuthority.com)