need opinion on NOLOCK

  • Grant Fritchey (5/3/2010)


    GilaMonster (5/3/2010)


    Grant Fritchey (5/3/2010)


    Also, having four completely different queries inside the IF statement pretty much guarantees you're likely to see recompiles.

    Why? SQL won't recompile when a different branch is taken. It'll still use the cached (and probably bad) plan that it generated on the first execution.

    If different objects are referenced, I'm pretty sure it recompiles, but I could be wrong (wouldn't be the first time... today).

    Don't think so. AFAIK, the entire thing compiles the first time, regardless of which branch will actually be executed (optimiser can't tell that). will test, worth a blog post I think.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (5/3/2010)


    Grant Fritchey (5/3/2010)


    GilaMonster (5/3/2010)


    Grant Fritchey (5/3/2010)


    Also, having four completely different queries inside the IF statement pretty much guarantees you're likely to see recompiles.

    Why? SQL won't recompile when a different branch is taken. It'll still use the cached (and probably bad) plan that it generated on the first execution.

    If different objects are referenced, I'm pretty sure it recompiles, but I could be wrong (wouldn't be the first time... today).

    Don't think so. AFAIK, the entire thing compiles the first time, regardless of which branch will actually be executed (optimiser can't tell that). will test, worth a blog post I think.

    Yeah, sounds like I might be laboring under yet another long line of bad assumptions. I'll try to post something on this soon(ish).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Tested and confirmed

    CREATE PROCEDURE TestingIfREcompiles (@Option INT)

    AS

    IF @option = 1

    SELECT * FROM dbo.LargeTable

    ELSE

    SELECT * FROM dbo.LargeTable2

    GO

    First execution passing 1 (to get first branch) generates a cache miss and a cache insert

    Second execution (passing 2 to get 2nd branch) gets a cache hit. No cache remove, no cache insert (which would be there if there was a recompile)

    Blog fodder for me this week or next. Good. I was short of ideas.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Grant Fritchey (5/3/2010)


    Also, having four completely different queries inside the IF statement pretty much guarantees you're likely to see recompiles. If you need to set up this kind of IF clause, then you need to create four new seperate stored procedures, each with the appropriate query, and call those procedures from this wrapper proc. You'll get radically less recompiles that way.

    You're guaranteed a table scan if you put any function on a column as part of a WHERE clause or JOIN.

    Other than, as Gail says, please post the details for more detailed help.

    Looking at the WHERE clauses, the functions are all on the right so I think these are still SARGable and will use the appropriate indexes if available.

  • Hi Gail and other experts

    How does "ROWLOCK" work? I have this situation:

    User1 - Query Analyzer

    begin transaction

    update t1 with (rowlock) set c1 = 10 where c1 = 5

    User 2 - Query Analyzer

    update t1 with (rowlock) set c1 = 20 where c1 = 15

    Since the query executed by User1 is done with begin transaction the which User2 fires fails to execute until I do rollback or commit transaction in User1 - Query Analyzer.

    According to me SQL Server should only do Row Level locking and should allow the other query to execute, but it does not execute. Can you please explain to me why?

  • samsql (5/5/2010)


    Hi Gail and other experts

    How does "ROWLOCK" work? I have this situation:

    User1 - Query Analyzer

    begin transaction

    update t1 with (rowlock) set c1 = 10 where c1 = 5

    User 2 - Query Analyzer

    update t1 with (rowlock) set c1 = 20 where c1 = 15

    Since the query executed by User1 is done with begin transaction the which User2 fires fails to execute until I do rollback or commit transaction in User1 - Query Analyzer.

    According to me SQL Server should only do Row Level locking and should allow the other query to execute, but it does not execute. Can you please explain to me why?

    You really should start a new thread instead of hijacking this one.

  • Hi Lynn i have started a new thread for this but there is no reply

Viewing 7 posts - 16 through 21 (of 21 total)

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