November 10, 2011 at 11:31 am
I'm setting up a multiserver architecture and have all backup and integrity checks created in a manner that will work across any server. Now I'm trying to setup a job to dynamically rebuild/reorganize indexes on all databases. I have a script that I got somewhere years ago that looks at the level of fragmentation to determine which it should do (rebuild/reorg) but it only works at a database level. Has anyone had experience with setting up this type of job for MSX?
Here is the script I would typically use on a database...I guess I would like to figure out how to edit it to make it run on all databases on the server (maybe sp_msforeachdb or something):
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF object_id('tempdb.dbo.#index_maintenance') is not null
DROP TABLE #index_maintenance;
-- conditionally select from the function, converting 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 #index_maintenance
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0
ORDER BY avg_fragmentation_in_percent desc;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #index_maintenance;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = 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 = 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
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname + '] ON [' + @schemaname + '].[' + @objectname + '] REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname +'] ON [' + @schemaname + '].[' + @objectname + '] REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT 'Executed ' + @command;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- drop the temporary table
IF object_id('tempdb.dbo.#index_maintenance') is not null
DROP TABLE #index_maintenance;
GO
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
November 10, 2011 at 1:15 pm
Here is a script I tossed together based on something I read in the 70-432 book. It will reorganize indexes or rebuild indexes based on thresholds. The variables at the top can be tweaked to preference. I have it reorganize between 5% and 30% fragmentation, and rebuild anything over that...always considering a minimum number of pages before worrying about fragmentation.
It will run against all databases with a DBID over 4 that are online....I didn't like it hitting the system databases. Feel free to pass it along.
November 10, 2011 at 4:17 pm
Thank you, I will take a look!
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
November 10, 2011 at 5:52 pm
That works on all dbs on the same server.
Should be easy enough to strip out the create sp part and use that as a standalone script.
November 10, 2011 at 11:55 pm
Kudikai (11/10/2011)
Here is a script I tossed together based on something I read in the 70-432 book. It will reorganize indexes or rebuild indexes based on thresholds. The variables at the top can be tweaked to preference. I have it reorganize between 5% and 30% fragmentation, and rebuild anything over that...always considering a minimum number of pages before worrying about fragmentation.It will run against all databases with a DBID over 4 that are online....I didn't like it hitting the system databases. Feel free to pass it along.
Courtesy: Paul & Gail.
http://www.sqlservercentral.com/Forums/Topic1202732-391-1.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply