September 5, 2008 at 8:23 am
I might see Nolock or Readpast as moderately useful in a Where Exists statement in a trivial issue. Even there, I'd be concerned about it, since it's hard to tell if something "trivial" will turn into something seriously non-trivial somewhere down the road.
I have used index hints a few times, mainly in testing. Add five or six indexes to a table, force certain queries to use each one, test IO and CPU time and such, decide which one is best and then get rid of the other indexes and the hints.
A very few times, I've found that index and join hints have helped, when one proc might return a small number of rows some of the time, and a huge number other times, and code maintenance precludes separate procs with separate execution plans. I'd rather run an unnecessary hash join on a small data set in order to make the large data set run that one instead of a nested loop.
I have one proc, for example, that runs 5-10 rows half the time, and over 10-million rows the other half of the time, up to 53-million rows about 10% of the time. I tested recompile vs forcing a hash join and found the hash join hint ended up with the best average performance. Also had to force certain indexes, also based on average performance testing. Pushed the small data sets up to about 1 second total processing time, up from half a second, but moved the large data sets down to 18 minutes from 3+ hours, and the huge data sets down to 1-2 hours from up to 3 days. In that case, after extensive testing, I'm using the hints I came up with. But that's an extreme case. (This is an automated process on a critical workflow. There isn't a user sitting there waiting for a web page to finish loading or anything like that.) And there isn't a single Nolock in that process, since it absolutely cannot have dirty reads for business reasons.
- 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
September 5, 2008 at 3:29 pm
Just for an example I had to find some "bad" dates (.... "24:00:00") that crippled a some random rows in a large table (500+ million rows) and locking the table was simply not an option, lock escalation was prohibitive and creating an index was not possible... "Nolock" to the rescue!
* Noel
September 8, 2008 at 5:59 am
I remain where I was. Any of the hints can be useful in a given situation. My primary issue is when people see it as a general fix or best practice for lots of places or even everywhere. I've seen it done with various hints, NOLOCK, FAST N, KEEP FIXED PLAN... Every one one of them appeared to "fix" the underlying problem, bad code, bad indexing, bad table design, in the short term. Every one of them, when applied universally, proved to be extremely costly in the long run. NOLOCK starts returning bad data. Duplicate rows, in any system, are an issue. As are missing rows. Both these can be cased by NOLOCK. It's not just a question of getting 'NY' instead of 'New York' because an update is in progress.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2008 at 3:26 am
The one place where I frequently use with (nolock) is in our datawarehousing ETLs - I KNOW that nothing else is hitting my staging database while my ETL is loading - I'm writing, then I'm reading.
If anything else is hitting the database, I have bigger problems.
Also MAXDOP - theoretically SQL knows best, but I've come across many instances where the parallelisation that it performs is nowhere near optimal.
September 9, 2008 at 6:07 am
Yeah, MAXDOP has been useful situationally. Although if I see a lot of queries suffering from parallelism, I'll raise the threshold.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2008 at 8:03 am
The first one is the reason so many database developers like Nolock. They notice that the query runs faster pretty much every time, get excited about that, and then use it all over the place, without accounting for the second effect.
I disagree with that. The savings is certainly under 1% of the net cost, probably well under 0.01%.
If you're encountering severe locking problems in SQL 2005 or higher, use SNAPSHOT isolation. That way reads never take locks, but don't read inconsistent data. It's the way Oracle works (more or less). The downside is that TempDB usage increases.
Beware that one. Performance is often truly horrid. Most people don't have tempdb appropriately configured for NORMAL usage, much less snapshot usage added in. Also IIRC there are some other thorny problems that arise with snapshot usage.
Yeah, MAXDOP has been useful situationally. Although if I see a lot of queries suffering from parallelism, I'll raise the threshold.
I firmly believe that 5 is definitely too low for CTOP setting. I also believe that most boxes perform better with a maxdop setting other than the default of 0. The engine (in combination with the usual hyperthreading/poor IO config, etc) just isn't able to do right with all cores used.
My best story with DOP setting is a client that had set it to 0. WOW was that a mess!! Virtually every plan was considered for parallelism (huge CPU drain calculating all those extra plans) and many of them went that route. I looked like I walked on water with the server overhead I gave back 15 mins after walking through the door on my first onsite visit for a performance review!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 9, 2008 at 10:43 am
Ok, so I've seen some half-answers to this question, but my pea-brain needs a clear response to this:
I'm working in an environment where the reporting server is only updated once every 24 hrs after work-hours, so as far as I'm concerned, it's static data.
I cannot update/delete any data in the tables.
Our DBA told all of the reporting analysts (who are in the same boat as myself) that we should use NOLOCK on everything.
If NOLOCK only helps on write situations, then he's full of hooey.
Is he full of hooey?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 9, 2008 at 12:03 pm
Hooey.
If it's a reporting system, and only a reporting system, you should mark the database itself as read only.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2008 at 12:23 pm
jcrawf02 (9/9/2008)
Ok, so I've seen some half-answers to this question, but my pea-brain needs a clear response to this:I'm working in an environment where the reporting server is only updated once every 24 hrs after work-hours, so as far as I'm concerned, it's static data.
I cannot update/delete any data in the tables.
Our DBA told all of the reporting analysts (who are in the same boat as myself) that we should use NOLOCK on everything.
If NOLOCK only helps on write situations, then he's full of hooey.
Is he full of hooey?
1) It does save on the overhead of taking/honoring locks - but that is simply a tiny amount of effort in reality, although not completely negligible.
2) I too would recommend setting the database to read-only. During your ETL process the first step would be to set it read-write, do the ETL, then finish by setting it back to read-only.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 9, 2008 at 1:20 pm
I'll third the switch to read-only . Not only does it remove the need to consider locking at all, it also prevents anyone from 'accidentally' changing data in the reporting system.
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
September 9, 2008 at 3:13 pm
IMHO, WITH(NOCLOCK) is a tool like any other sql statement. Used incorrectly it can certainly bite ya. But so can many other things in SQL! 🙂
Our web facing data is mostly read only but with read-write databases. We get away with using NOLOCK very well in certain cases. In fact it improved our performance to the end user by nearly 50% in many cases. Sure being able to set the filegroup to read only would have been nice but it isn't an option. Would I use it during my ETL process to check the data before insertion/updating? No way. But that is done in a staging environment and the query times are much less important.
There's also the chance of missing rows completely or reading rows twice under certain circumstances.
In the 12 years I've been using SQL Server I've never seen that happen. Can you give some sample sql to show how this is possible? If the DB allows you to put bad data in, that isn't something that NOLOCK has anything to do with. Garbage in garbage out....
Gary Johnson
Sr Database Engineer
September 10, 2008 at 1:46 am
Itzik Ben-Gan did a demo last year at PASS. It's got nothing to do with bad data. All it requires is that a page split occurs during a scan that uses nolock.
Since, in read uncommitted isolation level the storage engine will scan the table in allocation order (order of pages in a file) and not index key order, a page split can easily move half a page's rows from ahead of the scan's current point to behind it, or vis versa.
Using nelock essentially tells the query processor that you aren't concerned if the data is slightly inaccurate, making things like tat possible.
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
September 10, 2008 at 5:18 am
GilaMonster (9/10/2008)
Itzik Ben-Gan did a demo last year at PASS. It's got nothing to do with bad data. All it requires is that a page split occurs during a scan that uses nolock.Since, in read uncommitted isolation level the storage engine will scan the table in allocation order (order of pages in a file) and not index key order, a page split can easily move half a page's rows from ahead of the scan's current point to behind it, or vis versa.
Using nelock essentially tells the query processor that you aren't concerned if the data is slightly inaccurate, making things like tat possible.
That was an incredible demo. The most amazing part was how little data it took. It wasn't like he was doing page splits on a multi-terabyte database. It wasn't even a gb of data. Small data sets with scans and page splits rearranged stuff like crazy.
The thing is, it's pretty subtle. If you're not looking for it, you might not see it. I don't know about your end-users, but most of the end-users I've worked with seem to expect occasional glitches. They'll just resubmit a query if they get weird data and won't tell anyone until you ask them about it, "Oh yeah, we get that all the time."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2008 at 8:58 am
We use NOLOCK in quite a few of our reports (those that are used in production for work lists and error checking). This avoids the case where a report "hangs" people working open records, because the locks from the report frequently escalate to page locks. Many of the reports that we use NOLOCK on only look at data that is marked CLOSED, but may be in the same page as OPEN records.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
September 10, 2008 at 6:18 pm
I have found WITH(NOLOCK) very useful in reporting queries against web databases.
The reports are not critical, they are summarizing user counts and page hits rather than financial or medical info. No one gets excited if the numbers change a little from one report to the next.
The web sites are unusable if the reporting queries are run without NOLOCK.
The data is primarily from logging, it is written once and most is never updated. Inserts are always at the end of the tables, and the updates that are used are mostly fixed-length fields. Page splits are rare, and the updatable fields are not used in the reports.
The more elegant solutions would be to use snapshot isolation or to replicate the online database to a reporting server. These solutions don't come free, they would add a lot more overhead than using NOLOCK in the reporting queries.
If you know the data and the application well enough to be sure that dirty reads won't cause problems (can you bet your job on it?), then use NOLOCK.
One problem we found, however, was that "SELECT COUNT(*) FROM table WITH(NOLOCK)" can be very inaccuate on tables clustered on a GUID field (a gift from a wacko web developer). We saw counts varying randomly by about 10% on repeated executions, even with no other activity in the table. This was a while back, it may have been SQL 2000 32-bit.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply