January 5, 2012 at 3:54 am
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.
January 5, 2012 at 5:06 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2012 at 6:30 am
Here are the actual execution plans for both versions.
I ran a
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
before each run as well.
January 5, 2012 at 7:03 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2012 at 7:07 am
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
January 5, 2012 at 7:47 am
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.
January 5, 2012 at 7:52 am
HW = hardware.
But isolated & repeated tests takes out much of that option, if not all of it.
No more ideas on this!
January 5, 2012 at 9:01 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2012 at 9:06 am
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!
January 16, 2012 at 12:25 am
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.
January 7, 2015 at 10:04 am
Investigated some detailes here:
http://www.queryprocessor.com/nolock-and-top-optimization/
January 7, 2015 at 10:56 am
SomewhereSomehow (1/7/2015)
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.
January 7, 2015 at 1:06 pm
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?
January 7, 2015 at 3:35 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2015 at 1:38 am
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply