Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question about query hint nolock Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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.
Post #1441653
Posted Friday, April 12, 2013 6:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1441665
Posted Friday, April 12, 2013 3:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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?

Post #1441945
Posted Friday, April 12, 2013 4:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1441958
Posted Friday, April 12, 2013 8:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 35,265, Visits: 31,755
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1441999
Posted Friday, April 12, 2013 10:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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.
Post #1442007
Posted Saturday, April 13, 2013 1:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1442015
Posted Saturday, April 13, 2013 5:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 3:01 PM
Points: 28, Visits: 282
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
Post #1442025
Posted Saturday, April 13, 2013 9:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 35,265, Visits: 31,755
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. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442041
Posted Saturday, April 13, 2013 6:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 1,780, Visits: 5,750
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1442069
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse