nolock hint when there is isolation level given

Hi,

I have a question on giving noloc hint in select statements.

I am to enhance the performance of some SPs.

I notice that all the SPs start with giving the isolation level by "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" .

However it is my opinion still we can improvethe speed of queries if we give the nolock hint to the select queries given in this SP.

Could anyone please clarify if my attempt will be fruitful???

A response posted ASAP will be highly appreciated.

Thanks in advance,

UMI

quote:Originally posted by umimetha

Hi,

I have a question on giving noloc hint in select statements.

I am to enhance the performance of some SPs.

I notice that all the SPs start with giving the isolation level by "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" .

However it is my opinion still we can improvethe speed of queries if we give the nolock hint to the select queries given in this SP.

Could anyone please clarify if my attempt will be fruitful???

A response posted ASAP will be highly appreciated.

Thanks in advance,

UMI

Setting the isolation level
"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" equals to table hint (NOLOCK) for entire batch. If you want to use nolock for certain table only then you can remove isolation level and use nolock where necessary. Putting additional nolock on top of read uncommited isolation level won't help your performance. You need to tune the query instead. Post your query if you can.






Thanks,

Name
---------
Dilli Grg

(1 row(s) affected)

Hi,
Thanks a lot Dilli for your reply.
The SP looks something like this.

=================================================
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ValErr int, @ValString varchar(100)

SELECT
a.AID,
ap.ClientID,
a.UserID,
a.QuoteID,
a.ParentPID as PID,
pp.ParentPName as PDesc,
CASE
WHEN A.DENTC IS NOT NULL THEN dbo.ae_fn_IsDENTC(A.PID)
ELSE NULL
END [DENTC],
a.CoverageTypeID,
dbo.fn_CoverageTypeAltDesc(a.CoverageTypeID, dbo.ae_fn_GetStateIDByAID (a.AID) ) AS CoverageTypeDesc,
a.AStatusID,
ast.AStatusDesc,
a.AppVersionID,
a.ZipCode,
(select min(ST) from CountyZip (nolock) where Zip = a.ZipCode) as StateCode,
a.NumChildren,
a.ReqEffectiveDate,
a.SignatureDate,
a.AppReceivedDate,
a.ActualEffDate,
a.SubmissionMethod,
a.ReferralID,
a.MailCode,
dbo.ae_fn_GetFinalRate(a.AID) as FinalRate,
ap.FirstName as PrimaryFirstName,
ap.LastName as PrimaryLastName,
ap.MI as PrimaryMI,
a.MedicalPlatform,
a.StateCode,
a.ATypeID,
a.ProducerId,
a.GAId,
a.SalesRepId

FROM
A a

JOIN Client ap ON ap.AID = a.AID
AND ap.ClientTypeID = 1
LEFT JOIN HPParentP pp
ON a.ParentPId = pp.ParentPID
and
a.MedicalPlatform = pp.MedicalPlatform
and
a.StateCode = pp.StateCode

JOIN LUCoverageType ct ON a.CoverageTypeID = ct.CoverageTypeID

JOIN LUAStatus ast ON a.AStatusID = ast.AStatusID

LEFT OUTER JOIN HPP P ON P.PID = A.PID
WHERE
a.UserID = @UserID
============================================================================


as you can see (select min(ST) from CountyZip (nolock) where Zip = a.ZipCode) is given here. I want to know why it is again given explicitly. Isn't this no lock condition is already created by "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"

Please clarify if I am wrong.

Thanks in advance

NOLOCK does n ot have any impact when you are in READ UNCOMMITTED ISOLATION Level.

Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

quote:Originally posted by umimetha

Hi,

as you can see (select min(ST) from CountyZip (nolock) where Zip = a.ZipCode) is given here. I want to know why it is again given explicitly. Isn't this no lock condition is already created by "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"

Please clarify if I am wrong.

Thanks in advance

Yes, no need to use table hint again in this case. I can see that this SP is calling multiple functions, check to see if you can eliminate any of these functions by using straight select. This might improve performance as the functions are generally slow.



Thanks,

Name
---------
Dilli Grg

(1 row(s) affected)

Either code doesn't know the use of NOLOCK and Read_Uncommitted isolation level difference...or it could be a just regular practice...

Check the following blog the effect NOLOCK...
http://blogs.msdn.com/sqlcat/archiv...s-might-be-missed-if-nolock-hint-is-used.aspx


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

This NOLOCK double counting effect is the same under Read Uncommitted since they are the same lock type.

The double counting can happen if say a clustered index has been updated - this can cause pages to change order AS the NOLOCK query is running - say the page moves to AFTER the current point of the NOLOCK scan then rows will be counted twice.

Anyway, original point stands - nolock and read uncommitted are the same.

David Hilditch
MCSD, MCSE, MCDBA, MCITP, MCTS

Add flight search to your website for free
www.skyscanner.net

quote:Originally posted by satya

In SQL 2005 its better to "Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause" as it is deprecated.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Satya,
I wasn't aware of this. Can you be more specific on this? What is the alternative for this then? Are you suggesting that we should use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
at the beginning of query batch instead of using NOLOCK on each individual table?



Thanks,

Name
---------
Dilli Grg

(1 row(s) affected)

I don't think it is for SELECT statements...

As per the BOL

Catogery: Table hints

Deprecated feature: Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement when applied to the target table of the statement.

Replacement: Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.

SQL Server 2005 Books Online
Deprecated Database Engine Features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143729.aspx

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Ok, i did find this information in the msdn site but no alternatives offered specifically.

http://msdn2.microsoft.com/en-us/library/ms143729.aspx



Thanks,

Name
---------
Dilli Grg

(1 row(s) affected)


I was still typing when MohammedU posted. Good to know these info though.

from BOL:

Note:
Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of Microsoft SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.



In SQL Server 2005 you can minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications by using either of the following:

The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set ON.


The SNAPSHOT isolation level.

--------------------

So it only applies to "..target table of an UPDATE or DELETE..."

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

Looks like very body is on the same page at the same time [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

Yes, that's why I edited my post with comments. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Thanks,<br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">

Yeah..we use so many NOLOCKS..almost everywhere that I was a little startled when I read that..

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

We too use NOLOCK hints many places...

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

That is good and hoping to see.... but...
Any idea how it is going to handle becuase sql server default isolation is READ COMMITTED?
Without giving special instruction to sql, how the sql is going to use READ UNCOMMITTED isolation level.

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

[<img src='/community/emoticons/emotion-4.gif' alt='' />][<img src='/community/emoticons/emotion-4.gif' alt='' />][<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />