September 22, 2004 at 2:16 pm
Does anyone have a script that can be executed that will show which indexes are becoming fragmented? I am just starting to try and be proactive on a new highly visable SQL Server database that just went into production. I cannot seem to find much other than running the DBCC Showcontig report for each tbl/index.
September 22, 2004 at 4:14 pm
DBCC SHOWCONTIG will give you what you need. The main thing to look at is Scan Density. The lower the value (from 100) the more fragmented the index. Also look at the size of the index. Low Scan Density values on small indexes really don't mean much. In fact, on really small indexes, you may not be ABLE to get the scan density up. A good rule of thumb is that if scan density falls much below 90 on larger indexes, they should probably be rebuilt.
Steve
September 23, 2004 at 1:14 am
How about gettinf DBCC SHOWCONTIG into a table so it can be tracked over time.
(This is something I've yet to do)
Julian Kuiters
juliankuiters.id.au
September 23, 2004 at 2:41 am
you could set a batch job to output the details to a log. If you feel that the datapages are becoming more fragmented then you might want to try to re-index the file :
DBCC DBREINDEX ('DATABASE.DBO.TABLE NAME','',FILLER SIZE)
Cheers
Mike.
September 23, 2004 at 7:55 am
--This procedure run in a DTS package
--I run this on all my tables every night and I've never had a problem with re-indexing.
--This code will only re-index the tables with a scan density < 90
--and LogicalFragmentation > 10, You can change it of course.
First creat a table
create table XX_DBCC_SHOWCONTIG
(ObjectName Varchar (60),
ObjectID int,
IndexName varchar(60),
IndexId int,
Level int,
Pages int,
Rows int,
MinimumRecordSize int,
MaximumRecordSize int,
AverageRecordSize int,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
AverageFreeBytes int,
AferagePageDensity decimal,
Scandensity float,
Bestcount int,
ActualCount int,
LogicalFragmentation float,
ExtentFragmentation decimal)
--step 1
Truncate table XX_DBCC_SHOWCONTIG
--Step 2 insert data for your table(s)that you want to re-index
insert into XX_DBCC_SHOWCONTIG
exec ('DBCC SHOWCONTIG (TableA) with fast,ALL_INDEXES,TABLERESULTS')
go
insert into XX_DBCC_SHOWCONTIG
exec ('DBCC SHOWCONTIG (TableB) with fast,ALL_INDEXES,TABLERESULTS')
--step 3
--exec procDBREINDEX2
--Proc code
CREATE proc procDBREINDEX2
as
SET ARITHABORT ON
declare @ObjectName varchar(60)
declare @Scandensity float
declare @LogicalFragmentation float
set @Scandensity = (select min(Scandensity)as Scandensity
from XX_DBCC_SHOWCONTIG
where Scandensity < 90)
set @LogicalFragmentation = (select min(LogicalFragmentation)as LogicalFragmentation
from XX_DBCC_SHOWCONTIG
where LogicalFragmentation > 10)
--select @Scandensity,@LogicalFragmentation
-- re index run in a loop until all table's are re-indexed
while @Scandensity < 90 or @LogicalFragmentation > 10
begin
set @Scandensity = (select min(Scandensity)as Scandensity
from XX_DBCC_SHOWCONTIG
where Scandensity < 90)
set @LogicalFragmentation = (select min(LogicalFragmentation)as LogicalFragmentation
from XX_DBCC_SHOWCONTIG
LogicalFragmentation > 10)
set @ObjectName = (select top 1 ObjectName
from XX_DBCC_SHOWCONTIG
where Scandensity = @Scandensity or LogicalFragmentation = @LogicalFragmentation)
--select @Scandensity,@ObjectName,@LogicalFragmentation
if @Scandensity < 90 or @LogicalFragmentation > 10
begin
DBCC DBREINDEX (@ObjectName)
end
update XX_DBCC_SHOWCONTIG
set Scandensity = 100,
LogicalFragmentation = 0
from XX_DBCC_SHOWCONTIG
where ObjectName = @ObjectName
end
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply