SQL Server Performance

WITH(NOLOCK) and Performance

Discussion in 'Performance Tuning for Hardware Configurations' started by r_o_b_a, Aug 10, 2005.

  1. r_o_b_a New Member

    Hi,
    I have a large application, with a 12GB db. This db is OLTP and reporting (custom reporting, not OLPA) We have had locking/blocking issues, and have been adding WITH(NOLOCK) to all of our reporting queries and most of our OLTP queries. Business decision that dirty reads will not be an issue.

    We are still having performance issues. The CPU utilization is low, memory is good, # of transactions is good, re-indexing weekly, but we still get slow responses and timeouts in our ASP application. It seems that perormance degrades with usage.
    e.g. Query 1 on Developement db = 10 seconds, in production takes 40 seconds or more. (and in many cases we get odbc timeouts)

    So, aside from all the usual responses (separate oltp and reporting solutions, hardware, data partitioning etc) and am wondering if the WITH(NOLOCK) is contributing to the issues. We do see that adding the hint to a single query will make it run faster, but I am wonderint if adding it to ALL the queries is actaully a bad thing ! Does the SQL engine then work differently, and maybe not as efficent by NOT locking ?

    Everything I have read says that WITH(NOLOCK) will only affect the locking/blocking, but never mentions how/if it affects other aspects of it, or the overall performance.

    Has anyone experienced this ?

    Thanks in advance,
    rob.

  2. satya Moderator

  3. Argyle New Member

    I would start looking at other areas than SQL Server. How are the reports being built with ASP, lots of loops and extra parsing and calculations on the data that is returned? Isolate the report queries and run them in Query analyzer and compare to the ASP pages.<br /><br />What does the data access layer look like. Are you using disconncted clientside recordset or not? What kind of ADO cursor and locking options do you have on your queries in ASP. How much data are you returning, is it millions of rows? If so is all this data transfered over the wire and then the reports are created or are you using server side cursors? etc <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />
  4. brimba New Member

Share This Page