December 27, 2011 at 3:39 pm
Hi everyone,
We are in the process of enabling RCSI on our operational data store databases in SQL Server 2008 Enterprise. As a consequence of that we have asked the developers that consume the data to remove NOLOCK from their queries as NOLOCK overrides RCSI. As they were doing this an interesting performance issue developed. For the queries below the execution plans are identical. When NOLOCK is used the results are brought back in 2 sec and when it is not used the results take approximately 1 min 45 sec to return. I have included the IO STATISTICS with the queries. The table has approximately 25000000 rows of which about 2000000 meet the where clause criteria. The query plans are very simple a clustered index scan a top and a select with the scan using 100% of the resources. These queries were run on a quiet environment with no other users or processes reading or changing the data.
Set Statistics IO on
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Select top 1 *
from tableX with (nolock) where srcsys_cde = 'K' and Delete_IND <> 'Y'
---Table 'tableX'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 3514, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--execution time 2 seconds
Set Statistics IO on
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Select top 1 *
from tableX where srcsys_cde = 'K' and Delete_IND <> 'Y'
--Table 'tableX'. Scan count 1, logical reads 1788802, physical reads 2440, read-ahead reads 1797938, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Execution Time 1min 43sec
Obviously the big difference we see is the number of logical and physical reads, but we don't understand why the difference is there. We expected a slight performance decrease due to having to deal with locks that are avoided using NOLOCK but we didn't think the it would be this drastic. Does this have anything to do with the way a TOP command interacts with a NOLOCK? Any insight anyone could give us would be greatly appreciated as our users are very skeptical about removing the NOLOCKs from their queries if performance is potentially impacted this much. And we need the removal to occur to eliminate dirty reads.
December 27, 2011 at 3:54 pm
Would help if you posted the query and the two actual execution plans.
December 27, 2011 at 4:24 pm
I've been doing some research on Read Committed Snapshot Isolation myself. I was just asking about possible problems with it on Twittter (#SQLHelp), and I was given several articles to read.
Off the top of my head, I would say that it could be the overhead in the Temp DB which RCSI creates. That seems most likely. I haven't actually dug through these articles myself (yet). So, I'll just list them for you.
Kimberly Tripp: msdn.microsoft.com/library/ms345124.aspx
Klaus Aschenbrenner: http://www.csharp.at/blog/PermaLink,guid,67a44522-a504-402a-9f9e-d569e5940c72.aspx
I will just add that - in my opinion - no amount of performance problems justifies using WITH(NOLOCK). I recently spent a week tracking down a most mysterious, transient, data conflict which turned out to be entirely due to the (extensive) use of WITH(NOLOCK) in our system.
The data warehouse team had been trying to pin down this problem for years. Once I took out the WITH(NOLOCK) clauses, the problem went away and has not returned.
The real kicker is that taking out the WITH(NOLOCK) clauses has not created a performance problem!
RCSI is not a magic bullet, though. In SQL Server - as in life - there is no free lunch! 🙂
December 27, 2011 at 4:34 pm
You know, I was just reviewing your code, and I have to ask: Is TableX, by any chance, a VIEW?
I have a hideous number of atrocious views in my system, and they all look like tables (in the code).
I ask because TOP doesn't help at all when querying a view that has a query condition on an aggregate in the HAVING clause. The entire view has to be generated before the clause can be evaluated.
Beyond that, I can't guess. Lynn is right - you need to post your code and the actual query execution plans for anyone to help.
December 28, 2011 at 8:29 am
Thanks everyone for the response. TableX is not a view. Also we have tried this on a server with RCSI enabled and on one with RCSI disabled and the results are the same. The example I gave yesterday is the query that the user sent to us. I have attached the XML in for the execution plans. They are essentially identical, which only adds to the confusion as one returns the data so much faster than the other.
Thanks
December 28, 2011 at 7:02 pm
It is a puzzle. The first query delivers the performance I would expect with either query. The second query is loading a large portion of the table into the buffer pool before it returns a result.
If no other processes are running on this server, then WITH(NOLOCK) will have no effect. WITH(NOLOCK) simply permits uncommitted data to be read. However, if no other sessions are running, there can be no open transactions, and thus no uncommitted data to read.
TOP 1 on an unordered data set (like this one) will simply return the very first row that meets the criteria. TOP 1 and WITH(NOLOCK) do not interact at all.
Based on the statistics you provided, about 8% of the records in the table meet the search criteria. The first query scans through (approximately) 18,000 records (out of nearly 24,000,000) before it finds a match. The second query scans through about 10,000,000 records (or 40% of the table) before it finds a match.
The only thing that makes any sense to me is that there must be some difference in the underlying physical data on the disk. Perhaps the database is heavily fragmented. There must be some difference in the physical structure. Because there is no difference at all in the logical structure of the two queries.
If you run the test several times and make sure that the test conditions are identical, then the performance difference will go away.
Come back and let us know what you find. I am very curious.
December 28, 2011 at 11:44 pm
What do you mean by TOP 1 if you don't specify ORDER BY? You don't care about what record it will return?
It may be a difference on how SQL Server access data with and without NOLOCK hint if ORDER BY is not specified.
If you don't specify NOLOCK then it will go to the first page of Clustered Index and then scan all the chain based on the links inside Clustered Index pages.
If there is a NOLOCK then it may go to IAM pages and scan them, which sometimes can be faster, and probably it's you case. I bet you have a huge fragmentation on clustered index and also some bad luck because the records you need are too far from the first page of your index.
And of course result with and without NOLOCK in you case will be different because the order of reading the data is different.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply