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/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho