SQL Server Performance

Need a suggestion for using Nolock and Readpast

Discussion in 'SQL Server 2005 General Developer Questions' started by sasivashok, Dec 21, 2010.

  1. sasivashok New Member

    In my project i am in critical situation. I am having two scenarios. In single application, multiple user update and retrieve the data from single table. First one is when the user insert the data at the same time another user retrieve the data in same table from different machine using same application. For ex, a healthcare product a user insert information to a table and another user retrieve his information that already he stored in the same table. I am using Transaction. when insert, i am using upload the file. If the file size is large, the user inserting to the table. Begin transaction is going but still it is not committed. At the particular time another user retrieve data from same table. Problem if i am using NOLOCK, the retrieving process is executing still the inserting query till committed.So the user need to wait to get his data.My clients are feeling that this is performance issue.
    Second one is when i am using READPAST, it is working that it is lock the particular row and the user can retrieve the data. But in some other scneario if the user is updated na i cant show the particular row until it is committed. Please give the clear solution for performance tune and what is the problem will come while using NOLOCK and READPAST.
    It is very urgent!!!!!hope yours reply!!!
  2. satya Moderator

    I believe you need a good understanding about READPAST and locking hints when a highly transactional process is involved.
    Checkhttp://www.sql-server-performance.com/reducing_locks_hints.asp for more informatino.

    You can use query hints with a transaction but you're looking at scalability issues. You could use the READPAST locking hint. Keep in mind that this is onlyusefull in some very specific circumstances. If you use this locking hint in a statistical or reporting query, you'll miss some data without having any indication if and how much data you miss. At least with NOLOCK, you will get all of your rows back - even if the data is potentially dirty (which may not be a good way to present data to end-user).
    In general (in your situation) READPAST may not help if SELECTs that performs table scans block UPDATE statements, the SELECT gets a lock on table level, and the updaters will have to wait. READPAST makes sense if selects are fast, but your UPDATE/INSERT operations are complex and long-running.

    If you are looking to provide a performance to SELECT users then think adding supporting/covering indexes to the frequently executed queries. For UPDATE/INSERT processes make sure to captuer smaller transactions that taking millions of rows at the same time. As you are using SQL2005 then look at Books ONline (http://sqlserver-qa.net/blogs/tools...005-service-pack-4-rtm-test-and-download.aspx) for SNAPSHOT Isolation level which is suitable in your scenario.

Share This Page