Question about query hint nolock

Question

Question about query hint nolock

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Eric M Russell (4/15/2013)
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/

It also opens the possibility of duplicating data (the original data and the updated data) as well as having bad data that might be rolled back. Like Gail said, probably OK for dashboards and the like but no good where any type of accuracy is important.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)