SQL Server tips and experiences dedicated to my twin daughters.

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

The scenario:

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.

–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).

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.

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]