SQL – Rebuild Index/Fill factor

This week, I have got a challenge to improve the database performance. In this process I have used many techniques and achieved the 80% performance improvement. In this process, I would like to document one of the mainly used tasks in the database maintenance
 
1. Fragmentation of tables
2. Re Organize/Build Index
3. Fill factor
 
Fragmentation concepts:

Fragmentation is the process of data deviation from the Index order in the table/Index
I have created one diagram for easy of understanding

 
Here Index Order gives how the data inside the index are arranged in order and fragmentation is the percentage of data deviated from the order of the index data
Index order is indirectly relates to fragmentation, as the fragmentation increases the performance of database goes down.
We could say, fragmentation of 100% becomes table scan or logically a heap scan so the query performance will go down.
 
Solution
 
Step 1: Calculate the fragmentation
Use the database, to calculate the fragmentation as
 
Here considered only two generic sys tables and join the sys tables to get the information on tables and fragmentation

SELECT  *
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

SELECT  *
FROM    sys.indexes

SELECT  OBJECT_NAME(IDX.OBJECT_ID) AS TableName,
        IDX.NAME AS IndexName,
        IDXSTAT.avg_fragmentation_in_percent,
        IDXSTAT.*
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') IDXSTAT
        JOIN sys.indexes IDX ON IDXSTAT.OBJECT_ID = IDX.OBJECT_ID
                                AND IDXSTAT.INDEX_ID = IDX.INDEX_ID
WHERE   IDXSTAT.avg_fragmentation_in_percent > 30  -- filter condition

 
To extend this query further, presenting the sys table and the join of the sys tables with the limitation of fragmentation in the where clause.
 
Step 2:
 
Here, I prepared a standard script to loop through all database and all the tables to rebuild index with fill factor.
We could customize the script for particular scenario at the commented sections as
 

DECLARE @Database VARCHAR(255) 
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 75  -- Set the required fill factor

DECLARE DatabaseCursor CURSOR
    FOR SELECT  name
        FROM    master.dbo.sysdatabases
        WHERE   name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) 
     -- Loop through all the databases except the system database
     -- WHERE name IN ('SANDBOX')   -- Enter your database name 
        ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
    BEGIN  
		
     -- Create table cursor
        SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' 
                  + table_schema + ''.'' 
                  + table_name as tableName   
                    FROM ' + @Database
                  + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
        
        EXEC ( @cmd
             )  
        OPEN TableCursor   

        FETCH NEXT FROM TableCursor INTO @Table   
        WHILE @@FETCH_STATUS = 0   
            BEGIN       
       --DBCC DBREINDEX(@Table,' ',@fillfactor)  -- Not using DBCC DBREINDEX       
      
                SET @cmd = 'ALTER INDEX ALL ON ' + @Table
                         + ' REBUILD WITH (FILLFACTOR = '
                         + CONVERT(VARCHAR(3), @fillfactor) + ')'  
                EXEC ( @cmd
                    )  

                FETCH NEXT FROM TableCursor INTO @Table   
            END   

        CLOSE TableCursor   
        DEALLOCATE TableCursor  

        FETCH NEXT FROM DatabaseCursor INTO @Database  
    END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor   

 
Points to consider:
 
1. These scripts prepared bases on only the standard SQL sys tables and approached in SQL 2008
2. Presented only the Rebuild of index here as most of the times I came across the rebuilding of index with fragmentation more than 30
3. Rebuilding of index is an expensive operation and the database will into the lock mode so consider the time bandwidth for the maintenance
4. Don’t recommend to use the script directly without assessing the scenario of problem and the solution approaches as every database is different to each other apart from standard similarities