Rozdíly

Zde můžete vidět rozdíly mezi vybranou verzí a aktuální verzí dané stránky.

--- sql:reidexsusdb [12.08.2021 12:53] – Ladislav Vojtíšek
+++ sql:reidexsusdb [12.08.2021 13:00] (aktuální) – Ladislav Vojtíšek
@@ Řádek 16: / Řádek 16: @@
       , fragmentation float
       , numrows int
-)
+  )
-DECLARE @objectid int;
+  DECLARE @objectid int;
-DECLARE @indexid int;
+  DECLARE @indexid int;
-DECLARE @schemaname nvarchar(130);
+  DECLARE @schemaname nvarchar(130);
-DECLARE @objectname nvarchar(130);
+  DECLARE @objectname nvarchar(130);
-DECLARE @indexname nvarchar(130);
+  DECLARE @indexname nvarchar(130);
-DECLARE @numrows int
+  DECLARE @numrows int
-DECLARE @density float;
+  DECLARE @density float;
-DECLARE @fragmentation float;
+  DECLARE @fragmentation float;
-DECLARE @command nvarchar(4000);
+  DECLARE @command nvarchar(4000);
-DECLARE @fillfactorset bit
+  DECLARE @fillfactorset bit
-DECLARE @numpages int
+  DECLARE @numpages int
--- Select indexes that need to be defragmented based on the following
+  -- Select indexes that need to be defragmented based on the following
--- * Page density is low
+  -- * Page density is low
--- * External fragmentation is high in relation to index size
+  -- * External fragmentation is high in relation to index size
-PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
+  PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
-INSERT @work_to_do
+  INSERT @work_to_do
-SELECT
+  SELECT
-    f.object_id
+      f.object_id
-    , index_id
+      , index_id
-    , avg_page_space_used_in_percent
+      , avg_page_space_used_in_percent
-    , avg_fragmentation_in_percent
+      , avg_fragmentation_in_percent
-    , record_count
+      , record_count
-FROM
+  FROM
-    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
+      sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
-WHERE
+  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)
+      (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 > 50 and f.avg_fragmentation_in_percent > 15.0)
-    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.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 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))
-PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
+  PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)
-SELECT @numpages = sum(ps.used_page_count)
+  SELECT @numpages = sum(ps.used_page_count)
-FROM
+  FROM
-    @work_to_do AS fi
+      @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.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
+      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 the cursor for the list of indexes to be processed.
-DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
+  DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do
--- Open the cursor.
+  -- Open the cursor.
-OPEN curIndexes
+  OPEN curIndexes
--- Loop through the indexes
+  -- Loop through the indexes
-WHILE (1=1)
+  WHILE (1=1)
-BEGIN
+  BEGIN
-    FETCH NEXT FROM curIndexes
+      FETCH NEXT FROM curIndexes
-    INTO @objectid, @indexid, @density, @fragmentation, @numrows;
+      INTO @objectid, @indexid, @density, @fragmentation, @numrows;
-    IF @@FETCH_STATUS < 0 BREAK;
+      IF @@FETCH_STATUS < 0 BREAK;
-    SELECT
+      SELECT
-        @objectname = QUOTENAME(o.name)
+          @objectname = QUOTENAME(o.name)
-        , @schemaname = QUOTENAME(s.name)
+          , @schemaname = QUOTENAME(s.name)
-    FROM
+      FROM
-        sys.objects AS o
+          sys.objects AS o
-        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
+          INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
-    WHERE
+      WHERE
-        o.object_id = @objectid;
+          o.object_id = @objectid;
-    SELECT
+      SELECT
-        @indexname = QUOTENAME(name)
+          @indexname = QUOTENAME(name)
-        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
+          , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
-    FROM
+      FROM
-        sys.indexes
+          sys.indexes
-    WHERE
+      WHERE
-        object_id = @objectid AND index_id = @indexid;
+          object_id = @objectid AND index_id = @indexid;
-    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
+      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';
+          SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
-    ELSE IF @numrows >= 5000 AND @fillfactorset = 0
+      ELSE IF @numrows >= 5000 AND @fillfactorset = 0
-        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
+          SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
-    ELSE
+      ELSE
-        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
+          SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
-    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
+      PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
-    EXEC (@command);
+      EXEC (@command);
-    PRINT convert(nvarchar, getdate(), 121) + N' Done.';
+      PRINT convert(nvarchar, getdate(), 121) + N' Done.';
-END
+  END
--- Close and deallocate the cursor.
+  -- Close and deallocate the cursor.
-CLOSE curIndexes;
+  CLOSE curIndexes;
-DEALLOCATE curIndexes;
+  DEALLOCATE curIndexes;
-IF EXISTS (SELECT * FROM @work_to_do)
+  IF EXISTS (SELECT * FROM @work_to_do)
-BEGIN
+  BEGIN
-    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
+      PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
-    SELECT @numpages = @numpages - sum(ps.used_page_count)
+      SELECT @numpages = @numpages - sum(ps.used_page_count)
-    FROM
+      FROM
-        @work_to_do AS fi
+          @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.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
+          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))
+      PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
-END
+  END
-GO
+  GO
---Update all statistics
+  --Update all statistics
-PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
+  PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
-EXEC sp_updatestats
+  EXEC sp_updatestats
-PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
+  PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
-GO
+  GO