Forum Replies Created

Viewing 15 posts - 22,321 through 22,335 (of 49,552 total)

  • RE: Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

    anthony.green (11/11/2011)

    but if the dynamic sql plan is flushed from procedure cach on execution of the proc I will remove it

    No. Just the procedure's plan (which has just about nothing...

    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

    November 11, 2011 at 4:10 am

    #1406169

  • RE: SQL Server 2008 2 Node CLuster

    John Mitchell-245523 (11/11/2011)

    I'm not aware of any restrictions in Standard Edition specific to clustering (maybe the number of nodes you can have in the cluster?).

    Exactly that. Standard limits to 2-node...

    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

    November 11, 2011 at 3:48 am

    #1406160

  • RE: SQL Server 2008 2 Node CLuster

    NickBalaam (11/11/2011)

    With our current setup and version (web edition) I could simply lease another box, create a private lan between the two and set up log shipping. Unfortunately our requirements...

    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

    November 11, 2011 at 3:47 am

    #1406159

  • RE: Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

    Without the pk, you were getting table scans, which aren't pretty for updates and concurrency. The pk should fix this completely.

    btw, just something I noticed. What's the reason for with...

    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

    November 11, 2011 at 3:44 am

    #1406157

  • RE: Please Help

    Read up on Count and Group By.

    Hint: Start with this

    SELECT District, CASE Gender WHEN 'Male' THEN 1 ELSE 0 END as Male, CASE Gender WHEN 'Female' THEN 1 ELSE 0...

    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

    November 11, 2011 at 3:30 am

    #1406152

  • RE: Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

    Can you run this and post the execution plan (it'll rollback so no permanent changes are made)

    declare @id int

    select @id = max(id) from ncdba.logtimes

    begin transaction

    update ncdba.logtimes WITH (UPDLOCK, ROWLOCK) set...

    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

    November 11, 2011 at 3:26 am

    #1406150

  • RE: Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

    p.s. Any particular reason you've chosen serialisable isolation level here?

    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

    November 11, 2011 at 3:02 am

    #1406148

  • RE: Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

    One thing to note: The rowlock hint just tells SQL to start with row locks. If it decides that it needs to escalate, it will escalate, and you very clearly...

    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

    November 11, 2011 at 3:00 am

    #1406146

  • RE: Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

    So how does the LogSearchTimes table some into this. That's clearly the table that's getting the deadlocks and, it's table-level locks.

    Edit: never mind, your queries have the wrong table...

    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

    November 11, 2011 at 2:55 am

    #1406143

  • RE: Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

    The deadlock is not on the table that you're hinting and updating (logtimes), so no, you don't have an identity error.

    The deadlock comes from object-level (table locks) on domain.ncdba.LogSearchTimes. Is...

    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

    November 11, 2011 at 2:36 am

    #1406138

  • RE: Deadlocks

    anthony.green (11/11/2011)

    I have a situation at the moment with deadlocks which have just started happening with a very minor change to a stored proc.

    Could you start a new thread for...

    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

    November 11, 2011 at 2:27 am

    #1406132

  • RE: PageLatch_SH waittime drastically increases

    PageLatch on what resource? Could be TempDB allocation contention, but I want to see the resource first to be sure.

    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

    November 11, 2011 at 2:24 am

    #1406129

  • RE: The curious case of missing stored proc changes.

    ProofOfLife (11/10/2011)

    My conclusion from all this is that SQL Server had somehow tangled itself in relation to the stored proc - maybe something stuck in tempdb??

    Not how things work....

    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

    November 11, 2011 at 2:23 am

    #1406128

  • RE: Deadlocks

    If you're up to buying something, SQL Server MVP Deep Dives 1 has a chapter on reading deadlock graphs.

    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

    November 11, 2011 at 2:12 am

    #1406124

  • RE: The curious case of missing stored proc changes.

    Dev (11/11/2011)

    Well I can understand the scenario & your worries / frustration as well. I have experienced it. I don’t remember exact code that caused the issue BUT...

    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

    November 11, 2011 at 2:11 am

    #1406123

Viewing 15 posts - 22,321 through 22,335 (of 49,552 total)