Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Thursday, May 29, 2014

T-SQL Script to Rebuild Indexes in a Database

T-SQL Script to rebuild indexes in a database:

SET nocount ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @dbid SMALLINT;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbid = Db_id();

SELECT [object_id]                  AS objectid,
       index_id                     AS indexid,
       partition_number             AS partitionnum,
       avg_fragmentation_in_percent AS frag,
       page_count
INTO   #work_to_do
FROM   sys.Dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, N'LIMITED')
WHERE  avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
       AND index_id > 0 -- Ignore heaps
       AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
  SELECT objectid,
         indexid,
         partitionnum,
         frag
  FROM   #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE ( 1 = 1 )
  BEGIN
      FETCH next FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

      IF @@FETCH_STATUS < 0
        BREAK;

      SELECT @objectname = Quotename(o.name),
             @schemaname = Quotename(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 = Quotename(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          SET @command = N'ALTER INDEX ' + @indexname + N' ON '                         + @schemaname + N'.' + @objectname + N' REORGANIZE';        IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                       + @schemaname + N'.' + @objectname + N' REBUILD';

      IF @partitioncount > 1
        SET @command = @command + N' PARTITION='
                       + Cast(@partitionnum AS NVARCHAR(10));

      EXEC (@command);

      PRINT N'Executed: ' + @command;
  END

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;

go

No comments:

Post a Comment