Toto je starší verze dokumentu!

Reindexace databáze WSUS

Spuštěním tohoto skriptu provedete základní úlohy údržby na databázi SUSDB:

* Identifikace fragmentovaných indexů a jejich defragmentace.
* Aktualizace potenciálně zastaralých statistik tabulek.

USE SUSDB; 
GO 
SET NOCOUNT ON; 

-- Rebuild or reorganize indexes based on their fragmentation levels 
DECLARE @work_to_do TABLE ( 
    objectid int 
    , indexid int 
    , pagedensity float 
    , fragmentation float 
    , numrows int

)

DECLARE @objectid int; DECLARE @indexid int; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @numrows int DECLARE @density float; DECLARE @fragmentation float; DECLARE @command nvarchar(4000); DECLARE @fillfactorset bit DECLARE @numpages int

– Select indexes that need to be defragmented based on the following – * Page density is low – * External fragmentation is high in relation to index size PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121) INSERT @work_to_do SELECT

  f.object_id 
  , index_id 
  , avg_page_space_used_in_percent 
  , avg_fragmentation_in_percent 
  , record_count

FROM

  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f

WHERE

  (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
  or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
  or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count) FROM

  @work_to_do AS fi 
  INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
  INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

– Declare the cursor for the list of indexes to be processed. DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

– Open the cursor. OPEN curIndexes

– Loop through the indexes WHILE (1=1) BEGIN

  FETCH NEXT FROM curIndexes 
  INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
  IF @@FETCH_STATUS < 0 BREAK;

SELECT

      @objectname = QUOTENAME(o.name) 
      , @schemaname = QUOTENAME(s.name) 
  FROM  
      sys.objects AS o 
      INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
  WHERE  
      o.object_id = @objectid;

SELECT

      @indexname = QUOTENAME(name) 
      , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
  FROM  
      sys.indexes 
  WHERE 
      object_id = @objectid AND index_id = @indexid;

IF ¹⁾

¹⁾

@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)

      SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
  ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
      SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
  ELSE 
      SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
  PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
  EXEC (@command); 
  PRINT convert(nvarchar, getdate(), 121) + N' Done.';

END – Close and deallocate the cursor. CLOSE curIndexes; DEALLOCATE curIndexes; IF EXISTS (SELECT * FROM @work_to_do) BEGIN

  PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
  SELECT @numpages = @numpages - sum(ps.used_page_count) 
  FROM 
      @work_to_do AS fi 
      INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
      INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

  PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))

END GO –Update all statistics PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121) EXEC sp_updatestats PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121) GO