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.