April 9, 2009 at 11:26 am
Hi,
Could you please tell me how can we know the index fragmentation level for all the indexes in all the databases.
Iam using the below script to defragment the indexes in all databases weekly.
-- Ensure a USE statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
But I want to have a script which checks the current index fragmentation level on all databases continuously, and if it is higher than 30% or some then go head defragment.
I guess all the DBAs do in this way right?
please advise me the best way
April 9, 2009 at 11:40 am
Hi,
Take a look at the following post. It provides reference to what I beleive to be one of most robust SQL Server Index Defrag scripts and was written by Michelle Uford aks SQLFool.
With a little effort, you could wrap the stored procedure in a job and schedule execution at the desired frequency.
Index Optimization/Defrag Script[/url]
Cheers,
April 10, 2009 at 3:20 pm
Could you please explain how you gather the statistics, I mean Index fragmentation on a production sq server 2005 continuously checking for index fragmentation which will occurring because of data inserts updates n deletes and take the necessary action using scripts
April 11, 2009 at 7:39 am
madhu.arda (4/10/2009)
Could you please explain how you gather the statistics, I mean Index fragmentation on a production sq server 2005 continuously checking for index fragmentation which will occurring because of data inserts updates n deletes and take the necessary action using scripts
Hi,
You should look for dmv sys.dm_db_index_pysical_stats and look for the column avg_index_fragmentation_level. as a thumb of rule if its over 30% then you rebuild indexes and its it lesser than 30% then you defrag indexes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply