While working on a large project, I found that the queries were running slower and slower as the days went on. We were working with current and historical data and were only joining about 3-5 tables at a time. Each join was bringing back between only about 50K rows or so, and the results were aggregated and tossed up on an SSRS page. Since things were going well at first and started slowing down over time, I figured the indices were probably the cause.

If you don’t know much about indices, or you need to brush up on your knowledge, check out this article from simple-talk.com. You will learn all about the B-Tree structure .
So I looked at some of the tables just using the properties window and found that there was some index fragmentation going on. The rule of thumb that I have read in multiple places is 30% fragmentation and around 90% page fullness. [citation needed] Here is one of the smaller tables with over 80% fragmentation.

Now, I wanted to clean up all of these fragmented indices. One way to rebuild the indices is via T-SQL. You could use a query like this one to rebuild your index:

ALTER INDEX [TableName] ON [dbo].[TableName] REBUILD WITH (FILLFACTOR = 90)

But I didn’t want to use this T-SQL script and manually replace the table and index name for each and every index. I knew that I would have to use stuff like sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats, or sys.objects to build out a script where I could loop through and get the table and index name and toss them into the T-SQL script. I looked around the web and found GOLD on sqlserverpedia.com!

Index Maintenance via sqlserverpedia.com

This script is really powerful and has several great options. You can print out and see what indices are targets for rebuilding, without actually touching them. You can target a specific table, or all of the tables in a database. In fact, I recommend you read the script carefully to familiarize yourself with all the options. Now, I won’t reproduce the script here, but I will show you the usage I have set up for this script.

Exec dbo.dba_indexDefrag_sp
@executeSQL = 1
, @minFragmentation = 80
, @database = 'DB_Name'


Just schedule this as an SQL Job or inside of a DTS or SSIS package to run every day/week/month, etc. You’ll find that your queries run faster as your indices stay healthy.

Here is a quick look at the index after running the script. The index on this table is defragmented, the index uses less pages and the pages are full. As a bonus, all of the indices on the table are defragmented.