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 Saturday, April 13, 2013 9:12 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 35,770, Visits: 32,436
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."

(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 #1442077
Posted Sunday, April 14, 2013 10:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442111
Posted Sunday, April 14, 2013 10:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 1,816, Visits: 5,912
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


  • 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 #1442113
    Posted Sunday, April 14, 2013 10:56 AM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Yesterday @ 9:13 AM
    Points: 40,609, Visits: 37,070
    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 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 #1442114
    Posted Sunday, April 14, 2013 11:11 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:07 PM
    Points: 1,816, Visits: 5,912
    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...


    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 #1442116
    Posted Monday, April 15, 2013 11:09 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 1:23 PM
    Points: 1,792, Visits: 5,041
    ulisseslourenco (4/12/2013)
    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.

    There are many different reasons for blocking of readonly queries, but it basically boils down to another process that is holding update locks within an uncommitted transaction. Here are a few reasons:
    - Update statements based on a join between multiple tables.
    - ETL processes that frequently insert or mergin large numbers of rows.
    - Applications like MS Access that open scrollable and updatable cursors with pessimistic locking.

    What the NOLOCK hint (same as "read uncommited" isolation level) does it allow your query to return back a result based on uncommitted updates rather than waiting for the updates to commit.

    Try to identify what statements or batches are holding open uncommitted transactions for an extended period of time. Below is an article with a few methods of reporting this:

    Different techniques to identify blocking in SQL Server
    http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/
    Post #1442418
    Posted Monday, April 15, 2013 4:00 PM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: Yesterday @ 8:47 PM
    Points: 35,770, Visits: 32,436
    Eric M Russell (4/15/2013)
    ulisseslourenco (4/12/2013)
    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.

    There are many different reasons for blocking of readonly queries, but it basically boils down to another process that is holding update locks within an uncommitted transaction. Here are a few reasons:
    - Update statements based on a join between multiple tables.
    - ETL processes that frequently insert or mergin large numbers of rows.
    - Applications like MS Access that open scrollable and updatable cursors with pessimistic locking.

    What the NOLOCK hint (same as "read uncommited" isolation level) does it allow your query to return back a result based on uncommitted updates rather than waiting for the updates to commit.

    Try to identify what statements or batches are holding open uncommitted transactions for an extended period of time. Below is an article with a few methods of reporting this:

    Different techniques to identify blocking in SQL Server
    http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/


    It also opens the possibility of duplicating data (the original data and the updated data) as well as having bad data that might be rolled back. Like Gail said, probably OK for dashboards and the like but no good where any type of accuracy is important.


    --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 #1442523
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse