Σε παλαιότερο μου άρθρο είχα φτιάξει κάποια scripts τα οποία μπορούν να χρησιμοποιηθούν για το indexes fragmentation. Σήμερα σας δίνω μια stored procedure την οποία την κάνω system procedure και μπορεί να είναι διαθέσιμη σε όλες τις databases, αλλά παίρνει και παραμέτρους για να καλυφτούν περισσότερες ανάγκες ανά περίπτωση
Οι παράμετροι είναι οι παρακάτω:
@page_threshold : Ο αριθμός των index pages που πρέπει να έχει ο index ώστε να γίνει rebuild / reorganize. H default τιμή της παραμέτρου 1000 index pages
@fragementation_threshold_limit : Το ποσοστό του fragmentation απο το οποίο και μετά θα γίνει το rebuild / reorganize. H default τιμή της παραμέτρου είναι 5%.
@with_log : Αν θα γίνεται log η διαδικασία στα SQL Server logs. H default τιμή της παραμέτρου είναι 1 ( να γίνεται log ) (0: να μην γίνεται log)
Επίσης θα πρέπει να αναφέρω ότι μέσα στην συγκεκριμένη stored procedure γίνονται διάφοροι έλεγχοι όπως:
- Αν ο πίνακας έχει blob field τότε δεν γίνεται online το rebuild του index
- Aν το fragmentation είναι πάνω από 30% γίνεται rebuild αλλιώς reorganize
- Όλα τα rebuild γίνονται με MAXDOP=1 εφόσον είναι και ONLINE=ON, αλλιώς γίνονται με MAXDOP=2
USE master;
GO
CREATE PROC sp_db_index_maintenance ( @page_threshold int = 1000
, @fragementation_threshold_limit decimal(5,2) = 5.00
, @with_log bit = 1
)
as
BEGIN
SET NOCOUNT ON;
DECLARE idx CURSOR READ_ONLY FOR
WITH db_indexes as
(
SELECT
idx.name as index_name
,OBJECT_SCHEMA_NAME(idx.object_id) as table_schema_name
,OBJECT_NAME(idx.object_id) as table_name
,S.avg_fragmentation_in_percent as fragmentation_pct
,CASE
WHEN S.avg_fragmentation_in_percent < 30.0 THEN 'REORGANIZE'
ELSE 'REBUILD'
END as defrag_action
,CASE
WHEN (SELECT COUNT(*) FROM sys.partitions AS p WHERE (p.object_id = idx.object_id) AND (p.index_id = s.index_id)) > 1 THEN 1
ELSE 0
END as has_partitions
,s.partition_number
,idx.type as index_type
,CASE
WHEN SERVERPROPERTY('EditionID') in (1804890536, -- Enterprise
1872460670, -- Enterprise Edition: Core-based Licensing
610778273, -- Enterprise Evaluation
-2117995310) -- Developer
THEN 1
ELSE 0
END as support_online
,CASE
WHEN (select count(*) from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = OBJECT_SCHEMA_NAME(idx.object_id) AND TABLE_NAME = OBJECT_NAME(idx.object_id)
AND
(DATA_TYPE in ( 'text','ntext','image','binary','varbinary','xml')
OR
CHARACTER_MAXIMUM_LENGTH=-1) ) > 0
THEN 1
ELSE 0
END as has_blobs
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'LIMITED') AS s
JOIN sys.indexes AS idx
ON idx.OBJECT_ID = s.OBJECT_ID AND idx.index_id = s.index_id
WHERE
s.page_count > @page_threshold
AND
s.avg_fragmentation_in_percent > @fragementation_threshold_limit
AND
idx.type in (1,2)
AND
s.alloc_unit_type_desc='IN_ROW_DATA'
)
select 'ALTER INDEX ' + QUOTENAME(index_name)
+ ' ON '
+ QUOTENAME(table_schema_name)
+ '.'
+ QUOTENAME(table_name)
+ ' ' + defrag_action + ' '
+ CASE has_partitions
WHEN 1 THEN ' PARTITION = ' + CAST(partition_number AS nvarchar(10))
ELSE ''
END
+ CASE
WHEN defrag_action = 'REBUILD' AND support_online=1 AND has_blobs = 0 THEN ' WITH ( MAXDOP = 1 , ONLINE = ON ) '
WHEN defrag_action = 'REBUILD' AND ( support_online=0 OR has_blobs = 1 ) THEN ' WITH ( MAXDOP = 2 ) '
ELSE ''
END
+ ';' as idx
from db_indexes
ORDER BY table_schema_name , table_name , index_type , partition_number
DECLARE @msg nvarchar(max);
DECLARE @stm nvarchar(max);
OPEN idx
FETCH NEXT FROM idx INTO @stm
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
if (@with_log = 1 )
begin
SET @msg = 'Starting '+ @stm
RAISERROR (@msg,10,1) WITH LOG;
end
EXEC (@stm);
if (@with_log = 1 )
begin
SET @msg = 'Finished '+ @stm
RAISERROR (@msg,10,1) WITH LOG;
end
END
FETCH NEXT FROM idx INTO @stm
END
CLOSE idx;
DEALLOCATE idx;
END
GO
USE master;
GO
EXEC sp_ms_marksystemobject 'sp_db_index_maintenance';
GO//antonch