November 6, 2008 at 3:09 pm
Hi,
we are using sqlresponse for monitoring,Iam new to this product. It recommending that the following indexes are fragmented. What are the steps do I need to take to avoid this index fragmentation?
Here is the message:
Index Fragmented:Database BiztalkmgmtDb
IndexName TableNAme LogicalscanFragmentation Pages Size
IX_bt_DocumentSpec_msgtype dbo.bt_DocumentSpec92.59 % 27 216.0 kB
IX_bt_DocumentSpec_clr_namespace dbo.bt_DocumentSpec54.84 % 62 496.0 kB
Thanks
November 6, 2008 at 3:16 pm
Hi I develop this query to defrag indexes that have 30% defragmentation (you can change the %). it also show you a report of before and after fragmentation. I hope this will help:
ALTER Proc [DBM].[Defrag]
as
-- Index Fragmentation: Delete unused pages
Declare @SQL nvarchar(4000),
@acursor_TableName cursor,
@TableName varchar(150),
@acursor_Indexes cursor,
@Indexes varchar(150)
Create Table #Report
(
[Table Name] varchar(150),
Fragment decimal(18,2),
Defragment decimal(18,2),
Percentage decimal(18,2)
)
Create Table #TableIndex
(
Indexes varchar(150),
Fragment decimal(18,2)
)
-- Change change compatibility level to 90
EXEC sp_dbcmptlevel ICDB, 90
--Start Looping (Get table)
set @acursor_TableName = cursor for SELECT TABLE_NAME AS Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' And TABLE_SCHEMA = 'dbo'
open @acursor_TableName
fetch next from @acursor_TableName into @TableName
while (@@fetch_status = 0)
begin
---------------------------------------------------------------------------------------------------
INSERT INTO #Report
Values(@TableName,'0','0','0')
Truncate Table #TableIndex
Set @SQL = 'Select Name, Avg_fragmentation_in_Percent
FROM sys.dm_db_index_physical_stats (db_id(N''ICDB''),
Object_id(''' + @TableName + '''),null,null,null) as A
JOIN
sys.indexes as B on a.Object_ID = b.Object_id
AND a.index_id = b.index_id
AND name is not null
AND name <> '''''
Insert INTO #TableIndex(Indexes,Fragment)
Execute sp_Executesql @sql
UPDATE A SET Fragment = (SELECT Sum(Fragment) / (Select Count(*) FROM #TableIndex) FROM #TableIndex)
FROM #Report A
Where [Table Name] = @TableName
--Start Looping (Get Indexes)
set @acursor_Indexes = cursor for SELECT Indexes FROM #TableIndex Where Fragment >= 30
open @acursor_Indexes
fetch next from @acursor_Indexes into @Indexes
while (@@fetch_status = 0)
begin
---------------------------------------------------------------------------------------------------
Set @SQL = 'Alter INDEX ' + @Indexes + ' on ' + @TableName + ' Rebuild'
Execute sp_Executesql @sql
---------------------------------------------------------------------------------------------------
fetch next from @acursor_Indexes into @Indexes
end
close @acursor_Indexes
deallocate @acursor_Indexes
--End Looping
Truncate Table #TableIndex
Set @SQL = 'Select Name, Avg_fragmentation_in_Percent
FROM sys.dm_db_index_physical_stats (db_id(N''Database Name''),
Object_id(''' + @TableName + '''),null,null,null) as A
JOIN
sys.indexes as B on a.Object_ID = b.Object_id
AND a.index_id = b.index_id
AND name is not null
AND name <> '''''
Insert INTO #TableIndex(Indexes,Fragment)
Execute sp_Executesql @sql
UPDATE A SET Defragment = (SELECT Sum(Fragment) / (Select Count(*) FROM #TableIndex) FROM #TableIndex)
FROM #Report A
Where [Table Name] = @TableName
---------------------------------------------------------------------------------------------------
fetch next from @acursor_TableName into @TableName
end
close @acursor_TableName
deallocate @acursor_TableName
--End Looping
Update A Set Percentage = (Defragment/Fragment) * 100
FROM #Report A
Where Defragment <> 0
and Defragment is not null
and Fragment <> 0
and Fragment is not null
Select *
FROM #Report
Where Defragment is not null
and Defragment <> '0.00'
and Defragment <> Fragment
Drop table #TableIndex
Drop Table #Report
EXEC sp_recompile sys_profile
-- Change back compatibility level to 80
EXEC sp_dbcmptlevel ICDB, 80
November 6, 2008 at 4:38 pm
You can't avoid index fragmentation unless you don't change data. Sooner or later with deletes or page splits you'll get fragmentation. The thing to do is monitor it (as shown above) and then rebuild the indexes when it gets bad.
November 7, 2008 at 1:07 pm
Hi there. I also have a process that will defrag indexes nightly. You can find the script on my SQL blog here: http://sqlfool.com/?p=63.
Aside from that, Steve's right... data is bound to become fragmented in SQL Server. There are some steps you can do to minimize fragmentation, however. For example, you can ensure that you're clustering on an incrementing value, such an identity column or even a datetime stamp. If this is not an option, then you can play with the fill factor on the index (http://msdn.microsoft.com/en-us/library/ms177459.aspx).
HTH!
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 7, 2008 at 1:21 pm
With indexes as small as you're showing (27 pages), it's not likely that a rebuild OR a defragment will do anything. the Defrag and rebuild operation focus on improving performance, and NOT on building a perfectly ordered contiguous index. If your performance isn't negatively affected, then it's not going to do anything. The algorithm used essentially doesn't do much on indexes <1000 pages.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply