Whew – it’s been a while since I last posted (not to mention my postings this year have been a little sparse), but I am now settled back in Austin, Texas after having spent the last two years down in San Antonio. My wife and I have been busy selling the house down there, getting everyhting packed, working my client projects and transitioning to the Austin branch of my company – but, its all finally over, so I have no more excuses for delayed postings J.

 

I’ve been doing a lot of interviewing lately for some open positions at Catapult, and one of my favorite subject areas to ask any SQL/BI candidate we bring is on indexes and index strategy. So many like to leave this area of the database stack for the DBA, however, I will always be of the mind that the developer of the solution has a responsibility to know the basics of SQL Server architecture if they plan on using it in their solution and thus plan for performance. I’ll probably post a lot more about SQL performance since I have been doing a lot recently with various clients – no particluar theme or organization to it, but just post what I have been working with at the time.

 

One of the interesting items I like to point out to both developers and DBAs is about index fragmentation. Since a clustered index stores data in the physical sort order, a table that has a lot of CRUD operations against it will most likely have a lot of page splits as SQL inserts and removes rows from that table. Even more so with non-clustered indexes as the B-tree in a non-clustered index points to the index page and not a data page.

 

The script below illustrates a simple method you can use to defragment your indexes. While you can use the dbcc command to defrag your index, it is better to use the new SQL 2005 command alter index. Depending on your SQL 2005 version, you have various options you can set when using this command verus using the old dbcc commands, the most important of which is to set online rebuilds for your more fragmented indexes.

 

So how do you know if you need to defrag an index? Pretty simple, if your index is more than 10% fragmented, particularly for large tables, then you need to do something about it. How fragmented depends on what your next step is; the general rule of thumb is over 10 but <= 30%, you will use the alter index reorganize. Anything over 30%, it is best to use alter index rebuild. For complete detail on the commands, see here: http://msdn2.microsoft.com/en-us/library/ms188388.aspx

 

Again, the script below is more for illustrative purposes and logs to a basic table so I can see how long certain rebuilds take – do not use this directly for production purposes, but more as a template for understanding one piece of maintaining your database.

 

declare

        @dbid int,                    @TableName varchar(100),

        @IndexName varchar(200),        @Metric varchar(50),

        @MaintenanceOperation varchar(50),    @StartTime datetime,

        @EndTime datetime,            @StrSQL nvarchar(4000),

        @ChildObjectType varchar(50), @FragPercent float

 

set @dbid = (select database_id from sys.databases where name = ‘MyDatabaseToMaintain’)

set @MaintenanceOperation = ‘Index Defrag’

 

 

if exists(select object_id from tempdb.sys.objects where name like ‘#IndexTemp%’)

begin

    drop table #IndexTemp

end

 

select    DatabaseId = @dbid, TableName = s.name + ‘.[‘ + o.name + ‘]’, ChildObjectName = i.name

        , ChildObjectType = ‘Index’, avg_fragmentation_in_percent, IsDefragged =0

into        #IndexTemp

from        sys.dm_db_index_physical_stats(@dbid,null,null,null,null) ip

inner join    sys.objects o

on        ip.object_id = o.object_id

inner join    sys.schemas s

on        o.schema_id = s.schema_id

inner join    sys.indexes i

on        i.object_id = ip.object_id

and        i.index_id = ip.index_id

where        avg_fragmentation_in_percent > 10

and        o.is_ms_shipped = 0

and        i.name is not null

 

select * from #IndexTemp

 

while exists(select 9 from #IndexTemp where IsDefragged = 0)

begin

 

    select top 1    @TableName = TableName, @IndexName = ChildObjectName

                    , @ChildObjectType = ChildObjectType, @FragPercent = avg_fragmentation_in_percent

    from            #IndexTemp

    where            IsDefragged = 0

 

    set @StartTime    = getdate()

 

    if(@FragPercent <= 30)

    begin

        set @StrSQL = ‘alter index ‘ + @IndexName + ‘ on ‘ + @TableName + ‘ REORGANIZE ‘

        print @StrSQL

        exec sp_executesql @StrSQL

    end

    if(@FragPercent > 30)

    begin

        set @StrSQL = ‘alter index ‘ + @IndexName + ‘ on ‘ + @TableName + ‘ REBUILD with(SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)’

        print @StrSQL

        exec sp_executesql @StrSQL

    end

 

    set @EndTime    = getdate()

    

    insert into Maintenance.Tables

        (TableName, ChildObjectName, ChildObjectType, StartTime, EndTime, MaintenanceOperation)

    values

        (@TableName,@IndexName,@ChildObjectType,@StartTime,@EndTime,@MaintenanceOperation)

    

    update    #IndexTemp

    set        IsDefragged = 1

    where    TableName = @TableName

    and        ChildObjectName = @IndexName

 

end

 

 

References:

sys.dm_db_index_physical_stats: http://msdn2.microsoft.com/en-us/library/ms188917.aspx

dbcc indexdefrag: http://msdn2.microsoft.com/en-us/library/aa258286(SQL.80).aspx

Clustered Index: http://msdn2.microsoft.com/en-us/library/ms177443.aspx

Non-Clustered Index: http://msdn2.microsoft.com/en-us/library/ms177484.aspx

 

Feel free to post or e-mail with any questions, thanks!