Addition of NOLOCK to subquery causes slower query execution

  • I'm have an interesting scenario I have never encountered before and was wondering if anyone has seen this, or could explain potential sources of its occurrence.

    Simple enough query (Version 1):

    select col1, col2 ....

    from table1

    where col1 = 'BLAH1' and col2 = 'BLAH2'

    and col3 not in (

    select col11

    from table2)

    Produces the following statistics from statistics time and io:

    (1755 row(s) affected)

    Table 'table2'. Scan count 25, logical reads 372488, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'table1'. Scan count 9, logical reads 67001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 4677 ms, elapsed time = 645 ms.

    Now simply adding a nolock hint to table2 causes execution to run considerably slower (Version 2 - with nolock):

    select col1, col2 ....

    from table1

    where col1 = 'BLAH1' and col2 = 'BLAH2'

    and col3 not in (

    select col11

    from table2 with(nolock))

    Produces the following statistics from statistics time and io:SQL Server parse and compile time:

    (1755 row(s) affected)

    Table 'table2'. Scan count 25, logical reads 131352, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'table1'. Scan count 9, logical reads 67001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 122434 ms, elapsed time = 15471 ms.

    The point of interest is the Execution times, we see a marked increase in Version 2, with the NOLOCK added.

    The actual and estimated execution plans are identical, and the output from statistics profile is identical.

    I've rebuild the indexes on all affected tables and updated statistics as well.

    I cant see how removing locking could increase execution time.

    I am at a loss to explain this phenomena, any help appreciated.

  • It does 1/3 the number of reads, but increases the time? No, that's not one I've seen. Can you post both actual execution plans? I know you say they're identical, but do you mean in terms of the operators or the properties on the operators too? You can have a plan that's visually the same, but has different internals.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Here are the actual execution plans for both versions.

    I ran a

    DBCC FREEPROCCACHE

    DBCC DROPCLEANBUFFERS

    before each run as well.

  • Yeah, I can't spot any differences either. Hmmm.... Estimates & actuals are off, more than a little bit. I know you updated the statistics, but did you try using WITH FULL SCAN? Other than, I'm stuck. Other things to try, use an outer JOIN instead of the NOT IN. See if limiting parallelism makes a difference. I'm not sure.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Locking <> blocking. You removed only 1 of the factors, but not the hw.

    Just in case you didn't know :

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

  • Ninja's_RGR'us (1/5/2012)


    Locking <> blocking. You removed only 1 of the factors, but not the hw.

    Just in case you didn't know :

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    Not sure what you mean by:

    You removed only 1 of the factors, but not the hw.

    Could you elaborate?

    With reference to the URL, this thread wasn't aimed at being a discussion about the peril's of using NOLOCK. This topic has been discussed to death in various forums, blogs... It is more aimed at the peculiarity of this specific situation and under why the addition NOLOCK, which should decrease locking overhead and decrease execution time, when in fact, it decreases logical reads, while increasing execition time.

    Note also that these results were generated, (repeatedly), in an isolated development environment, after first noticing them in a production one.

  • HW = hardware.

    But isolated & repeated tests takes out much of that option, if not all of it.

    No more ideas on this!

  • Just a note, the use of no_lock CAN reduce time because it reduces locking, but it's not an automatic. Not trying to get into the whole no_lock discussion, but we should be clear.

    I'm thinking there has to be some difference that's not being noted here other than the no_lock, but I sure don't see it based on the information in front of us.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I'd actually try running the tests with this.

    http://www.datamanipulation.net/sqlquerystress/

    Easy to take screen shots and the results are easily tallied and copied over here!

  • Apologies for the long delay in response. I haven't been able to further investigate this situation, though should I be able to look at it again, I will be sure to post any finding of interest.

  • Investigated some detailes here:

    http://www.queryprocessor.com/nolock-and-top-optimization/


    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

  • SomewhereSomehow (1/7/2015)


    Investigated some detailes here:

    http://www.queryprocessor.com/nolock-and-top-optimization/%5B/quote%5D

    Note that this is a 3 years old thread.

    The information might be useful if someone arrives here with the same problem.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Long time passed…

    Do you have a solution so far? Explanation?

    Did you read the blog post?

    Do you know that this problem is applied even in 2014 sql server?

    Did you examined the text and saw that this is actual for today?


    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

  • That's certainly a potential solution to the problem.

    Let me provide the link as a URL link.[/url]

    Just so you know, if you post a link, use the little globe button above the edit window with some highlighted text to make the URL clickable as I've done above. It'll help drive more people to the solution since they won't have to copy & paste to get there.

    Nice write up.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks Grant. I've corrected my message to be more convenient.

    I've also update my blog post to define what does scan "initialization" mean.


    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply