Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

read UNCOMMITTED run slower than read COMMITTED Expand / Collapse
Author
Message
Posted Thursday, November 22, 2012 10:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, Visits: 349
Hello
I have the following query which runs considerably slower when we change the isolation level to read uncomitted, I just wanted to understand why. I have attached the 2 query plans and scripts


  Post Attachments 
uncomitted.sqlplan (20 views, 29.17 KB)
Post #1388021
Posted Thursday, November 22, 2012 10:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 36,995, Visits: 31,522
matthew.peters (11/22/2012)
Hello
I have the following query which runs considerably slower when we change the isolation level to read uncomitted, I just wanted to understand why. I have attached the 2 query plans and scripts


Not sure what the problem is but I can't open you're query plan with SQL Server 2008 client tools (SSMS).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388023
Posted Thursday, November 22, 2012 11:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, Visits: 349
OK I just uploaded them again, whats the error when you try to open them?
Post #1388045
Posted Friday, November 23, 2012 1:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
idk about Jeff, but it seems to be a 30 KB xml file that when I open up using management studio, just brings up a blank page. I do have one question however: have you tested several times for consistency ? Just to make absolutely certain that the behavior is consistent(Read committed always results in faster exec time) ?

Cant understand how that could be the case. Read uncommitted should have(if anything) lesser overhead because of the locks. Cant understand why there would be a difference in the execution plans at all.
Post #1388247
Posted Friday, November 23, 2012 1:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
Why dont you save them as .sqlplan files and upload them again ?

Also - upload plans for both. The faster plan and the slower one.
Post #1388251
Posted Sunday, November 25, 2012 4:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, Visits: 349
Not sure what the problem is here, I can open the attachment fine, if you have a look it is saved as a .sqlplan file. It may be the version of management studio I'm on, I'm running 2008 R2. I have put all the files into a rar file and uploaded them, if this doesn't work the only thing I can think of is to take a screen shot, let me know how you go

To answer your first question I ran it several times and got the same result.


  Post Attachments 
SQLPLans.rar (10 views, 7.50 KB)
Post #1388421
Posted Monday, November 26, 2012 2:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 5,227, Visits: 5,087
First off why is the statement wrapped in sp_cursorprepexec?

Second, the parameters supplied in both sql scripts are different so will affect query time.

Third, read uncommitted brings in a whole different behavour so you could be running into things like dirty reads etc which will change the behavour of the execution.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1388477
Posted Monday, November 26, 2012 7:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
This is often because the non-read-uncommitted SELECT is blocked by INSERT/UPDATE/DELETE operations currently ongoing. You can use sp_whoisactive (an awesome free tool on sqlblog.com) to observe this behavior if it is the issue.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1388637
Posted Monday, November 26, 2012 9:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I've seen Read Committed run faster than Read Uncommitted in a couple of circumstances.

One is if the table is heavy on data-modification, and Read Committed Snapshot Isolation has been enabled. In that case, Read Committed will read from snapshots, which can be faster, especially if tempdb is on different drive media than the main DB (since tempdb is where the snapshots are held for RCSI). Can be faster, can be slower, depends on a lot of variables, but definitely can be faster in some situations.

The other is heavy updates causing lots of page splits. The Read Committed version will lock the pages it needs, preventing concurrent splits. This can make a big difference in I/O time on the query, especially if it's over a range of data that parallels the leading edge of one of the indexes. The RU query will have to deal with pages being split while it's reading, potentially reading the same data two or more times, which can result in the total query being slower and returning incorrect results, both at the same time.

Either one is kind of rare, but they can happen.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1388682
Posted Monday, November 26, 2012 4:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, Visits: 349
First off why is the statement wrapped in sp_cursorprepexec?

Second, the parameters supplied in both sql scripts are different so will affect query time.

Third, read uncommitted brings in a whole different behavior so you could be running into things like dirty reads etc which will change the behavour of the execution.

The statement is wrapped in a sp_cursorprepexec because this is prepared statement that I have captured through profiler coming from the application

If you check the statements you will find that the parameters on both are the same

I just did some more testing and found that the read committed returns no results set yet the read uncommitted returns a result set??? (dont know why I didn't pick this up in the first place) Why would this be, they are exactly the same statement but just with a different isolation level. The database I'm testing on has no other users connected ??
Post #1388895
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse