Question about query hint nolock

Question

Question about query hint nolock

ulisseslourenco

SSC-Addicted

Points: 417
More actions
April 12, 2013 at 6:00 am

#394572

Hi people,
I have a question about nolock. I work in a company that there is a rule: all select query we must put nolock. If we don't put it the query execution becomes slowly. don't sqlserver get manage it transactions?
Is there any configuration or parameter to avoid using nolock all time when I run a select statement?
Best regards.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There is, but to be honest you shouldn't be using nolock everywhere. It's not a go-faster switch, it is not a performance tuning technique, it is not necessary to make queries fast (if you have problems with blocking, you need to tune the queries, not hide the symptoms)

Nolock allows incorrect data, not just dirty reads, duplicate rows, missed rows and other similar fun. I suggest you pass this onto your colleagues.

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

I hope that's not financial data that you're querying there. Do all the users know and accept that the data they see could be very wrong?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 2

GilaMonster (4/12/2013)
There is, but to be honest you shouldn't be using nolock everywhere. It's not a go-faster switch, it is not a performance tuning technique, it is not necessary to make queries fast (if you have problems with blocking, you need to tune the queries, not hide the symptoms)
Nolock allows incorrect data, not just dirty reads, duplicate rows, missed rows and other similar fun. I suggest you pass this onto your colleagues.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
I hope that's not financial data that you're querying there. Do all the users know and accept that the data they see could be very wrong?

Thanks for answer me

In my company, we are querying financial data, because we support Collection Agencies. I don't know if my co-works know about nolock issues as well.

However I must to prove the company managers (Project Manager) that nolock is worst solution.

Could you mind to give me some examples?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Did you read the blog post I referenced and all the articles it references?

Basically, you're querying financial data and creating reports that I assume will affect amounts and bills and using a hint that the SQL documentation clearly states

Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

Depending on the financial laws in your country, that could cause your company some serious problems, basically they could be completely misrepresenting how much money those collection agencies should be collecting.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

If you're really trying to prevent blocking, then try the READPAST hint instead of the troublesome NOLOCK hint.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 5

Jeff Moden (4/12/2013)
If you're really trying to prevent blocking, then try the READPAST hint instead of the troublesome NOLOCK hint.

Hi

I read about readpast command and it isn't good as nolock. It has problems as it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ReadPast = skip locked rows. Also rather bad for accurate results. Find for stuff like dashboards or areas where close to accurate is good enough

If you're having problems with blocking, I recommend tuning queries as a first step and one of the snapshot isolation levels as the second step if still necessary.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 7

GilaMonster (4/13/2013)
ReadPast = skip locked rows. Also rather bad for accurate results. Find for stuff like dashboards or areas where close to accurate is good enough
If you're having problems with blocking, I recommend tuning queries as a first step and one of the snapshot isolation levels as the second step if still necessary.

Thank you twice.

I will read that link and try to talk about nolock issues in my company

I hope I 'd get convict them.

Best regards

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

GilaMonster (4/13/2013)
ReadPast = skip locked rows. Also rather bad for accurate results. Find for stuff like dashboards or areas where close to accurate is good enough
If you're having problems with blocking, I recommend tuning queries as a first step and one of the snapshot isolation levels as the second step if still necessary.

Of course you're absolutely correct and, considering the general nature of the original post, my unadorned suggestion to use READPAST is actually inappropriate. I should have been much more specific instead of being in such a hurry. :blush: My apologies.

For most types of reporting, updating of other tables, etc, etc, I agree with Gail and 99% of the rest of the world that WITH(NOLOCK), WITH(READPAST), and other such trickery is the wrong thing to do. As stated, the best thing to do is to fix the queries/processes so that when there is necessary blocking, it only lasts for the briefest of moments. Performance is in the code.

There are only two general places where I'll use READPAST. As Gail pointed out, dashboards and dashboard-like reports are one of those places. For example, we have a call-center where I work. If a supervisor wants to see who has handled what types of calls, they can call a report generator. If a call is in the process of being inserted into the table, it shouldn't be included at that particular instant that the report is being generated so READPAST works fine on this particular type of "close enough" report.

The other place I use it is on high volume OLTP "feeder" or "staging" tables. For example, we have several "audit" systems that capture changes made to groups of several related tables. The "final" audit table is used a whole lot for audit reporting so it's important to keep inserts of data not only as short as possible, but as efficient as possible. Instead of 9 (for example) very busy OLTP tables (for a given audit group) constantly inserting a row per column changed and possibly slowing down the audited processes because indexes on the audit table must also be updated during the inserts, we feed the audit data (via audit trigger) to a staging table that only has a clustered index on an IDENTITY column to keep the inserts at the logical "end" of the table. It's very quick.

We then have a scheduled job that will move successfully inserted (fully committed) rows from that staging table to the final table in larger, more efficient groups of rows. We also want this to be quick and not wait on any uncommitted rows being inserted and we don't want to actually use any uncommitted rows because we also have to delete the rows that we moved to the final audit table. NOLOCK would be a problem here because, with NOLOCK, you do stand a chance of reading a row that might be rolled back, which also means that a change to the source table wasn't actually made and shouldn't be audited. READPAST does the job of skipping uncommitted rows perfectly, in this case. Any rows that are not picked up for movement (because they haven't been committed, yet) to the final table and deletion in the staging table are picked up on the next pass of the job that does the movement. No data is lost and no data that has been rolled back is moved nor even considered for movement. Since the rows currently being inserted are all in "one spot" in the table, there's very little locking competition for either the copy or the deletion of the rows being moved to the final table, which also lends itself to performance of the audit system.

This also helps the performance of the reporting systems on the final audit table, as well. Instead of having to wait for sometimes hundreds of transactions to clear for any given minute and with the idea that one insert of many rows is much faster than many inserts of one or two rows, the once per minute insert of many audit rows from the staging table to the final table causes very little competition for locks with the reporting systems.

As cited above, there are places where skipping uncommitted transactions are ok and will cause no loss of data but those are very rare cases. The general recommendation to avoid using NOLOCK or READPAST and the recommendation to make your queries more efficient to cause less blocking to begin with should seriously be taken to heart.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

How about this situation?

Our customers are using Third Party software, which is not supported on a database using Snapshot Isolation and which frequently holds locks for minutes or even hours, yet we still want to read the data ...

What would be the alternative to a NOLOCK hint?

For example, the product master table in this software contains static information such as description, preferred supplier, etc. as well as some aggregates, such as physical qty, due in qty etc (I know, bad design, but that is what we are stuck with and have been for over 20 years)

So, what we can see is someone modifying the static data, and the software locks the product row until the user is finished (I know, bad design again etc...). Meanwhile, someone is running a report on products and wants the description - they are blocked from reading this data by the person editing the product record.

We can't use snapshot isolation because it breaks the terms of the software support contract. What alternative to having the report use a NOLOCK is there?

Oh, did I mention that every table in this wonderful software's database is a heap? No, oops!

Any ideas?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

mister.magoo (4/13/2013)
Any ideas?

Yes, but they're probably not viable for your situation. My recommedations would all start with "Get rid of..." and "Replace the..." and end with " and kill the designer to end that branch of the gene pool." 😛

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Jeff Moden (4/13/2013)
mister.magoo (4/13/2013)
Any ideas?
Yes, but they're probably not viable for your situation. My recommendations would all start with "Get rid of..." and "Replace the..." and end with " and kill the designer to end that branch of the gene pool." 😛

I would have to agree with Jeff on this one. Sounds like a mess.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

Lynn Pettis (4/14/2013)
Jeff Moden (4/13/2013)
mister.magoo (4/13/2013)
Any ideas?
Yes, but they're probably not viable for your situation. My recommendations would all start with "Get rid of..." and "Replace the..." and end with " and kill the designer to end that branch of the gene pool." 😛
I would have to agree with Jeff on this one. Sounds like a mess.

It is a mess, but mostly works sort of....

What we also see is users running crystal reports and they are usually awful and will lock all over the place, so these are usually told to use NOLOCK just to avoid them blocking transactions, and add far as I am concerned, if their report is wrong so be it.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Do the report users need up-to-the-minute data? If not, have you considered creating a database snapshot and letting them run their reports off that?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

GilaMonster (4/14/2013)
Do the report users need up-to-the-minute data? If not, have you considered creating a database snapshot and letting them run their reports off that?

I just accidentally clicked "Report" instead of "Quote" - I hope cancelling out of that works! if not, sorry!

Anyway, we have anywhere between 30 to 100 customers that we work with who use this software (we are a consultancy, not the software developers), and I think database snapshots would be great , but unfortunately in a lot of cases, they are looking for relatively recent data, don't tend to have in house IT staff and mostly don't have the budget for anyone to actively manage their systems.

In the end, I guess what I am saying is that in these cases, the resources to do things "right" are not available...we just have to live on the dirty edge of the SQL landscape and cross our fingers... :sick:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);