MSSQLTips.com - your daily source for SQL Server tips

Google
 
Web mssqltips.com

ESSENTIALS: Home | Tips | Search | Categories | Top 10 | Products | Authors | Blogs | Forums | Webcasts | Advertise | About
worse fragmented indexes -

in Search

worse fragmented indexes

Last post 06-25-2008 8:04 AM by aprato. 1 replies.
Page 1 of 1 (2 items)
Sort Posts: Previous Next
  • 06-25-2008 12:17 AM

    • Das
    • Top 500 Contributor
    • Joined on 06-25-2008
    • Posts 1

    worse fragmented indexes

    Hi All,  I am working on SQL 2000 Sever.  I want to find out the top 10 worse fragmented indexes in the database.  Can some help me with a query to get this information? Thanks you.

     

  • 06-25-2008 8:04 AM In reply to

    • aprato
    • Top 10 Contributor
    • Joined on 12-01-2007
    • Greater Boston
    • Posts 191

    Re: worse fragmented indexes

     Here's a script I put together that you can test out.  You may have to tweak it.

    Run it against a test database and try it out. 

    set nocount on
    go
    if object_id('tempdb..#table') is not null
       drop table #table
    go
    if object_id('tempdb..#dbcc') is not null
       drop table #dbcc
    go

    declare @counter int, @totalrows int, @tablename sysname

    create table #table (tempid int identity not null primary key, tablename sysname)
    create table #dbcc  (dbccid int identity not null primary key, objectname sysname, objectid int, indexname sysname, indexid int, level int,
                         pages int, rows int, minRecordSize int, MaxRecordSize int, AvgRecordSize int, ForwardedRecords int, Extents int,
                         ExtentSwitches int, AvgFreeBytes int, AvgPageDensity int, ScanDensity int, BestCount int, ActualCount int,
                         LogicalFragmentation int, ExtentFragmentation int)


    insert into #table (tablename)
    select table_name
    from information_schema.tables
    where table_type = 'base table'
    and objectproperty(object_id(table_name),'IsMSShipped') = 0

    select @totalrows = @@rowcount

    -- Load the fragmentation data for each index
    select @counter = 1
    while (@counter <= @totalrows)
    begin

      select @tablename = tablename
      from #table
      where tempid = @counter

      insert into #dbcc (objectname, objectid, indexname, indexid, [level], pages, [rows], minRecordSize, MaxRecordSize, AvgRecordSize,
                         ForwardedRecords, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount,
                         LogicalFragmentation, ExtentFragmentation)
      exec ('dbcc showcontig (' + @tablename + ') with tableresults, all_indexes')

      -- prepare to get the next index
      select @counter = @counter + 1

    end
     
    select top 10 indexname, rows, logicalfragmentation, bestcount, actualcount
    from #dbcc
    where actualcount > 0
    and indexname <> ''
    --and pages > 1000
    order by logicalfragmentation desc
    go 
     

Page 1 of 1 (2 items)