Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


read UNCOMMITTED run slower than read COMMITTED


read UNCOMMITTED run slower than read COMMITTED

Author
Message
matthew.peters
matthew.peters
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
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
Attachments
uncomitted.sqlplan (20 views, 29.00 KB)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44970 Visits: 39864
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
matthew.peters
matthew.peters
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 349
OK I just uploaded them again, whats the error when you try to open them?
sqlGDBA
sqlGDBA
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 288
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.
sqlGDBA
sqlGDBA
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 288
Why dont you save them as .sqlplan files and upload them again ?

Also - upload plans for both. The faster plan and the slower one.
matthew.peters
matthew.peters
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
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.
Attachments
SQLPLans.rar (10 views, 7.00 KB)
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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
When a question, really isn't a question - Jeff Smith
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


TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8298
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14371 Visits: 9729
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
matthew.peters
matthew.peters
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
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 ??
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search