MS Admin

Tato stránka je pouze pro čtení. Můžete si pouze prohlédnout zdrojový kód, ale ne ho měnit. Zeptejte se správce, pokud si myslíte, že něco není v pořádku.
====== 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