SQL Server Performance

nolock hint when there is isolation level given

Discussion in 'SQL Server 2005 General Developer Questions' started by umimetha, Apr 9, 2007.

  1. umimetha New Member

    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
  2. DilliGrg Member

    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)

  3. umimetha New Member

    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
  4. Roji. P. Thomas New Member

  5. DilliGrg Member

    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)

  6. satya Moderator

  7. MohammedU New Member

  8. dhilditch New Member

    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
  9. satya Moderator

    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.
  10. DilliGrg Member

    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)

  11. MohammedU New Member

    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.
  12. DilliGrg Member

    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.
  13. ndinakar Member

    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/
  14. MohammedU New Member

    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 />
  15. DilliGrg Member

    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">
  16. ndinakar Member

    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/
  17. MohammedU New Member

    We too use NOLOCK hints many places...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

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

    I was having a discussion with SQL dev. team in Reading today and mentioned this point, he suggested to keep using what it is so far, but in the future service pack release or next SQL release it will be taken care by system automatically.

    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.
  19. MohammedU New Member

    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.
  20. satya Moderator

    Can't talk much on next SQL version due to NDA [<img src='/community/emoticons/emotion-6.gif' alt=':(' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
  21. MohammedU New Member

    [<img src='/community/emoticons/emotion-4.gif' alt=':p' />][<img src='/community/emoticons/emotion-4.gif' alt=':p' />][<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<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 />
  22. satya Moderator

    I can only be able to deliver the information when discussed such tasks with SQL dev. team and other than that anything about next SQL release wait until Tech-ed. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>

Share This Page