(This blog post was first published on 18-jan-2013 on my former blog)
Download all used scripts here.
Query hints WITH (NOLOCK) and WITH (READPAST) are a great way to increase performance on (readonly) queries that run on one or more tables in which data is added , edited or deleted at the same time.
However those query hints have also some drawbacks, to be aware of, when using them.
In this article I will demonstrate how these query hints work, and when to use them.
Let’s start with some theory: what do these query hints do?
It makes no sense to copy the MSDN description here. If you need more detailed information you can read it yourself on msdn
I will just summarize what these query hints can do for me.
WITH (READPAST) – shows only committed data. When a record gets updated, it will not be selected at all, not the previous version of the record, nor the updated but uncommitted version.
WITH (NOLOCK) – shows both committed and uncommitted (dirty) data. When a record gets updated, the new uncommitted values of the record will be selected.
When to use
In general, do not use these queryhints, when you retrieve data to edit, e.g. in disconnected scenario’s like editing in a webapplication. Working with uncommitted data will in this case seldom be what you want.
When it comes to read only access, it depends. You have to decide what is more important: showing always the right (committed) data, or showing data fast. If the risk that uncommitted data is shown is acceptable, WITH (NOLOCK) is the query hint for you, if the risk that data being changed is not shown at all, WITH (READPAST) is the query hint for you.
Also for semi-static data (dimension- or system tables), that is seldom changed, the risk of the drawbacks of both queryhints, is usually acceptable.
Executing the demo
Step 1
First of all we have to do some setup work. I have made a script for that. You can run it in SQL Server Management Studio (see link to download all scripts at the top of this article).
Note that for simplicity of the demo I have created a CHAR column to be primary key. I know it is not a best practice, but for the demo it is sufficient. Normally you would always strive for numeric primary keys.
— Step 1: Create a QueryHintsDemo database with one table, DemoData
USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N’QueryHintsDemo’)
CREATE DATABASE [QueryHintsDemo]
GO
USE [QueryHintsDemo]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DemoData]’) AND type in (N’U’))
DROP TABLE [dbo].[DemoData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DemoData](
[DemoDataCode] CHAR(6) NOT NULL,
[DemoDataDescription] VARCHAR(100) NOT NULL,
CONSTRAINT [PK_dbo_DemoData] PRIMARY KEY CLUSTERED
(
[DemoDataCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Download all used scripts here.
Step 2
What is next?
Now you have to make sure you have two query windows open in Management Studio, so you can execute one script, and before that is finished, execute another script as well.
The first script will insert some initial data in the DemoData table, and then update it in a transaction. To be able to do this demonstration properly, the WAITFOR DELAY feature is used to wait 20 seconds before the transaction is rolled back.
In the first query window, use this script:
TRUNCATE TABLE [QueryHintsDemo].[dbo].[DemoData]
GO
INSERT INTO [QueryHintsDemo].[dbo].[DemoData]
([DemoDataCode]
,[DemoDataDescription])
VALUES
(‘DEMO01’
,‘This is the original text.’)
GO
BEGIN TRANSACTION
UPDATE [QueryHintsDemo].[dbo].[DemoData]
SET [DemoDataDescription] = ‘This is the updated text that gets rolled back later.’
WHERE [DemoDataCode] = ‘DEMO01’
WAITFOR DELAY ’00:00:20′
ROLLBACK TRANSACTION
GO
BEGIN TRANSACTION
UPDATE [QueryHintsDemo].[dbo].[DemoData]
SET [DemoDataDescription] = ‘This is the updated text that gets committed.’
WHERE [DemoDataCode] = ‘DEMO01’
COMMIT TRANSACTION
GO
This is the script for the second querywindow:
— WITH READPAST, will not show the updated, non committed record
— but also not the ‘old’ version of the record, before it got updated.
— So you potentially miss out some data when using this query hint.
SELECT ‘WITH (READPAST)’ AS [READPAST], *
FROM [QueryHintsDemo].[dbo].[DemoData] WITH (READPAST)
GO
— WITH NOLOCK, will show the updated, but uncommitted record. This is also called a “dirty read”.
— The risk here is that you use/display data, that in the end will
— not be committed and therefor never became ‘official’.
SELECT ‘WITH (NOLOCK)’ AS [NOLOCK], *
FROM [QueryHintsDemo].[dbo].[DemoData] WITH (NOLOCK)
GO
— Without using any queryhint, the select statement will wait until the
— first transaction is rolled back, and then show the original value.
— This is the most reliable, but also the slowest.
— You will notice that the first two SELECT statements will return
— a result immediately, but that the query below will only show result after almost
— 20 seconds, caused by the 20 seconds delay in the previous script.
SELECT ‘Without queryhint (1)’ AS [No hint 1], *
FROM [QueryHintsDemo].[dbo].[DemoData]
— Wait a few seconds, so that the update to be committed in the other script, is completed.
WAITFOR DELAY ’00:00:02′
— Without using any queryhint, the select statement will wait until the
— update is finished, and then show the final value.
SELECT ‘Without queryhint (2)’ AS [No hint 2], *
FROM [QueryHintsDemo].[dbo].[DemoData]
As you might already have understood, you should execute both scripts in SQL Server Management Studio: execute the first script, and while it is still running, start the second script in a seperate query window as soon as possible (but at least within 20 seconds after starting the first script).
Analyzing the query result
Clean up
Use this script to clean up.
USE [master]
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’QueryHintsDemo’
GO
ALTER DATABASE [QueryHintsDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [QueryHintsDemo]
GO
Conclusion
The queryhints WITH (NOLOCK) and WITH (READPAST) are a great way to improve performance.
However they both have their own drawnbacks, to be aware of, when using them:
- When using WITH (READPAST), you potentially miss out some data that is being updated, when your select query runs.
- When using WITH (NOLOCK), the risk here is that you use/display data, that in the end will not be committed and therefor never became ‘official’.
It depends on the situation, when it is acceptable to use these queryhints.