SQL Server Performance Forum – Threads Archive

WITH(NOLOCK) and Performance

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.

http://www.sql-server-performance.com/reducing_locks_hints.asp
http://www.sql-server-performance.com/lock_contention_tamed_article.asp Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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 />

At some rare scenarios you can get problems even though you use NOLOCK. Sometimes you can get the error: "Could not continue scan with NOLOCK due to data movement". http://support.microsoft.com/default.aspx?scid=kb;en-us;235880

]]>