Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question about query hint nolock


Question about query hint nolock

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45071 Visits: 39906
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." :-P

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24195 Visits: 37959
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." :-P


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

Cool
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)
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2270 Visits: 7827
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." :-P


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);




  • 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

  • GilaMonster
    GilaMonster
    SSC-Forever
    SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

    Group: General Forum Members
    Points: 47241 Visits: 44377
    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
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2270 Visits: 7827
    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);




  • 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

  • Eric M Russell
    Eric M Russell
    SSCarpal Tunnel
    SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

    Group: General Forum Members
    Points: 4612 Visits: 9545
    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/


    "The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
    Jeff Moden
    Jeff Moden
    SSC-Forever
    SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

    Group: General Forum Members
    Points: 45071 Visits: 39906
    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.
    Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
    Although change is inevitable, change for the better is not.
    Just because you can do something in PowerShell, doesnt mean you should. Wink

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search