Checking Your Database Fragmentation Level

  • Nicholas Cain

    SSC-Dedicated

    Points: 33009

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column



    Shamless self promotion - read my blog http://sirsql.net

  • Bikeman

    Ten Centuries

    Points: 1207

    Hi Nicholas,

    Nice article, I enjoyed reading it.

    BTW, what is the benifit of using the dts vs scheduling these steps from a job? I am assuming both will be done by someone with dba rights.

    Thanks again

     

  • Preston Burroughs

    SSC Enthusiast

    Points: 124

    This was a great article.  I tried implementing, but have been having problems running the stored procedure USP_DBshowcontig_single_db.  #table# name error.  I have been having a provlem with this part of the stored procedure.

    set @inserttable = ('INSERT INTO ADMINDB.DBO.INDEX_INFO_.'+@NAME+ 'ObjectName,

       ObjectId,

       IndexName,

       IndexId,

       Lvl,

       CountPages,

       CountRows,

       MinRecSize,

       MaxRecSize,

       AvgRecSize,

       ForRecCount,

       Extents,

       ExtentSwitches,

       AvgFreeBytes,

       AvgPageDensity,

       ScanDensity,

       BestCount,

       ActualCount,

       LogicalFrag,

       ExtentFrag'

     select ObjectName,

       ObjectId,

       IndexName,

       IndexId,

       Lvl,

       CountPages,

       CountRows,

       MinRecSize,

       MaxRecSize,

       AvgRecSize,

       ForRecCount,

       Extents,

       ExtentSwitches,

       AvgFreeBytes,

       AvgPageDensity,

       ScanDensity,

       BestCount,

       ActualCount,

       LogicalFrag,

       ExtentFrag

      FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''

     

  • Christi Nicholson

    SSC Journeyman

    Points: 92

    I'm also have problems with this stored procedure.  Have you had any luck with it?

  • Preston Burroughs

    SSC Enthusiast

    Points: 124

    Stll not having any luck.

  • Dave Henderson

    SSC Rookie

    Points: 47

    I got it working by adding a 5th single quote to the line:

    FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''

    Dave H

  • hot2use

    SSCrazy

    Points: 2915

    Hiyas.

    Had the same problem with the quotes ... noticed in QA that everything was red so I added a fifth quote.

    Anyway. Got a small addition here for the same sproc USP_DBshowcontig_single_db :

    set @sql = 'SELECT TABLE_NAME = ' + @dbname + '..SYSUSERS.NAME' + ' + ''.'' + ' + '' + @dbname + '..SYSOBJECTS.NAME FROM ' + @dbname + '..SYSOBJECTS JOIN ' + @dbname + '..SYSUSERS ON ' + @dbname + '..SYSOBJECTS.uid = ' + @dbname + '..SYSUSERS.uid WHERE XTYPE =' + '''' + 'U' + ''''

    It adds the owner name of the table to TABLE_NAME, which allows you to run the script on databases which have user tables not created by sa.

    My piece replaces the line:

    set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''

    Good luck.

     

    hot2use


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • kumarkaji@yahoo.com

    Old Hand

    Points: 303

    Hi,

    I am very new to these things.. can any of you guys tell me how to identify ideal fragmentation level...and how do i identify it..

     

    Thanks in advance..

    Kumar

  • Tatsu

    SSCertifiable

    Points: 7824

    This is fantastic. I have a client who is having fragmentation problems in a heavily used medical records database and this could be just what I need to measure just how quickly the database is becoming fragmented so we can schedule the maintenance plans appropriately.

    Thanks a lot!

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • hot2use

    SSCrazy

    Points: 2915

    I'm not a fully fledged DBA, but looking at the scripts I'd assume that a scan density beloew 75 percent is bad.

    Ken England has a good book on "Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook" which I'd recommend (ISBN# 1-55558-241-9). And then searching for DBCC SHOWCONTIG in Google should give you some more information at what to look for.

    Regards

    hot2use


    __________________________________
    Searching the KB articles is like
    picking your nose. You never know
    what you'll find.

  • RML51

    SSCarpal Tunnel

    Points: 4410

    No luck getting this to output. Eventually got everything to run, but email output simply reads: THETEXT

     

     

  • Stijn977

    SSC Eights!

    Points: 962

    Hi,

    I am using a similar approach (running a SHOWCONTIG and use the output to decide which indexes need to be defragmented). However, i am always running into rapid growing log files once i start reindexing or DBCC INDEXDEFRAG.

    Does anyone know a "safe" way on how to defragment those fragmentated ones without risking blowing up the logs?

    Greets,

    Stijn

  • noeld

    SSC Guru

    Points: 96590

    There are two schools in this matter. Pick what suit you best

    1. Run parallel Tlog backups at the same time the reindexing is happening eg. Run a Tlog backup every 2 min while the reindexing is going on

    2. Make a Tlog Backup, Switch the recovery model to Bulk_logged, perform the Reindexing, switch back to Full and when performing the next TLog backup do it on a drive that have plenty of space

    Cheers,

     


    * Noel

  • Stijn977

    SSC Eights!

    Points: 962

    Thanks ... i'll see how it goes when i up the backup frequency when i'm reindexing.

    Much appreciated,

    Stijn

  • nmargol

    SSC Enthusiast

    Points: 134

     

    Still can't get usp_dbshowcontig_single_db to work.  I added the fifth quote but am still getting this error message:  Invalid object name '#tablename'

    Has anyone else had this error message and solved the problem?

     

Viewing 15 posts - 1 through 15 (of 18 total)

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