There are many myths related to performance tuning in SQL Server. One of the top-ranking myths is that using the WITH (NOLOCK) table hint is a silver bullet to 80% of the performance problems that an application might be facing. However, the WITH (NOLOCK) may not even be in effect in all situations.
If a table contains a computed column, then the table hints on the table do not propagate through to the functions and expressions used to define that computed column. Let’s study this statement with the help of an example.
Demo
For the purposes of this demo, let us first create a table with a computed column. We will be using the [dbo].[ufnGetProductDealerPrice] scalar function available in the AdventureWorks2012 sample database to populate the computed column used in this demo.
USE AdventureWorks2012;
GO
SET NOCOUNT ON;
IF OBJECT_ID(‘dbo.ProductDealerInformationByOrder’,’U’) IS NOT NULL
DROP TABLE dbo.ProductDealerInformationByOrder;
GO
CREATE TABLE dbo.ProductDealerInformationByOrder
(ProductId INT,
OrderDate DATETIME,
DealerListPrice AS (dbo.[ufnGetProductDealerPrice](ProductId, OrderDate))
);
GO
–Populate some test data
INSERT INTO dbo.ProductDealerInformationByOrder (ProductId, OrderDate)
SELECT sod.ProductID,
soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = 51721;
GO
The scenario:
The scalar function – [dbo].[ufnGetProductDealerPrice] references two tables:
- Production.Product
- Production.ProductListPriceHistory
To check whether the NOLOCK hint is propagated through the computed column and affects the tables referred inside the function, we will be updating some of the values in the table: Production.ProductListPriceHistory inside a transaction and then attempting to select data from our test table – dbo.ProductDealerInformationByOrder by using the WITH (NOLOCK) table hint. If the table hint does propagate through the scalar function to the Production.ProductListPriceHistory table, then the query should return some data (it would be uncommitted data).
Step 01: Run the update to Production.ProductListPriceHistory table
The script below performs the update to the Production.ProductListPriceHistory and lists out all the locks that have been acquired by the transaction. Notice that we have neither committed nor rolled back the transaction, so the locks continue to remain in effect.
USE AdventureWorks2012;
GO
— Note the session number!
BEGIN TRANSACTION LockProductListPriceHistory
UPDATE plph
SET plph.ListPrice = (plph.ListPrice + 10)
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.ProductListPriceHistory AS plph ON sod.ProductID = plph.ProductID
WHERE soh.SalesOrderID = 51721;
–Check the nature of the locks applied
SELECT sdtl.request_type AS LockRequeust,
sdtl.request_status AS LockRequestStatus,
sdtl.request_mode AS LockMode,
sdtl.resource_type AS ObjectType,
DB_NAME(sdtl.resource_database_id) AS DatabaseName,
SCHEMA_NAME(so.schema_id) AS SchemaName,
so.name AS ObjectName,
sdtl.request_session_id AS SessionNumber
FROM sys.dm_tran_locks AS sdtl
INNER JOIN sys.objects AS so ON sdtl.resource_associated_entity_id = so.object_id;
–ROLLBACK TRANSACTION LockProductListPriceHistory
The output on my test server is shown below.
Step 02: Select Data from test table
The next step now is to select data from our test table, from a new query editor window in SSMS (so that we begin a new session).
/* Run this in a new SSMS query editor window. */
USE AdventureWorks2012;
GO
BEGIN TRANSACTION SelectWithNoLock
;WITH ProductInfoCTE (ProductId, OrderDate, DealerListPrice)
AS (SELECT pdio.ProductId,
pdio.OrderDate,
pdio.DealerListPrice
FROM dbo.ProductDealerInformationByOrder AS pdio WITH (NOLOCK)
)
SELECT DISTINCT sdtl.request_type AS LockRequest,
sdtl.request_status AS LockRequestStatus,
sdtl.request_mode AS LockMode,
sdtl.resource_type AS ObjecType,
DB_NAME(sdtl.resource_database_id) AS DatabaseName,
SCHEMA_NAME(so.schema_id) AS SchemaName,
so.name AS ObjectName,
sdtl.request_session_id AS SessionNumber,
pcte.ProductId,
pcte.OrderDate,
pcte.DealerListPrice
FROM sys.dm_tran_locks AS sdtl
INNER JOIN sys.objects AS so ON sdtl.resource_associated_entity_id = so.object_id
CROSS JOIN ProductInfoCTE AS pcte;
ROLLBACK TRANSACTION SelectWithNoLock
GO
What we observe is that the query continues to execute without any results being returned (Note down the session Id, in this case, 55):
Step 03: Examine the locks acquired
Clearly, the query is being locked by another process or task. Hence, we will open up another query editor window in SSMS and execute the query provided below:
/* Run this in a new SSMS query editor window (window #3). */
USE AdventureWorks2012;
GO
SELECT sdtl.request_type AS LockRequeust,
sdtl.request_status AS LockRequestStatus,
sdtl.request_mode AS LockMode,
sdtl.resource_type AS ObjectType,
DB_NAME(sdtl.resource_database_id) AS DatabaseName,
SCHEMA_NAME(so.schema_id) AS SchemaName,
so.name AS ObjectName,
sdtl.request_session_id AS SessionNumber,
sr.blocking_session_id AS BlockingSession
FROM sys.dm_tran_locks AS sdtl
INNER JOIN sys.objects AS so ON sdtl.resource_associated_entity_id = so.object_id
LEFT OUTER JOIN sys.dm_exec_requests AS sr ON sdtl.request_session_id = sr.session_id
ORDER BY sr.session_id;
GO
Here is the output from my test server:
As can be seen from the screenshot, we can see that the WITH (NOLOCK) query from the select statement from session #55 has requested Sch-S (Schema stability) locks on the dbo.ProductDealerInformationByOrder and the scalar function – dbo.ufnGetProductDealerPrice indicating that the hint is in effect for the objects directly referenced on the query.
However, it is requesting IS (Intent Shared) locks on the Production.Product and Production.ProductListPriceHistory tables. Unfortunately, the Production.ProductListPriceHistory is being locked by an IX (Intent Exclusive) lock from the session #52 thereby blocking session #55.
Running the ROLLBACK TRANSACTION statement in session #52 unblocks session #55 and results are finally returned.
Hence proving that,
If a table has computed columns that are computed by functions and expressions that refer other objects, the table hints on the parent table are not propagated to the objects referred by these functions and expressions.
Further Reading
- Myths – Using WITH (NOLOCK) also works on INSERT/UPDATE/DELETE statements [Link]
- Books-On-Line/MSDN: Table Hints in SQL Server [Link]
- Pinal Dave, a.k.a. “SQLAuthority” (B|T): What Kind of Lock WITH (NOLOCK) Hint Takes on Object? [Link]
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 