<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
read UNCOMMITTED run slower than read...
15 posts, Page 1 of 2
1
2
»»
read UNCOMMITTED run slower than read COMMITTED
Rate Topic
Display Mode
Topic Options
Author
Message
matthew.peters
matthew.peters
Posted Thursday, November 22, 2012 10:04 PM
SSC 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
Jeff Moden
Jeff Moden
Posted Thursday, November 22, 2012 10:22 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906,
Visits: 26,789
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1388023
matthew.peters
matthew.peters
Posted Thursday, November 22, 2012 11:19 PM
SSC 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
sqlGDBA
sqlGDBA
Posted Friday, November 23, 2012 1:27 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, May 03, 2013 10:05 AM
Points: 78,
Visits: 185
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
sqlGDBA
sqlGDBA
Posted Friday, November 23, 2012 1:54 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, May 03, 2013 10:05 AM
Points: 78,
Visits: 185
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
matthew.peters
matthew.peters
Posted Sunday, November 25, 2012 4:27 PM
SSC 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
anthony.green
anthony.green
Posted Monday, November 26, 2012 2:26 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
TheSQLGuru
TheSQLGuru
Posted Monday, November 26, 2012 7:47 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 3,575,
Visits: 5,115
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
GSquared
GSquared
Posted Monday, November 26, 2012 9:31 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442,
Visits: 9,571
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
matthew.peters
matthew.peters
Posted Monday, November 26, 2012 4:48 PM
SSC 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 »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
