CodFusion

I ran into a fun problem today where a developer was rebuilding his indexes. So many people know that it's a good idea to use indexing on their tables to speed up the database performance, etc. Those indexes do fragment over time for example with delete and insert calls to the object.

Here's a short article on how the fragmentation occurs..
http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx

If you want to see how fragmented your indexes are, you can use the DBCC SHOWCONTIG command or doing something like the following..

DECLARE @db_id int;
SET @db_id = DB_ID();
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent < 10

The above SQL will find all the indexes that have over 10 percent fragmentation and list out their id, fragmentation and page count.

So, naturally, it's a good idea to rebuild or reorganize your indexes from time to time. In this particular case, the developer was doing the following, early every morning..

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 90)"

I was pretty surprised by this, but apparently a lot of bloggers have been promoting this solution. So what's wrong with it? Well, basically this call is doing a full rebuild of every index in the database regardless if it's fragmented or not. Keep in mind, rebuilding an index isn't a trival thing, specially if we're dealing with large indexes. It's very heavy handed and you really want to do it on just the objects that have become fragmented. Many indexes in a database will not ever get fragmented, and the process of rebuilding the entire index does affect the performance of the site during the operation. So you will want this to be as quick and painless as possible. So on really small databases you could probably get away with doing the previous call, but when the database grows you're guaranteed to have issues.

To add to this particular problem, the reindexing was being done around the time we do our full database backups. At FusionLink, we do transactional backups every 2 hours and then a full backup at night. Needless to say, that didn't help. Just a reminder for people, if you are doing things like this, it's a really good idea to check with your hosting provider. In many cases, they may be monitoring the fragmentation anyway and even if they aren't you may end up conflicting with a backup schedule like in this case.

So thankfully, Microsoft has given us plenty of ways to solve this problem and to do so with far more finesse. Here's a modified solution I have that originally came from Microsoft..

SET NOCOUNT ON;
DECLARE @db_id int;
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);

SET @db_id = DB_ID();
-- 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
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id < 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR 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;
        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

Feel free to modify this for your particular needs. So what are we doing here? We get the current database id and then find all the indexes that are at least 10 percent fragmented. We then do a rebuild of the indexes that are currently over 30% fragmented and only do a light reorganization for indexes between 10-30% fragementation.

A couple of things to keep in mind. First, there's always fragmentation. It requires your attention, but don't expect to kill it off completely. There's almost no reason to do a rebuild every day. Once a week should be fine for most circumstances. Another issue, is in some situations the tables are actually over indexed in which case, rebuilding the indexes won't give you much mileage. If you do run into that problem, you'll need to review your indexes and see about taking some out.