High Performance SQL Views Using WITH(NOLOCK)

Word cloud for databaseEDITOR’S NOTE: Since we last visited the subject of using NOLOCK to make database queries go faster, many other developers and DBAs have weighed in on the subject, adding warnings on its misuse. So the question now is, should you use a WITH (NOLOCK) on tables in your SELECT query? Also, additional SQL Server 2016 resources have been added to this blog.

Eric Cobb, writing in his SQL Nuggets blog, said always doing this is a bad practice. He explained adding NOLOCK to a query “is the same as setting the Transaction Isolation Level to READ UNCOMMITTED.”

Then when would you use NOLOCK? According to Cobb, you might want to use it to help avoid deadlocks with other queries running against the same data, as related in our original blog post. If you can deal with some inconsistencies in the results your query returns, such as long-term sales trends that don’t need real-time data, it might be of use. Or perhaps you are just testing a query and don’t want to tie up the database.

But that points to the problem with NOLOCK – if you use it you can’t guarantee accurate and precise results will be returned consistently from your query. And if you need it to avoid a blocking problem on your query, you’d better go fix the blocking problem instead.

Want to learn more about the worrisome effects of using NOLOCK? Take a look at this list that Aaron Bertrand shared:

  • Dirty read
  • Missing rows
  • Reading rows twice
  • Reading multiple versions of the same row
  • Index corruption
  • Read error

What Other Options Exist?

Reading through Bertrand’s post, you’ll see a nod to Kendra Little’s post on implementing Snapshot or Read Committed Snapshot Isolation. When database load increases get DBAs battling with blocking and deadlocks, solutions are sought. “People start using the NOLOCK hint to work around problems, often with disastrous results,” Little said.

Microsoft plans to remove the NOLOCK hint from SQL Server sometime in the future though that has yet to be put on an exact schedule. What other changes are to come are best followed at the Microsoft Developer Network website.
Come see what Izenda’s embedded self-service business intelligence and analytics has to offer your business.

Other SQL Server Resources

Preview Feedback Improves SQL Server 2016 in CTP 3.2

Will You Upgrade to SQL Server 2016?

SQL Server 2016 is generally available today

SQL Server 2016 product page

SQL Server 2016 Technical Documentation

Download from TechNet Evaluation CenterMSDN Subscriber DownloadsVisual Studio Dev Essentials, or your own Volume License server(s).

Request a live demo

Follow Izenda on social media for the latest on technology and business intelligence:
LinkedInOUTLOOKFacebookOUTLOOKTwitterOUTLOOK

 


Original Post: 2010/11/02

Every now and then you find a simple way to make everything much faster. We often find customers creating data warehouses or OLAP cubes even though they have a relatively small amount of data (a few gigs) compared to their server memory.

If you have more server memory than the size of your database or working set, nearly any aggregate query should run in a second or less. In some situations there may be high traffic from transactional applications and SQL Server may wait for several other queries to run before giving you your results.

The purpose of this is to make sure you don’t get two versions of the truth. In an ATM system, you want to give the bank balance after the withdrawal, not before, or you may get a very unhappy customer. So by default databases are rightly very conservative about this kind of thing.

Unfortunately, this split-second precision comes at a cost. The performance of the query may not be acceptable by today’s standards because the database has to maintain locks on the server. Fortunately, SQL Server gives you a simple way to ask for the current version of the data without the pending transactions. To better facilitate reporting, you can create a view that includes these directives:

CREATE VIEW CategoriesAndProducts AS
SELECT *
FROM dbo.Categories WITH(NOLOCK)
INNER JOIN dbo.Products WITH(NOLOCK) 
ON dbo.Categories.CategoryID = dbo.Products.CategoryID

In some cases, queries that are taking minutes end up taking seconds. Much easier than moving the data to a separate database and it’s still pretty much real time, give or take a few milliseconds. You’ve been warned not to use this for bank balances, though.

12 Comments

  • tlianza says:

    Wouldn't it be simpler to issue a "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" for the session you want to access read-only, instead of duplicating your query logic with NOLOCK's interspersed?

  • Marty says:

    No, messing with isolation level like that will give you uncommitted data. Why is there a blog post about something this basic in SQL Server? Don't waste our time…

  • tlianza says:

    NOLOCK will also give you uncommitted data:

    http://msdn.microsoft.com/en-us/library/aa259216(

    READ UNCOMMITTED

    "This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction."

  • tlianza says:

    NOLOCK will also give you uncommitted data.
    http://msdn.microsoft.com/en-us/library/aa259216(

    READ UNCOMMITTED

    "This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction."

  • tlianza says:

    NOLOCK will also give you uncommitted data.
    http://msdn.microsoft.com/en-us/library/aa259216(

    READ UNCOMMITTED

    "This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction."

  • Tom says:

    NOLOCK will also give you uncommitted data. http://msdn.microsoft.com/en-us/library/aa259216(

    READ UNCOMMITTED
    "This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction."

  • Krit says:

    Thanks for a tip and trick but this is a bit basic thing.

  • Csaba Áll says:

    More than that, the practical use of queries with NOLOCK is for example in a data-driven ASP.NET AJAX web application where the data shown to the user in a popup panel must be instantaneous (the panel is triggered when the user is hovering the mouse on a piece of data). In this case a query WITHOUT NOLOCK may be disappointing in terms of speed. This is an issue only with SQL Server. Oracle do not have similar feature to speed up the queries.

  • OracleDude says:

    Marty, not everyone knows this stuff. I'm an Oracle guy, but I also do some SQL Server stuff every now and then and I'm not as well versed on the SQL Server front. These kind of quick tips come in handy. Nobody likes a smug M.F.

  • Dan says:

    I though if you created a view, MS SQL server would not use indexes, so this may not be good if you want to further restrict the results. I would say just remember to use the with(lock) on all your queries, that do not necessarily need the latest version.

  • Glen says:

    For our Datawarehouse which only refreshes once a day I use the WITH (NOLOCK) extensively. For an OLTP database I would NOT use it. That's it.

  • Doug says:

    I've created a view for a data source and I need to be able to pass one of the filter values into the view.
    Has anyone ever done that ?

    Thanks,

    Doug