InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 06:49:04 GMT20http://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxI 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.Wed, 10 Sep 2008 18:18:39 GMTScott Colemanhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxWe 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.Wed, 10 Sep 2008 08:58:29 GMTRussell Shillinghttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspx[quote][b]GilaMonster (9/10/2008)[/b][hr]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.[/quote]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."Wed, 10 Sep 2008 05:18:43 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxItzik 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.Wed, 10 Sep 2008 01:46:30 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxIMHO, 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.[quote]There's also the chance of missing rows completely or reading rows twice under certain circumstances.[/quote]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.... Tue, 09 Sep 2008 15:13:35 GMTGary Johnson-259336http://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxI'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.Tue, 09 Sep 2008 13:20:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspx[quote][b]jcrawf02 (9/9/2008)[/b][hr]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?[/quote]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.Tue, 09 Sep 2008 12:23:15 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxHooey.If it's a reporting system, and only a reporting system, you should mark the database itself as read only.Tue, 09 Sep 2008 12:03:46 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxOk, 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?Tue, 09 Sep 2008 10:43:40 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic563902-338-1.aspx[quote]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.[/quote]I disagree with that. The savings is certainly under 1% of the net cost, probably well under 0.01%.[quote]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.[/quote]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.[quote]Yeah, MAXDOP has been useful situationally. Although if I see a lot of queries suffering from parallelism, I'll raise the threshold. [/quote]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!! :cool:Tue, 09 Sep 2008 08:03:45 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxYeah, MAXDOP has been useful situationally. Although if I see a lot of queries suffering from parallelism, I'll raise the threshold.Tue, 09 Sep 2008 06:07:09 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxThe 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.Tue, 09 Sep 2008 03:26:41 GMTMark Staceyhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxI 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.Mon, 08 Sep 2008 05:59:02 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxJust 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!Fri, 05 Sep 2008 15:29:51 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxI 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.Fri, 05 Sep 2008 08:23:36 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxNOLOCK does have its uses! The main problem is that many tend to abuse it or use it where is not really appropriate.Thu, 04 Sep 2008 14:22:26 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxHonestly, in my opinion, if you do your job right on the SQL syntax, DB design and indexing a NOLOCK statement shouldn't be necessary in any environment.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.Yes, there will be the occasional case where nolock is necessary. The problem is that it is vastly overused with people adding it to every query without the slightest knowledge of what it actually means or the possible problems they could be causing for themselves.Thu, 04 Sep 2008 14:17:44 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspx[quote][b]bcronce (9/4/2008)[/b][hr]It is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.If I'm wrong, let me know, because I thought this is how it worked.edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?[/quote]I think Grant hit on the point pretty well. If it DOES help that much, then there's some other underlying issue you're glossing over. To me NOLOCK is the equivalent of a medic treating someone, saying:"Now I'm going to ignore the gunshot wound you have, and just fix everything ELSE, okay?"Thu, 04 Sep 2008 14:12:15 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxThat's what I thought as well... So except for where exact reporting is required, if you do your job right on the SQL syntax and DB design a NOLOCK statement can only help in CRM environments...Thats where my experience is, I have not dealt with extremely high transaction enviroments.Thu, 04 Sep 2008 13:54:55 GMTparacksonhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxIt is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.If I'm wrong, let me know, because I thought this is how it worked.edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?Thu, 04 Sep 2008 13:50:31 GMTbcroncehttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxYeah, I've run into that one a few times too.Thu, 04 Sep 2008 12:51:27 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspx[quote][b]Grant Fritchey (9/4/2008)[/b][hr] Look at the graphical execution plan, they said. It's all index seeks and nested loop joins... [/quote]I had a dev that wanted to use the LOOP hint in all his queries, because the nested loop is the 'fastest' join type. I gently pointed out to him that there are conditions where the other two join types are appropriate. They're in the product for a reason.Thu, 04 Sep 2008 12:44:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxFrom where I sit, NOLOCK is a crutch, not unlike DISTINCT, that can, in the short term, "fix" issues that are better approached by the hard slog of understanding how to appropriately write TSQL, how to structure the database, how to index the database, etc. The first time someone makes an important business decision based on missing or duplicate data... Or worse still, what if you're writing queries against medical data. I'd sure hate to be responsible for killing people.Query hints in general are very dangerous things. We had a consultant fix one query with a FAST 1 query hint. The programming team involved proceded to put FAST 1 on EVERY single query inside EVERY procedure. Later, they were experiencing serious performance problems and I looked at their queries. When I saw the FAST 1, I started complaining. Ah, but they had an answer. Look at the graphical execution plan, they said. It's all index seeks and nested loop joins... Unfortunately, the way FAST 1 works is to create two executions, one to return that first row and a second to return everything else. Yes, the first row was coming back faster, the rest was coming back slower. Just removing the FAST 1 and allowing the optimizer to pick good execution plans resulted in a 5x increase.I don't trust query hints, especially when they're applied as "best practice."Thu, 04 Sep 2008 12:15:51 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxJust my 5 cents.There is no prevailing wisdom. There is a situational wisdom.You are bringing it from a conversation link where you are suggesting to use a NOLOCK in a stored procedure that is ran on the heavy OLTP environment for a reporting?First of all, running reporting on OLTP is a faulty design.I would assume then that running NOLOCK on a sproc for reports will simply return a wrong data.Thu, 04 Sep 2008 10:56:37 GMTGlen Sidelnikovhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspx[quote][b]GilaMonster (9/4/2008)[/b][hr]If you know that a set of tables are static and hence locks are an unnecessary overhead, then put those tables into a separate filegroup and mark that filegroup as readonly. SQL will never take a lock on a table in a read-only filegroup.[/quote]nice..Thu, 04 Sep 2008 10:08:11 GMTparacksonhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxIf you know that a set of tables are static and hence locks are an unnecessary overhead, then put those tables into a separate filegroup and mark that filegroup as readonly. SQL will never take a lock on a table in a read-only filegroup.Thu, 04 Sep 2008 10:05:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxNolock reduces query time in two ways. First, it eliminates the establishment of a lock, which means less processing time. Second, it allows the select to read data that updates/deletes aren't done with, instead of waiting for the update/delete to finish.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.Personally, I would prefer accurate data slightly slower over garbage that's fast, in almost all circumstances.Nolock is pretty much the database equivalent of always eating at McDonald's, instead of doing your own cooking. It's fast, easy, etc., and it will probably end up killing you eventually. At the very least, make sure to budget for blood-pressure medication and a good possibility of bypass surgery. Same principles apply to Nolock. Make sure, if you use Nolock all over the place, that company execs know that the reports they are using might be just plain wrong, and that they budget for expensive mistakes because of that; and make sure that you yourself budget for a potential unemployment period if that happens.Thu, 04 Sep 2008 09:55:18 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxIntersting...From what I have seen sometimes (yet rare) calling out the actual index has had a marked improvement on the execution time.But except for your "dirty read" comment (which is new knowledge to me..thank you) NOLOCK has been one of the things that I would do to a query, especially one with multiple joins, that would always show a decrease in the overall processing time.Thanks for the eye opener however.Thu, 04 Sep 2008 09:44:52 GMTparacksonhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspx[quote][b]parackson (9/4/2008)[/b][hr]Calling out an index has not always been productive but no matter what NOLOCK would always speed up a query...[/quote]I've already put in my 2 cents on nolock... I'll debate something else.RE the quoted line, do you mean using a hint to force a particular index? If so...Index hints (and other hints in general) are massively overused. If SQL's not picking the index that you think is optimal, there is usually a good reason why. It could be something like a cardinality inaccuracy that's leading to inaccurate costs, the query could be written in such a way as to make the index less attractive or it could be that the index you think is optimal actually isn't. 95% of the time, the optimiser does actually know best (especially in the later versions of SQL). In those few, rare cases where hinting an index does lead to a performance improvement and the query is written in the absolute optimal way, then document carefully and test carefully and regularly. The index that's good this week may not be so good in a couple of weeks time when the data volumes and distribution have changed.A query hint is not a suggestion, it's an order. By using them you are forcing the optimiser to do what you say and preventing it from even evaluating plans that may be far more optimal than the one it's actually going to use.Personally, I've never found it necessary to use an index hint in a real system and in all but one of the cases where I've seen them used, removing the hint improved performance.Thu, 04 Sep 2008 09:38:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic563902-338-1.aspxGila brought some attention to something that is rather interesting to me and was hoping to spark a discussion.... ([url]http://www.sqlservercentral.com/Forums/Topic563258-338-2.aspx#bm563628[/url])Myself I have not worked in an enviroment that has a massive amount of writes... Just reads...I have noticed that if I put NOLOCK on a table it has always had a dramatic improvement on the query execution time.... Calling out an index has not always been productive but no matter what NOLOCK would always speed up a query...[quote]Just bear in mind that NOLOCK means dirty reads, so if the data's changing a lot, you may read something you don't want to. There's also the chance of missing rows completely or reading rows twice under certain circumstances.Nolock essentially means to SQL "I don't care if my result set is slightly inaccurate."If the data is not changing a lot, then you are probably not running into lock waits and hence nolock won't help much. Nolock is not a silver bullet to be added to every select statement without careful consideration. [/quote]So with this all said what is the prevailing wisdom?Thu, 04 Sep 2008 09:11:45 GMTparackson