De-fragmenting indexes in SQL Server

One of the key tasks I have done to improve performance on SQL Server database is defragmenting indexes in a SQL database.

Indexes are very crucial for improving database performance because they make database searches efficient and faster.

Index problems

What are some of the issues indexes may have in a database?

There are some issues usually associated with indexes. Here are some of them:

Indexes with high fragmentation ratio: Indexes with fragmentation will cause a huge performance issue on the database.

Too many indexes on a table: Though indexes are desirable, too much of them may be a problem in a database. Index redundancy can occur from too many indexes on a table. Also, it may cause confusion as to which index to use for sql execution. Also, update statements will take a very long time to complete.

Hypothetical indexes: These are indexes created during a SQL tuning operation to improve performance. They may not be needed after the query is executed.

Unnecessary index: creating an index on a very small table is not recommended because it amounts to misuse of resources. 

Databases identified with one or more tables, with indexes that may require update statistics: If statistics of an index is not updated, there could be a performance issue.

There are foreign keys with no supporting indexes: It is best practice to create an index to support foreign keys.

Indexes have been identified with an index key larger than the recommended size (900 bytes): Index keys should not be greater than 900 bytes.

High number of Full Scans versus Index Searches: This is a clear case of lack of index on a table.

Rebuilding an index

You can use the GUI tool to rebuild an index.

Retrieving Fragmentation info on all indexes

--Use this script to get all the information about all the tables and indexes in a database. You should use this list to prepare a collation of all indexes to defrag in a database. 

You can also use this list to set a threshold of defragmentation based on the trend.

-- Declare database

use Patientdatadb

go

-- Show fragmentation per table

SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

Result should list all the indexes on all the tables in each database along with the fragmented percentage. 

DeFragmenting all indexes in a database

To defragment all indexes in a database that are fragmented above a declared threshold, you can use the script below:

Please note that my threshold in my script is set to 30%

-- Declare database

use Patientdatadb

go 

-- Declare variables

SET NOCOUNT ON;

DECLARE @tablename varchar(255);

DECLARE @execstr   varchar(400);

DECLARE @objectid  int;

DECLARE @indexid   int;

DECLARE @frag      decimal;

DECLARE @maxfrag   decimal;

-- Declare maximum fragmentation to allow

SELECT @maxfrag = 30.0;

-- Declare a cursor

DECLARE tables CURSOR FOR

   SELECT TABLE_SCHEMA + '.' + TABLE_NAME

   FROM INFORMATION_SCHEMA.TABLES

   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table

CREATE TABLE #fraglist (

   ObjectName char(255),

   ObjectId int,

   IndexName char(255),

   IndexId int,

   Lvl int,

   CountPages int,

   CountRows int,

   MinRecSize int,

   MaxRecSize int,

   AvgRecSize int,

   ForRecCount int,

   Extents int,

   ExtentSwitches int,

   AvgFreeBytes int,

   AvgPageDensity int,

   ScanDensity decimal,

   BestCount int,

   ActualCount int,

   LogicalFrag decimal,

   ExtentFrag decimal);

-- Open the cursor

OPEN tables;

-- Loop through all the tables in the database

FETCH NEXT

   FROM tables

   INTO @tablename;

WHILE @@FETCH_STATUS = 0

BEGIN

-- Do the showcontig of all indexes of the table

   INSERT INTO #fraglist

   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');

   FETCH NEXT

      FROM tables

      INTO @tablename;

END;

-- Close and deallocate the cursor

CLOSE tables;

DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged

DECLARE indexes CURSOR FOR

   SELECT ObjectName, ObjectId, IndexId, LogicalFrag

   FROM #fraglist

   WHERE LogicalFrag >= @maxfrag

      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor

OPEN indexes;

-- Loop through the indexes

FETCH NEXT

   FROM indexes

   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0

BEGIN

   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',

      ' + RTRIM(@indexid) + ') - fragmentation currently '

       + RTRIM(CONVERT(varchar(15),@frag)) + '%';

   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',

       ' + RTRIM(@indexid) + ')';

   EXEC (@execstr);

   FETCH NEXT

      FROM indexes

      INTO @tablename, @objectid, @indexid, @frag;

END;

-- Close and deallocate the cursor

CLOSE indexes;

DEALLOCATE indexes;

-- Delete the temporary table

DROP TABLE #fraglist;

GO