Script for rebuilding and reindexing the fragmented index

Question

Can anyone provide the script for rebuilding and re-indexing the fragmented index when  avg fragmentation in percent  exceeds certain limits  better if cursor is not used

User · Answer

Query for REBUILD REORGANIZE Indexes   30  lt   Rebuild   5  lt   Reorganize    5   do nothing   Query   SELECT OBJECT NAME ind OBJECT ID  AS TableName   ind name AS IndexName  indexstats index type desc AS IndexType   indexstats avg fragmentation in percent   ALTER INDEX     QUOTENAME ind name       ON    QUOTENAME object name ind object id      CASE    WHEN indexstats avg fragmentation in percent gt 30 THEN   REBUILD            WHEN indexstats avg fragmentation in percent gt  5 THEN  REORGANIZE          ELSE NULL END as  SQLQuery   -- if  lt 5 not required  so no query needed 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  WHERE  --indexstats avg fragmentation in percent   e g   gt 10  you can specify any number in percent  ind Name is not null  ORDER BY indexstats avg fragmentation in percent DESC   Output  TableName      IndexName            IndexType              avg fragmentation in percent SQLQuery --------------------------------------------------------------------------------------- ------------------------------------------------------ Table1         PK Table1            CLUSTERED INDEX        75                           ALTER INDEX  PK Table1  ON  Table1  REBUILD  Table1         IX Table1 col1 col2  NONCLUSTERED INDEX     66 6666666666667             ALTER INDEX  IX Table1 col1 col2  ON  Table1  REBUILD  Table2         IX Table2            NONCLUSTERED INDEX     10                           ALTER INDEX  IX Table2   ON  Table2  REORGANIZE Table2         IX Table2            NONCLUSTERED INDEX     3                            NULL

User · Answer

To rebuild use   ALTER INDEX   NAME OF INDEX   ON   NAME OF TABLE   REBUILD   or to reorganize use   ALTER INDEX   NAME OF INDEX   ON   NAME OF TABLE   REORGANIZE   Reorganizing should be used at lower   lt 30   fragmentations but only rebuilding  which is heavier to the database  cuts the fragmentation down to 0   For further information see https   msdn microsoft com en-us library ms189858 aspx

User · Answer

Two solutions  One simple and one more advanced    Introduction  There are two solutions available to you depending on the severity of your issue  Replace with your own values  as follows    Replace XXXMYINDEXXXX with the name of an index  Replace XXXMYTABLEXXX with the name of a table  Replace XXXDATABASENAMEXXX with the name of a database    Solution 1  Indexing  Rebuild all indexes for a table in offline mode  ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD   Rebuild one specified index for a table in offline mode  ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD   Solution 2  Fragmentation  Fragmentation is an issue in tables that regularly have entries both added and removed    Check fragmentation percentage  SELECT       ips  index id        idx  name        ips  avg fragmentation in percent  FROM         sys dm db index physical stats DB ID N XXXMYDATABASEXXX    OBJECT ID N XXXMYTABLEXXX    NULL  NULL  NULL  AS  ips      INNER JOIN sys indexes AS  idx  ON  ips   object id     idx   object id  AND  ips   index id     idx   index id    Fragmentation 5  30   If the fragmentation value is greater than 5   but less than 30  then it is worth reorganising indexes   Reorganise all indexes for a table  ALTER INDEX ALL ON XXXMYTABLEXXX REORGANIZE   Reorganise one specified index for a table  ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REORGANIZE   Fragmentation 30    If the fragmentation value is 30  or greater then it is worth rebuilding then indexes in online mode   Rebuild all indexes in online mode for a table  ALTER INDEX ALL ON XXXMYTABLEXXX REBUILD WITH  ONLINE   ON    Rebuild one specified index in online mode for a table  ALTER INDEX XXXMYINDEXXXX ON XXXMYTABLEXXX REBUILD WITH  ONLINE   ON

User · Answer

I have found the following script is very good at maintaining indexes  you can have this scheduled to run nightly or whatever other timeframe you wish   http   sqlfool com 2011 06 index-defrag-script-v4-1

User · Answer

Here is the modified script which i took from http   www foliotek com devblog sql-server-optimization-with-index-rebuilding which i found useful to post here   Although it uses a cursor and i know what is the main problem with cursors it can be easily converted to a cursor-less version   It is well-documented and you can easily read through it and modify to your needs      IF OBJECT ID  tempdb   work to do   IS NOT NULL          DROP TABLE tempdb   work to do  BEGIN TRY --BEGIN TRAN  use yourdbname  -- Ensure a USE  statement has been executed first       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  pagecount INT      DECLARE  command NVARCHAR 4000        DECLARE  page count minimum SMALLINT     SET  page count minimum   50      DECLARE  fragmentation minimum FLOAT     SET  fragmentation minimum   30 0  -- Conditionally select tables and indexes from the sys dm db index physical stats function -- and convert object and index IDs to names       SELECT  object id AS objectid               index id AS indexid               partition number AS partitionnum               avg fragmentation in percent AS frag               page count AS page count     INTO     work to do     FROM    sys dm db index physical stats DB ID    NULL  NULL  NULL                                              LIMITED       WHERE   avg fragmentation in percent  gt   fragmentation minimum             AND index id  gt  0             AND page count  gt   page count minimum   IF CURSOR STATUS  global    partitions    gt   -1 BEGIN  PRINT  partitions CURSOR DELETED        CLOSE partitions     DEALLOCATE partitions END -- Declare the cursor for the list of partitions to be processed      DECLARE partitions CURSOR LOCAL     FOR         SELECT            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   pagecount               IF   FETCH STATUS  lt  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               SET  command   N ALTER INDEX      indexname   N  ON                      schemaname   N       objectname   N  REBUILD                IF  partitioncount  gt  1                 SET  command    command   N  PARTITION                         CAST  partitionnum AS NVARCHAR 10                 EXEC   command               --print   command     uncomment for testing              PRINT N Rebuilding index      indexname     on table                      objectname              PRINT N   Fragmentation      CAST  frag AS VARCHAR 15                PRINT N   Page Count         CAST  pagecount AS VARCHAR 15                PRINT N             END   -- Close and deallocate the cursor      CLOSE partitions      DEALLOCATE partitions   -- Drop the temporary table      DROP TABLE  work to do  --COMMIT TRAN  END TRY BEGIN CATCH --ROLLBACK TRAN     PRINT  ERROR ENCOUNTERED     ERROR MESSAGE   END CATCH

User · Answer

The real answer  in 2016 and 2017  is  Use Ola Hallengren s scripts   https   ola hallengren com sql-server-index-and-statistics-maintenance html  That is all any of us need to know or bother with  at this point in our mutual evolution

[sql-server] Script for rebuilding and reindexing the fragmented index?

Examples related to sql-server

Examples related to indexing

Examples related to fragmentation