Manutenzione Indici del DB

[Marco Papo]

Buonasera a tutti,

sui BOL ho trovato questo script che dovrebbe riorganizzare o ricostruire
gli indici in base alla percentuale di frammentazione degli stessi.

-- ensure a USE <databasename> statement has been executed first.
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 EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- 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 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.
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 EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO

Lo eseguito su una copia del mio DB e ho riscontrato che in taluni casi ha
deframmentato gli indici, mentre alcuni inidici o li ha tralasciati o ne ha
ridotto la frammentazione ma in maniera poco significativa.
Guardate per esempio, il prima e poi di, questa tabella

NOMESITUAZIONE PRIMA DELLO SCRIPTMESSAGGIO SCRIPTSITUAZIONE DOPO LO SCRIPT
aaaaaTBL_NC_da_clienti_PK

66,6666666666667Executed ALTER INDEX aaaaaTBL_NC_da_clienti_PK ON
dbo.TBL_NC_da_clienti REBUILD66,6666666666667
ID66,6666666666667Executed ALTER INDEX ID ON dbo.TBL_NC_da_clienti
REBUILD66,6666666666667
IDArticolo88,8888888888889Executed ALTER INDEX IDArticolo ON
dbo.TBL_NC_da_clienti REBUILD80
IDCliente80Executed ALTER INDEX IDCliente ON dbo.TBL_NC_da_clienti
REBUILD66,6666666666667
IDNC_Cliente83,3333333333333Executed ALTER INDEX IDNC_Cliente ON
dbo.TBL_NC_da_clienti REBUILD80
IDRapporto_cliente85,7142857142857Executed ALTER INDEX IDRapporto_cliente ON
dbo.TBL_NC_da_clienti REBUILD80
IDRapporto_cliente_File28,5714285714286Executed ALTER INDEX
IDRapporto_cliente_File ON dbo.TBL_NC_da_clienti REORGANIZE33,3333333333333
IDRapporto_cliente_File_Indirizzo20Executed ALTER INDEX
IDRapporto_cliente_File_Indirizzo ON dbo.TBL_NC_da_clienti
REORGANIZE22,2222222222222


di cui allego script di creazione:

/****** Object: Table [dbo].[TBL_NC_da_clienti] Script Date: 02/21/2008
14:51:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_NC_da_clienti](
[IDNC] [int] IDENTITY(1,1) NOT NULL,
[IDNC_Cliente] [nvarchar](50) NULL,
[Data] [datetime] NULL,
[IDCliente] [int] NULL DEFAULT ((0)),
[Data_Ricevimento_Notifica] [datetime] NULL,
[IDRapporto_cliente] [nvarchar](20) NULL,
[Data_rapporto_cliente] [datetime] NULL,
[IDRapporto_cliente_File] [nvarchar](255) NULL,
[IDRapporto_cliente_File_Indirizzo] [nvarchar](255) NULL,
[Rif_DDT] [nvarchar](50) NULL,
[Rif_DDT_Data] [datetime] NULL,
[IDArticolo] [nvarchar](7) NULL,
[Quantità_fornitura] [int] NULL DEFAULT ((0)),
[Quantità_NC_Pz] [int] NULL DEFAULT ((0)),
[Quantità_NC_Perc] [int] NULL DEFAULT ((0)),
[Conseguenze_fornitura] [nvarchar](50) NULL,
[Causale_gestionale] [nvarchar](50) NULL,
[Notificare_AC] [bit] NULL DEFAULT ((0)),
[Notificare_a] [nvarchar](255) NULL,
[Intraprendere_AC_AP] [bit] NULL DEFAULT ((0)),
[Rif_AC_AP] [nvarchar](50) NULL,
[Esaminata] [bit] NULL DEFAULT ((0)),
[Note] [ntext] NULL,
[Riemisione_ordine] [bit] NULL DEFAULT ((0)),
[DateStamp] [timestamp] NOT NULL,
CONSTRAINT [aaaaaTBL_NC_da_clienti_PK] PRIMARY KEY NONCLUSTERED
(
[IDNC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ID] ON [dbo].[TBL_NC_da_clienti]
(
[IDNC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDArticolo] ON [dbo].[TBL_NC_da_clienti]
(
[IDArticolo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDCliente] ON [dbo].[TBL_NC_da_clienti]
(
[IDCliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDNC_Cliente] ON [dbo].[TBL_NC_da_clienti]
(
[IDNC_Cliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDRapporto_cliente] ON [dbo].[TBL_NC_da_clienti]
(
[IDRapporto_cliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDRapporto_cliente_File] ON
[dbo].[TBL_NC_da_clienti]
(
[IDRapporto_cliente_File] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDRapporto_cliente_File_Indirizzo] ON
[dbo].[TBL_NC_da_clienti]
(
[IDRapporto_cliente_File_Indirizzo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

La cosa strana se eseguo il Reorganize All da Management Studio il
risultato non cambia?
Coma mai? qualche idea?

[Luca Bianchi]

Post by Marco Papo
Coma mai? qualche idea?

Quanti record hanno quelle tabelle? Se una tabella e/o una struttura di
indice non clustered non arrivano ad occupare una pagina da 8 kb non potrà
mai modificare il suo livello di frammentazione. Se hai una tabella (o una
struttura di indice non clustered) che occupano soltanto 4 kb, quindi il 50%
di una pagina dati, puoi deframmentare l'indice clustered (ovvero la
tabella) senza che riuscirai a coprire più del 50% dello spazio complessivo.
Lo stesso vale per tabelle che si spalmano su qualche decina/centinaia di
pagine ma in cui gli indici non clustered non utilizzino più di una o due
pagine...

Post by Marco Papo
Marco Papetti

Bye

[m***@gmail.com]

Post by Luca Bianchi

Post by Marco Papo
Coma mai? qualche idea?

Quanti record hanno quelle tabelle? Se una tabella e/o una struttura di
indice non clustered non arrivano ad occupare una pagina da 8 kb non potrà
mai modificare il suo livello di frammentazione. Se hai una tabella (o una
struttura di indice non clustered) che occupano soltanto 4 kb, quindi il 50%
di una pagina dati, puoi deframmentare l'indice clustered (ovvero la
tabella) senza che riuscirai a coprire più del 50% dello spazio complessivo.
Lo stesso vale per tabelle che si spalmano su qualche decina/centinaia di
pagine ma in cui gli indici non clustered non utilizzino più di una o due
pagine...

Ciao Luca,

grazie per la risposta.
La tabella ha 1109 records. L'Index Space è di 0.484 MB. Quindi come
osservi giustamente, "perchè sistemare, se le cose da cercare sono
così poche."
Non ci avevo pensato!

Nello script che ho postato ho provato ha specificare l'opzione ONLINE
ON, ma non avendo SQL Server 2005 Enterprise Edition, non posso
utilizzarla. Quindi modificherò lo script per effettuare solo il
REORGANIZE, in quanto nella mia azienda il DB lavora 24 su 24.

Sui BOL ho trovato anche questo scritp che utilizza invece DBCC
INDEXDEFRAG.

/*Perform a 'USE <database name>' to select the database in which to
run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr varchar(400);
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #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);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Uno è meglio dell'altro?

Sul mio DB il flag "Auto Update Statistics" è impostato a True, e
quindi le statistiche si aggiornano in automatico, devo fare qualcosa
di altro per ottimizzare il DB?

Grazie mille.
Ciao Marco

[Luca Bianchi]

Post by m***@gmail.com
Quindi modificherò lo script per effettuare solo il
REORGANIZE, in quanto nella mia azienda il DB lavora 24 su 24.

Io lascerei lo script così come è.
Se il db lavora h24, di sicuro non tutte le tabelle saranno utilizzate senza
sosta per tutto il tempo.
Se anche la rebuild di 10 tabelle su 10000 dovesse fallire, per un puro
calcolo delle probabilità la settimana successiva (o comunque la volta
successiva) quelle 10 verranno ricostruite (e magari ne falliranno altre
10). Non è un dramma... :-)

Post by m***@gmail.com
Sui BOL ho trovato anche questo scritp che utilizza invece DBCC
INDEXDEFRAG.

INDEXDEFRAG non è altro che l'antenato dell'ALTER INDEX...WITH REORGANIZE.
Non c'è nessuna ragione per continuare ad utilizzarlo

Post by m***@gmail.com
Sul mio DB il flag "Auto Update Statistics" è impostato a True, e
quindi le statistiche si aggiornano in automatico, devo fare qualcosa
di altro per ottimizzare il DB?

Semmai puoi attivarti per meglio calibrare la frequenza di ricostruzione
delle tabelle in maniera selettiva e per trovare il miglior fillfactor per
ciascun indice sulla base della frequenza di ricostruzione. Se fai una
ricerca tra i post di questo ng ricordo che Marcello in più occasioni ha
pubblicato una procedura in grado di calibrare il fill-factor sulla base dei
valori "storici". In ogni caso si tratta di una attività di fine tuning che
potrebbe richiedere più oneri che benefici. Vedi tu se è il caso... magari
anche solo per motivi didattici...

Post by m***@gmail.com
Grazie mille.
Ciao Marco

Bye

[Marco Papo]

Post by Luca Bianchi

Post by m***@gmail.com
Quindi modificherò lo script per effettuare solo il
REORGANIZE, in quanto nella mia azienda il DB lavora 24 su 24.

Io lascerei lo script così come è.
Se il db lavora h24, di sicuro non tutte le tabelle saranno utilizzate
senza sosta per tutto il tempo.
Se anche la rebuild di 10 tabelle su 10000 dovesse fallire, per un puro
calcolo delle probabilità la settimana successiva (o comunque la volta
successiva) quelle 10 verranno ricostruite (e magari ne falliranno altre
10). Non è un dramma... :-)

Giusta osservazione...
Fatta eccezione per una "maledetta" tabella, che è anche la più grande, dove
scrivono costantemente macchine utensili e operatori umani... e li la
probabilità di trovarla libera è molto bassa....
per quella metterò un DEFRAG adHoc

Post by Luca Bianchi
Semmai puoi attivarti per meglio calibrare la frequenza di ricostruzione
delle tabelle in maniera selettiva e per trovare il miglior fillfactor per
ciascun indice sulla base della frequenza di ricostruzione. Se fai una
ricerca tra i post di questo ng ricordo che Marcello in più occasioni ha
pubblicato una procedura in grado di calibrare il fill-factor sulla base
dei valori "storici". In ogni caso si tratta di una attività di fine
tuning che potrebbe richiedere più oneri che benefici. Vedi tu se è il
caso... magari anche solo per motivi didattici...

Si, lo ricordo... l'ho salvato nella cartella "Query di Utilità", ma per il
momento mi accontenterò di quanto scritto nei BOL, se un giorno ve ne sarà
il bisogno si vedrà!

Post by Luca Bianchi

Post by m***@gmail.com
Grazie mille.
Ciao Marco

Bye

Ciao e grazie.

Post by Luca Bianchi
--
Luca Bianchi
Microsoft MVP - SQL Server
http://community.ugiss.org/blogs/lbianchi
--

Marco Papetti
www.shortchannel.it Il software di commercio elettronico OpenSource
www.mondoideale.com - Prodotti naturali e biologici.