Forum Replies Created

Viewing 15 posts - 13,741 through 13,755 (of 49,552 total)

  • RE: Lock only one row in update (lock a row)

    abitguru (3/25/2013)

    Thanks McSQL, when I try those hits, I can't access other ID when I select ID 1 for example.

    Probably because SQL is taking row locks on every single row...

    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

    March 25, 2013 at 10:43 am

    #1600419

  • RE: SQL 2005 - Get Deadlock information

    There's no way to get historical deadlocks in SQL 2005 unless you already had some traceflag or trace on at the time.

    To catch future deadlocks, turn traceflag 1222 on, the...

    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

    March 25, 2013 at 10:36 am

    #1600414

  • RE: fragmentation in database

    vikingDBA (3/25/2013)

    Totally agree. Page count is the main one other than avg frag %.

    I use > 30% and > 50 pages to rebuild. Less on either just gets...

    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

    March 25, 2013 at 10:29 am

    #1600403

  • RE: Truncate table

    kevaburg (3/25/2013)

    Information related to non-clustered indexes are stored as a view in the MASTER database named sys.partitions.

    The view definition is in the system resource database. The data that the...

    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

    March 25, 2013 at 9:12 am

    #1600359

  • RE: Lock only one row in update (lock a row)

    abitguru (3/25/2013)

    [/b]

    begin transaction

    SELECT * FROM Reque_pa with (updlock) WHERE CEREQ = 1

    WAITFOR DELAY '00:00:10';

    commit transaction

    This is seriously exactly what you have in your application, a select with a waitfor?...

    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

    March 25, 2013 at 9:08 am

    #1600357

  • RE: Lock only one row in update (lock a row)

    Cool, please post the table definition, the index definition and the code that contains that transaction (at least the begin tran part through to the selection of the parameter).

    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

    March 25, 2013 at 7:55 am

    #1600326

  • RE: Multiple condition based on where clause in sql server 2008

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    You'll need dynamic SQL on this one.

    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

    March 25, 2013 at 7:49 am

    #1600318

  • RE: Cannot backup - Old killed backup still running

    You won't be able to drop or restore the database, just restart SQL. Shouldn't take any recovery time at all.

    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

    March 25, 2013 at 7:45 am

    #1600314

  • RE: Cannot backup - Old killed backup still running

    Restart the SQL instance.

    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

    March 25, 2013 at 7:31 am

    #1600300

  • RE: Lock only one row in update (lock a row)

    abitguru (3/25/2013)

    1 - I understand that I can't lock (for selecting, updating) a row with a select statement. Am I right?

    No, you've done exactly that several times in this thread,...

    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

    March 25, 2013 at 7:29 am

    #1600299

  • RE: Why should we have multiple FileGroups instead of one?

    terry999 (3/25/2013)

    Say you have a table of sales you could partition on sales date, so have one file per year.

    Performance would be better for queries in that time period

    Partitioning 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

    March 25, 2013 at 7:23 am

    #1600294

  • RE: Why should we have multiple FileGroups instead of one?

    Another article making blanket statements without explaination .... 🙁

    Multiple filegroups - maybe. Not always. Two reasons.

    Split for performance - requires that the DB is IO bound to have any effect...

    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

    March 25, 2013 at 7:03 am

    #1600286

  • RE: Lock only one row in update (lock a row)

    abitguru (3/25/2013)

    this records are independent each other, so if I read parameter number 1 I want noone can read this row. I know that this action is imposible in sql...

    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

    March 25, 2013 at 6:57 am

    #1600284

  • RE: Lock only one row in update (lock a row)

    McSQL (3/25/2013)

    You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;

    ALTER INDEX...

    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

    March 25, 2013 at 6:27 am

    #1600272

  • RE: Lock only one row in update (lock a row)

    abitguru (3/25/2013)

    GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)

    You can. So you'd prefer SQL to run out of lock...

    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

    March 25, 2013 at 6:25 am

    #1600271

Viewing 15 posts - 13,741 through 13,755 (of 49,552 total)