September 18, 2015 at 9:16 am
We use a script which reorgs between 10-30% fragmentation and rebuilds at >30% fragmentation
I think it is from TechNet...dont know anymore but it is a script i can read and understand
USE Database
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 WITH ( SORT_IN_TEMPDB = ON )';
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
I`ve started reading Hallengren's script...and it is (for me) a bit complicated to read
Perry Whittle (9/18/2015)
when you shrink you'll likely undo all the rebuild or reorg work anyway as pages may get moved during the shrink operation, so your efforts in the first place are futile.
k this is new to me, thought it handled it another way (we have no fragmentation after shrinking btw)....
September 18, 2015 at 11:13 am
Wurstsalat (9/18/2015)
I`ve started reading Hallengren's script...and it is (for me) a bit complicated to read
I think that too. You can just use it "as it", and it has every option under the Sun - in the event that you should need it 🙂
I think Minion Backup may be more suitable for smaller organisations. Their thinking is more like how I do my backups (home-grown code). Out-of-the-box it just backs everything up, to a reasonable pattern.
If you want to prevent a bunch of databases being backed up then just add their names (or a wildcard) to an exclusions list.
Want an index to be forced to FILLFACTOR = 80 at reindex? You can set that for the default for the whole server (scary!), or a complete database (probably also too-scary!), or a single table ... or a single index. There are lots of other options, many of those will make sense to set at a higher granularity level, others at the lowest possible level, but I like the cascading-settings concept.
It is built to be trivially easy to deploy, and configure, on a network-wide basis (assuming you have plenty of SQL Boxes scattered around your organisation).
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply