November 14, 2011 at 8:13 am
hi i need to know what is the use of WITH (NOLOCK, READUNCOMMITTED)
plese give me some threads for detailed description
November 14, 2011 at 8:15 am
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2011 at 8:17 am
Another case of 'The Department of Redundancy Department'. Those 2 hints mean exactly the same thing (and it's not 'go faster')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 8:29 am
Well, most people think the primary use of either of those is to make the query faster. They're wrong, but that's what they think it's for.
Putting both of them in there is probably just a question of "If one rocket engine on my skateboard is going to be good, two should be even better!" Ignorance works that way.
The real use of them is allow a query to skip over data that is in use by some other process. This can be useful in incrementally loading data from one table to another, where rows that are skipped can be picked up by the next load process, or other places where skipping rows won't actually matter to the end result of the data consumption.
The most common use of them is to "magically make the database faster", while ignoring the negative effects of skipping in-transit data. It's like using steroids in a professional sport. It may give a short-term performance boost, but it will also likely kill you. (Kill your data in this case, possibly kill the business you work for, likely kill your career or at least your current job if management ever finds out that all the reports and processes you built for them are just plain wrong because of skipped or double-read rows of data.)
Read up on the "Cargo Cult" mindset. It's exactly what makes developers use these things in most cases.
- 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
November 14, 2011 at 9:11 am
OK GSquared. Now you have me worried.
I use WITH (NOLOCK) extensively - not as a go-faster thing, but to avoid having to wait for locks to clear before getting the data.
I always assumed we could get incorrect data - perhaps if a transaction was rolled back, we'd get the data that was never committed.
But you seem to be implying we could get 2 rows where only 1 exists, or no row at all?
November 14, 2011 at 9:14 am
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 9:14 am
Tom Brown (11/14/2011)
OK GSquared. Now you have me worried.But you seem to be implying we could get 2 rows where only 1 exists, or no row at all?
Yes.
If data moves around (page split, PK updated). You can either miss the row or read it twice, thrice if real unlucky.
If data quality matters, you can NOT use that isolation level.
If the timing is right, it will even kill your connection, so you need a retry logic.
November 14, 2011 at 9:14 am
Tom Brown (11/14/2011)
OK GSquared. Now you have me worried.I use WITH (NOLOCK) extensively - not as a go-faster thing, but to avoid having to wait for locks to clear before getting the data.
I always assumed we could get incorrect data - perhaps if a transaction was rolled back, we'd get the data that was never committed.
But you seem to be implying we could get 2 rows where only 1 exists, or no row at all?
Did you read the link I posted?
Reading Rows Twice:
If you are in the process of doing an IAM scan and read a page that gets split after you just read it you can actually read up to half the rows from that page again since the split page will go to the end of the IAM chain.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2011 at 9:19 am
It's only been posted 3 times... not sure it's enough ;-).
November 14, 2011 at 9:21 am
Ninja's_RGR'us (11/14/2011)
It's only been posted 3 times... not sure it's enough ;-).
Yeah apparently we were all three typing at the same time. Kind of scary!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2011 at 9:25 am
Sean Lange (11/14/2011)
Ninja's_RGR'us (11/14/2011)
It's only been posted 3 times... not sure it's enough ;-).Yeah apparently we were all three typing at the same time. Kind of scary!!
I think I've seen up to 5 correct answer in the same minute as the 1st reply to the op. :w00t:
November 14, 2011 at 9:27 am
Ninja's_RGR'us (11/14/2011)
Sean Lange (11/14/2011)
Ninja's_RGR'us (11/14/2011)
It's only been posted 3 times... not sure it's enough ;-).Yeah apparently we were all three typing at the same time. Kind of scary!!
I think I've seen up to 5 correct answer in the same minute as the 1st reply to the op. :w00t:
We need to get a hobby (oh I guess posting here is our hobby). 😛 Funny how we all post the same link and type pretty much the same thing on this topic 2-3 every week.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2011 at 9:29 am
Sean Lange (11/14/2011)
Ninja's_RGR'us (11/14/2011)
Sean Lange (11/14/2011)
Ninja's_RGR'us (11/14/2011)
It's only been posted 3 times... not sure it's enough ;-).Yeah apparently we were all three typing at the same time. Kind of scary!!
I think I've seen up to 5 correct answer in the same minute as the 1st reply to the op. :w00t:
We need to get a hobby (oh I guess posting here is our hobby). 😛 Funny how we all post the same link and type pretty much the same thing on this topic 2-3 every week.
Only 2-3?
You don't read enough posts! :w00t:
November 14, 2011 at 9:32 am
OK I've read and inwardly digested the link. :blush:
Seems like SNAPSHOT Read committed is one way to go. However if I investigate this and find is what we want, I'm gonna need a help in the argument to persuade our DBAs to implement it.
November 14, 2011 at 9:32 am
Sean Lange (11/14/2011)
Funny how we all post the same link and type pretty much the same thing on this topic 2-3 every week.
Type?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply