chickerinoDOTcom

Here at Webjam, our database indexes have been suffering from a little mal-nourishment of late. By that I mean we've found that they're getting fragmented rather too quickly for our liking and our weekly index maintenance process has a habit of grinding our database server to a halt whilst it hastily attempts to resolve the issues of the past week.

Therefore we needed a more gentile solution to our problem that would address the fragmentation issues before they became too much of a problem and thus not kill our database server every time they were addressed.

What we came up with was a clever way of constantly analysing which indexes were getting over-fragmented and fixing them before they became too much of a problem. This requires a table to store some simple information on the indexes in the database:

CREATE TABLE [dbo].[_index_stats](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [table_name] [varchar](255) NOT NULL,
    [index_name] [varchar](255) NOT NULL,
    [fragmentation] [float] NOT NULL,
    [scan_density] [float] NOT NULL,
    [pages] [int] NOT NULL,
    [date_created] [datetime] NOT NULL CONSTRAINT [DF__index_stats_date_created] DEFAULT (getdate()),

[sql_statement] [varchar](255) NULL)

And a stored procedure to work out the fragmentation and size of the indexes.

CREATE procedure [dbo].[generate_index_stats]

-- Create the table.

CREATE TABLE #fraglist (

ObjectName varchar(255),

ObjectId int,

IndexName varchar(255),

IndexId int,

Lvl int,

CountPages int,

CountRows int,

MinRecSize int,

MaxRecSize int,

AvgRecSize int,

ForRecCount int,

Extents int,

ExtentSwitches int,

AvgFreeBytes int,

AvgPageDensity int,

ScanDensity float,

BestCount int,

ActualCount int,

LogicalFrag float,

ExtentFrag decimal);

--delete the current stats

delete from _index_stats

--table name variable

declare @table_name varchar(255)

--use a cursor to loop through the DB tables

declare c cursor for

select [name] from sysobjects where type = 'U'

open c

fetch next from c into @table_name

while @@fetch_status = 0

begin

insert into #fraglist

exec('DBCC SHOWCONTIG(''' + @table_name + ''') with fast, all_indexes, tableresults, no_infomsgs')

fetch next from c into @table_name

end

insert into _index_stats

(table_name, index_name, fragmentation, scan_density, pages, sql_statement)

select

ObjectName, IndexName, LogicalFrag, ScanDensity, CountPages,

case

when LogicalFrag > 30 then

'ALTER INDEX [' + IndexName + '] ON [' + ObjectName + '] REBUILD;'

when LogicalFrag > 5 then

'ALTER INDEX [' + IndexName + '] ON [' + ObjectName + '] REORGANIZE;'

else

end

from #fraglist

where

LogicalFrag > 0

and

CountPages > 500

and

--exclude system tables and user system tables (starting with underscore)

ObjectName not like '[_]%' and ObjectName not like 'sys%'

--show the results

select*

from_index_stats

where

fragmentation > 5

and

pages > 100

order by

fragmentation * fragmentation * pages desc

--close cursor

close c

deallocate c

And finally a stored procedure to fix the fragmented indexes, one at a time. Once the index has been fixed, stats are re-generated to work out the next index to fix.

CREATE procedure [dbo].[fix_next_index]
as

--get the sql and id of the first row in the list of fragmented indexes
declare @sqlvarchar(512)
declare @idint

--get from first index from the pre-prepared list - this will be the one with the highest fragmentation
select @id = id, @sql =sql_statement
from _index_stats

where sql_statement is not null and sql_statement <> ''

order by
fragmentation * fragmentation * pages

--if we have an index to rebuild, do it
if(@id is not null)
begin
delete from _index_stats where id = @id
exec(@sql)
end

--generate the new index stats
exec generate_index_stats

This procedure is then run on a schedule (we run it every 5 mins at the moment) keeping index fragmentation low at all times.

pages

tumble-log » Geeky stuff: Self-maintaining SQL Server indexes

Comments

Leave a Comment

© 2008, Chickerino.com and trademark owners