Forum Replies Created

Viewing 15 posts - 11,041 through 11,055 (of 49,552 total)

  • RE: Index/table rebuild

    Matt Crowley (11/6/2013)

    GilaMonster (11/6/2013)

    Matt Crowley (11/6/2013)

    just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to...

    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 6, 2013 at 2:09 pm

    #1664568

  • RE: Database went into Restoring State for unknown reason

    Restoring or recovering?

    A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY or runs a restore over the database and specifies NORECOVERY. In either case running RESTORE...

    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 6, 2013 at 2:09 pm

    #1664567

  • RE: Index/table rebuild

    Matt Crowley (11/6/2013)

    just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to wait for 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

    November 6, 2013 at 1:41 pm

    #1664561

  • RE: Index/table rebuild

    Heaps do appear in sys.dm_db_index_physical_stats, but since you can't really rebuild them there's not much point.

    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 6, 2013 at 1:41 pm

    #1664560

  • RE: Compact, fully indexed, archive database

    Good point. Should have said CREATE ... WITH DROP_EXISTING

    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 6, 2013 at 10:36 am

    #1664524

  • RE: DELETE running for 8 hours need to Stop the process

    Next time don't restart SQL while there's a large transaction rolling back.

    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 6, 2013 at 10:36 am

    #1664522

  • RE: SQL 2005 T-SQL Performance tuning

    Please post table definitions and execution plan. Also consider getting rid of those nolocks, they're not go-faster options they can result in incorrect results.

    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 6, 2013 at 7:44 am

    #1664467

  • RE: DELETE running for 8 hours need to Stop the process

    Hope no one needs to use that database for the next several hours...

    As I said, if you restart, the rollback will continue with the database unavailable. It'll be unavailable until...

    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 6, 2013 at 6:59 am

    #1664453

  • RE: DELETE running for 8 hours need to Stop the process

    Welsh Corgi (11/6/2013)

    The Transaction Log Backup is 145 GB. Tried shrinking it but it did not make much of a difference.

    No, it won't because there's no free space in 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

    November 6, 2013 at 6:50 am

    #1664448

  • RE: Compact, fully indexed, archive database

    I'll second the 'rebuild indexes onto another filegroup' option. If you just rebuild them in the filegroup they're currently in, you'll probably end up with a lot of empty space...

    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 6, 2013 at 6:31 am

    #1664444

  • RE: T-SQL syntax

    Blocked simply means waiting for a lock that some other process has. Not a deadlock, not fatal. It's not something you can tell from just an update statement, identifying that...

    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 6, 2013 at 6:26 am

    #1664438

  • RE: DELETE running for 8 hours need to Stop the process

    Wait for it to finish or stop it and wait for it to rollback. That's pretty much it.

    Or drop the database and restore from backups.

    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 6, 2013 at 6:22 am

    #1664437

  • RE: Memory taken by procedure

    Have you considered enabling 'optimise for adhoc workloads'?

    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 6, 2013 at 5:31 am

    #1664418

  • RE: Memory taken by procedure

    Low PLE != plan cache memory pressure. The balance between the plan cache and the data cache is complex, but they are two separate caches each with their own aging...

    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 6, 2013 at 5:15 am

    #1664408

  • RE: Memory taken by procedure

    sys.dm_exec_cached_plans

    size_in_bytes int Number of bytes consumed by the cache object.

    So yes.

    See whether you have memory pressure, see whether you have cache memory pressure before you spend lots...

    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 6, 2013 at 4:44 am

    #1664398

Viewing 15 posts - 11,041 through 11,055 (of 49,552 total)