none
Is "with (nolock)" a good thing?

    Question

  • I've got an application that generates a report using Crystal Reports 9. It is timing out when it runs queries against the SQL Server. At this time I am trying to determine which query is causing this behavior. The time outs occur sometimes to some users. I attempted to duplicate this behavior but the report generated in a timely fashion without any delay.

    One option that has been suggested is to add the "with (nolock)" directive to the queries, but I am not sure if this should be done so liberally. What do you all think about "with (nolock)"? Any other suggestion how I can narrow down the possible causes for the time outs? Thank you! Saga

    Friday, January 17, 2014 10:12 PM

Answers

  • No, NOLOCK is nothing you should put in your application code. And NOLOCK is definitely not a good thing, if you don't understand the implications. And they are difficult to grasp even for an expert. With NOLOCK, this can happen:

    1) You read uncomitted data. This may be acceptable for reports that only shows summaries and trends. It's certainly entirely unacceptable for a report from a general ledger.

    2) You fail to read committed data (because data is moved while the query is running). This can be grossly unacceptable even for a report that only shows rough values, because you leave out facts that the user expects to see.

    3) The query can explode in the face of the user with error 601, data movement during NOLOCK scan.

    All these three points are also likely to cause a nightmare for the support desk, because error reports will not be easily reproduceable.

    If you have blocking problems with your report, consider any of the snapshot isolation modes. Either have the database set to READ_COMMITTED_SNAPSHOT, and they you don't need to more. Or set ALLOW_SNAPSHOT_ISOLATION ON, and run your queries in the SNAPSHOT isolation level.

    And, oh, take out that command timeout. By default it is 30 seconds in most API. If you set it to 0, you might not even have to do more, as users may accept that the reports run a little longer at times. No, I don't know where you set the command timeout from Crystal.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Sunday, January 19, 2014 3:25 AM
    • Marked as answer by SGarciaV Tuesday, January 21, 2014 5:48 PM
    Friday, January 17, 2014 11:36 PM
  • I've got an application that generates a report using Crystal Reports 9. It is timing out when it runs queries against the SQL Server. At this time I am trying to determine which query is causing this behavior. The time outs occur sometimes to some users. I attempted to duplicate this behavior but the report generated in a timely fashion without any delay.

    Hi SGarciaV,

    I think we should narrow down the timeout issue in your case. Generally, when a user view a report from application, it will contain "Data Retrieval", "Processing" and "Report Rendering" stages. I'm not good at crystal reports, but here are some articles for your referece, please see:
    http://www.codeproject.com/Tips/69288/Crystal-Reports-5-Tests-for-Top-Performance
    http://www.recrystallize.com/merchant/cortex/report-analyzer.htm

    In addition, we can correlate SQL Server Profiler with Performance Monitor to perform performance tuning when run the report at client. For details, please see:
    https://www.simple-talk.com/sql/database-administration/correlating-sql-server-profiler-with-performance-monitor/

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by SGarciaV Tuesday, January 21, 2014 5:48 PM
    Monday, January 20, 2014 1:34 PM

All replies

  • The use nolock will help you to prevent from some locking situations. But it has many adverse effect on the data.

    Try to run the query in SSMS, generate the execution plan and see what can be done.

    Refer the below link

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

    -Prashanth

    Friday, January 17, 2014 10:31 PM
  • (NOLOCK) is good to have when you run the select statement and it's always good to have.

    We use to have separate reporting server and separate reporting database usually it's read only data. In this case even if I don't place the (Nolock) it's not going to cause any issues... but always nice to add it So no need to worry...

    Thanks,

    JKK


    Friday, January 17, 2014 11:18 PM
  • No, NOLOCK is nothing you should put in your application code. And NOLOCK is definitely not a good thing, if you don't understand the implications. And they are difficult to grasp even for an expert. With NOLOCK, this can happen:

    1) You read uncomitted data. This may be acceptable for reports that only shows summaries and trends. It's certainly entirely unacceptable for a report from a general ledger.

    2) You fail to read committed data (because data is moved while the query is running). This can be grossly unacceptable even for a report that only shows rough values, because you leave out facts that the user expects to see.

    3) The query can explode in the face of the user with error 601, data movement during NOLOCK scan.

    All these three points are also likely to cause a nightmare for the support desk, because error reports will not be easily reproduceable.

    If you have blocking problems with your report, consider any of the snapshot isolation modes. Either have the database set to READ_COMMITTED_SNAPSHOT, and they you don't need to more. Or set ALLOW_SNAPSHOT_ISOLATION ON, and run your queries in the SNAPSHOT isolation level.

    And, oh, take out that command timeout. By default it is 30 seconds in most API. If you set it to 0, you might not even have to do more, as users may accept that the reports run a little longer at times. No, I don't know where you set the command timeout from Crystal.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Sunday, January 19, 2014 3:25 AM
    • Marked as answer by SGarciaV Tuesday, January 21, 2014 5:48 PM
    Friday, January 17, 2014 11:36 PM
  • I've got an application that generates a report using Crystal Reports 9. It is timing out when it runs queries against the SQL Server. At this time I am trying to determine which query is causing this behavior. The time outs occur sometimes to some users. I attempted to duplicate this behavior but the report generated in a timely fashion without any delay.

    Hi SGarciaV,

    I think we should narrow down the timeout issue in your case. Generally, when a user view a report from application, it will contain "Data Retrieval", "Processing" and "Report Rendering" stages. I'm not good at crystal reports, but here are some articles for your referece, please see:
    http://www.codeproject.com/Tips/69288/Crystal-Reports-5-Tests-for-Top-Performance
    http://www.recrystallize.com/merchant/cortex/report-analyzer.htm

    In addition, we can correlate SQL Server Profiler with Performance Monitor to perform performance tuning when run the report at client. For details, please see:
    https://www.simple-talk.com/sql/database-administration/correlating-sql-server-profiler-with-performance-monitor/

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by SGarciaV Tuesday, January 21, 2014 5:48 PM
    Monday, January 20, 2014 1:34 PM
  • If these are not real-time reports as most of the case, suggest to your management to restore previous night backup as a reporting database. That way reporting will not conflict with OLTP activities.


    Kalman Toth Database & OLAP Architect SELECT Query Video Tutorial 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, January 20, 2014 2:03 PM
  • Hello,

    I would like you to focus on Idea of using Snapshot or read committed snapshot isolation level not server wide but in query as pointed out by Erland.Remember to also look at your tempdb when you have implemented it.

    when you are running select on history data may be one month old data you can use NOLOCK but be sure data is not updated in any case otherwise problem has been pointed out as shown by Erland.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, January 20, 2014 3:13 PM
  • Thanks JKK for your reply. Unfortunately the report is coming off the current db, or as a poster called it, the OLTP DB. This is my concern with "with (nolock)". Regards, Saga

    Monday, January 20, 2014 7:14 PM
  • Thanks Erland. I had read about items 1 and 2, but was unaware of #3.

    Since the same app can be configured to use different data sources (for reports) it uses a generic CR9 report. The app is responsible for querying the DB and generating the record set that is passed to the CR9 report.

    "No, I don't know where you set the command timeout from Crystal."

    I don't either :-). Since the app controls the data access, setting a command timeout in CR has never been an issue. At times the traffic to the database becomes intense and people tend to generate reports during this peak period, so the apps have a timeout larger than the 30 second default, but I definitely do not think that the solution to this particular issue is simply incrementing the timeout.

    Clearly this problem goes beyond this one report. I plan on using the resources provided in this thread to determine a suitable path to resolve this issue as best as possible. Again thank you for your time and assistance. Regards, Saga

    Monday, January 20, 2014 7:36 PM
  • Thank you Kalman. Yes, these are real time reports, so getting the data from a previous back up is not the way to go. Saga
    Monday, January 20, 2014 7:39 PM