UGISS User Group Italiano SQL Server
Abbiamo trattato il problema della frammentazione degli indici nei post Index Fragmentation e Index Fragmentation (Parte 2) definendo la stored procedure USP_ExecReorgRebuildIndex() in grado di eseguire la deframmentazione degli indici con avg_fragmentation_in_percent maggiore del 10% in un database SQL Server 2005.
La DMV sys.dm_db_index_physical_stats non è presente nella versione 2000 di SQL Server (di cui esistono numerose installazioni attive in produzione) da qui la non compatibilità della stored procedure USP_ExecReorgRebuildIndex() su SQL Server 2000.
La necessità di deframmentare gli indici di un database SQL Server
— Drop procedure dbo.USP_DBCC_IndexDefrag_DBReindex if exists
IF OBJECT_ID(‘USP_DBCC_IndexDefrag_DBReindex’) IS NOT NULL
DROP PROCEDURE dbo.USP_DBCC_IndexDefrag_DBReindex
GO
— Create procedure dbo.USP_DBCC_IndexDefrag_DBReindex
CREATE PROCEDURE dbo.USP_DBCC_IndexDefrag_DBReindex
(@MaxFrag AS DECIMAL,
@Exec AS INT,
@Print AS INT,
@DefragMode AS INT,
@FillFactor AS INT)
AS BEGIN
/*
Descrizione:
Utilizzo di DBCC SHOWCONTIG, DBCC INDEXDEFRAG e DBCC DBREINDEX per
eseguire la deframmentazione degli indici di un database
Parametri:
@MaxFrag = Massima frammentazione consentita (in percentuale)
@Exec = La stored procedure esegue la deframmentazione dell’indice
(@Exec = -1 esegue, @Exec <> -1 non esegue)
@Print = La stored procedure restituisce in output (stampa)
i comandi REORGANIZE o REBUILD dell’indice
(@Print = -1 stampa i comandi, @Print <> -1 non stampa i comandi)
@DefragMode = Modalità di deframmentazione (1= DBCC INDEXDEFRAG, 2= DBCC DBREINDEX)
@FillFactor = FillFactor per DBCC DBREINDEX
*/
DECLARE @index_id AS INT,
@index_name SYSNAME,
@action_to_do_1 AS VARCHAR(1024),
@action_to_do_2 AS VARCHAR(1024),
@action_to_do_3 AS VARCHAR(1024),
@LogicalFrag FLOAT,
@table_name AS VARCHAR(512)
SET NOCOUNT ON
IF ((@DefragMode <> 1) AND (@DefragMode <> 2))
BEGIN
— Modalità di deframmentazione non prevista
PRINT ‘Il valore del parametro @DefragMode(=’ + LTRIM(STR(@DefragMode)) + ‘) è errato.’
PRINT ‘Valori ammessi per @DefragMode: 1 per DBCC INDEXDEFRAG, 2 per DBCC DBREINDEX.’
PRINT ‘Deframmentazione annullata.’
PRINT ”
END
ELSE BEGIN
— Drop tabella dbo.FragList
IF (OBJECT_ID(‘FragList’) IS NOT NULL)
DROP TABLE dbo.FragList
— Create tabella dbo.FragList
CREATE TABLE dbo.FragList (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
— ShowContig Into dbo.FragList di tutti gli indici del database corrente
INSERT INTO dbo.FragList
EXEC (‘DBCC SHOWCONTIG WITH ALL_LEVELS, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)
— Declare cursore CUR
DECLARE CUR CURSOR FOR
SELECT
F.IndexId,
F.IndexName,
F.LogicalFrag,
‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(F.objectid) + ‘, ‘ +
RTRIM(F.indexid) + ‘) ‘ AS action_to_do_1,
‘DBCC DBREINDEX (”’ + RTRIM(T.Table_Catalog) + ‘.’ +
RTRIM(T.Table_Schema) + ‘.’ +
RTRIM(T.Table_Name) + ”’, ‘ +
RTRIM(F.IndexName) + ‘, ‘ +
LTRIM(STR(12)) + ‘) ‘ AS action_to_do_2,
RTRIM(T.Table_Catalog) + ‘.’ + RTRIM(T.Table_Schema) + ‘.’ + RTRIM(T.Table_Name) AS Table_Name
FROM dbo.FragList F
JOIN INFORMATION_SCHEMA.TABLES T On F.ObjectName = T.Table_Name
WHERE (T.TABLE_TYPE = ‘BASE TABLE’)
AND (INDEXPROPERTY(F.ObjectId, F.IndexName, ‘IndexDepth’) > 0)
AND (F.LogicalFrag >= @MaxFrag)
ORDER BY F.LogicalFrag ASC
— Open cursore CUR
OPEN CUR
— Fetch fuori ciclo
FETCH NEXT FROM CUR INTO @index_id, @index_name, @LogicalFrag, @action_to_do_1, @action_to_do_2, @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@Exec = –1)
BEGIN
IF (@DefragMode = 1)
BEGIN
— DBCC INDEXDEFRAG
PRINT ‘Executing DBCC INDEXDEFRAG (0, ‘ + RTRIM(@table_name) + ‘, ‘ + RTRIM(@index_id) + ‘) – fragmentation currently ‘ +
RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’
PRINT @action_to_do_1
— *Exec*
EXEC(@action_to_do_1)
PRINT ”
END
ELSE BEGIN
— DBCC DBREINDEX
PRINT ‘Executing DBCC DBREINDEX (”’ + RTRIM(@table_name) + ”’, ‘ + RTRIM(@index_name) + ‘, ‘ + LTRIM(STR(12)) + ‘) – fragmentation currently ‘ +
RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’
PRINT @action_to_do_2
— *Exec*
EXEC(@action_to_do_2)
PRINT ”
END
END
IF (@Print = –1)
BEGIN
SET @action_to_do_3 = ‘DBCC SHOWCONTIG (”’ + RTRIM(@table_name) + ”’, 1) WITH ALL_INDEXES’
— *Exec*
EXEC(@action_to_do_3)
PRINT ”
IF (@DefragMode = 1)
BEGIN
— DBCC INDEXDEFRAG
PRINT ‘Printing DBCC INDEXDEFRAG (0, ‘ + RTRIM(@table_name) + ‘, ‘ + RTRIM(@index_id) + ‘) – fragmentation currently ‘ +
RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’
PRINT @action_to_do_1
PRINT ”
END
ELSE BEGIN
— DBCC DBREINDEX
PRINT ‘Printing DBCC DBREINDEX (”’ + RTRIM(@table_name) + ”’, ‘ + RTRIM(@index_name) + ‘, ‘ + LTRIM(STR(12)) + ‘) – fragmentation currently ‘ +
RTRIM(CONVERT(varchar(15), @LogicalFrag)) + ‘%’
PRINT @action_to_do_2
PRINT ”
END
END
FETCH NEXT FROM CUR INTO @index_id, @index_name, @LogicalFrag, @action_to_do_1, @action_to_do_2, @table_name
END — WHILE
— Close e deallocate cursore CUR
CLOSE CUR
DEALLOCATE CUR
— Delete tabella temporanea
DROP TABLE dbo.fraglist
END
SET NOCOUNT OFF
END
Per ottenere informazioni sugli indici del database TestDB con Logical Scan Fragmentation maggiore o uguale al 2% sarà sufficiente eseguire:
DECLARE
@MaxFrag AS DECIMAL,
@Exec AS INT,
@Print AS INT,
@DefragMode AS INT,
@FillFactor AS INT
SET @MaxFrag= 2.0
SET @Exec= 0
SET @Print= –1
SET @DefragMode= 1
SET @FillFactor= 10
EXEC dbo.USP_DBCC_IndexDefrag_DBReindex @MaxFrag, @Exec, @Print, @DefragMode, @FillFactor
L’output sarà:
DBCC SHOWCONTIG scanning ‘TMPTAB’ table…
Table: ‘TMPTAB’ (1716201164); index ID: 1, database ID: 7
TABLE level scan performed.
– Pages Scanned…………………………..: 212
– Extents Scanned…………………………: 31
– Extent Switches…………………………: 32
– Avg. Pages per Extent……………………: 6.8
– Scan Density [Best Count:Actual Count]…….: 81.82% [27:33]
– Logical Scan Fragmentation ………………: 3.77%
– Extent Scan Fragmentation ……………….: 12.90%
– Avg. Bytes Free per Page…………………: 3059.6
– Avg. Page Density (full)…………………: 62.20%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Printing DBCC INDEXDEFRAG (0, TestDB.dbo.TMPTAB, 1) – fragmentation currently 4%
DBCC INDEXDEFRAG (0, 1716201164, 1)
Per eseguire la deframmentazione, in modalità DBCC INDEXDEFRAG, degli indici del database TestDB con Logical Scan Fragmentation maggiore o uguale al 2% sarà sufficiente eseguire:
USE TestDB
DECLARE
@MaxFrag AS DECIMAL,
@Exec AS INT,
@Print AS INT,
@DefragMode AS INT,
@FillFactor AS INT
SET @MaxFrag= 2.0
SET @Exec= -1
SET @Print= 0
SET @DefragMode= 1
SET @FillFactor= 10
EXEC dbo.USP_DBCC_IndexDefrag_DBReindex @MaxFrag, @Exec, @Print, @DefragMode, @FillFactor
