PikoDrakův Blog

Zkusil jsem se podívat na stav indexů v databázi vCentra a výsledek se mi vůbec nelíbil. Po chvíli googlení a zkoušení jsem přišel na jednoduchý postup, jak narovnat setříděnost indexů všech tabulek v rámci jedné databáze (ještě budu hledat udělátko na rebuild indexů v rámci instance).

Nejdříve je dobré vědět, jak na tom s indexaci jsme

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
AND ind.index_id > 0
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Poté spustit rebuild

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

UPDATE
Tak jsem nalezl script na rebuild všech tabulek v rámci instance

— Index rebuild online on all databases on an instance
SET NOCOUNT ON
DECLARE @db as sysname;
DECLARE @db_id as int;
DECLARE @cmd1 as nvarchar(1000);

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @command nvarchar(4000);
DECLARE @errornumber int;
DECLARE DatabaseList CURSOR FAST_FORWARD FOR

SELECT [name], [database_id] FROM sys.databases
WHERE [name] NOT IN (‘tempdb’,’model’)
AND state_desc = ‘ONLINE’
ORDER BY [name]

OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @db, @db_id
WHILE (@@fetch_status = 0)
BEGIN
PRINT N’ ‘;
PRINT N’Database: ‘ + @db;
— Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
— and convert object and index IDs to names.
EXEC (‘USE ‘ + @db + ‘;
SELECT p.object_id AS objectid
, p.index_id AS indexid
, o.name AS objectName
, s.name AS schemaName
, i.name AS indexName
INTO ##work_to_do
FROM sys.dm_db_index_physical_stats (‘ + @DB_ID + ‘, NULL, NULL , NULL, ”LIMITED”) AS p
INNER JOIN sys.objects as o
ON p.object_id = o.object_id
INNER JOIN sys.schemas as s
ON s.schema_id = o.schema_id
INNER JOIN sys.indexes i
ON p.object_id = i.object_id
AND i.index_id = p.index_id
WHERE p.avg_fragmentation_in_percent > 10.0 AND p.index_id > 0
AND p.index_id < 25000 AND p.page_count > 1000;’)

— Declare the cursor for the list of fragmented indexes to be processed.
DECLARE FragmentedIndexes CURSOR FOR SELECT * FROM ##work_to_do;

— Open the cursor.
OPEN FragmentedIndexes;

— Loop through the indexes.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM FragmentedIndexes
INTO @objectid, @indexid, @objectname, @schemaname, @indexname;
IF @@FETCH_STATUS < 0 BREAK; -- Check for invalid datatypes for online rebuild -- If those datatypes are found, do normal rebuild BEGIN TRY SET @command = N'USE ' + @db + '; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE=ON)'; EXEC (@command); PRINT N'Executed: ' + @command; END TRY BEGIN CATCH SELECT @errornumber = ERROR_NUMBER() IF @errornumber = 2725 BEGIN SET @command = N'USE ' + @db + '; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; EXEC (@command); PRINT N'Executed: ' + @command; END END CATCH END; -- Close and deallocate the cursor. CLOSE FragmentedIndexes; DEALLOCATE FragmentedIndexes; -- Drop the temporary table. DROP TABLE ##work_to_do; FETCH NEXT FROM DatabaseList INTO @db, @db_id END CLOSE DatabaseList; DEALLOCATE DatabaseList; [/code]