To speed up access to the data in SQL Server we use something called indexes, most of you probably know all about it. Indexes are B-tree structures that are extremely efficient for looking up data, consider the fact that looking up row based on a indexed column take the same time when there are 100 rows and when there are 4 millions rows. Because the tree is very compact SQL Server can cache the top levels of the B-tree and sometimes even leaf levels further minimizing disk reads (which really is where the bottleneck is so always invest in lots of RAM on your SQL Server).

So why not have indexes for every column ? Indexes take up disk space and needs to maintained for insert/update/delete operations so the key is to only have the indexes you really use (and need).

Indexes works best for columns with a ever increasing number(1,2,3,4,5..n) because then the index just grows and grows. But when you have columns where the types are more random in its nature(string, GUID) or when you have lots of deletes and updates the index get less efficient over time. The index gets fragmented and need to be rebuilt on regular intervals to keep its performance, especially if you are importing large amounts of data or have a very high number of changes in the database.

The easy way is to add maintenance jobs in SQL Server Management Studio to rebuild indexes for example once a week at off hours.

The programmers way is to create your own SQL script that automatically finds fragmented indexes and rebuilds them for you.

So, I've written a script that reorganizes indexes with fragmentation between 10 and 30% and rebuilds indexes with more than 30% fragmentation (which is Microsoft's recommendation). It should support all types of databases but has been specifically tested on EPiServer CMS 5 R2.

The key in this script is sys.dm_db_index_physical_stats which reads statistics on indexes, its a bit slow on larger tables because it samples data from the index.

An example of reading the statistics can look like this:

declare @dbid int
set @dbid=db_id()

Select convert(decimal(5,2), avg_fragmentation_in_percent) as  avg_fragmentation_percent, object_name(d.object_id) as [table],
i.name as [name], d.index_type_desc, alloc_unit_type_desc as [type], index_depth, index_level, fragment_count, page_count   
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, 'SAMPLED') d  -- or 'DETAILED'
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
order by avg_fragmentation_percent desc

EPiServer CMS 5 R2 introduces a bunch of new indexes to back the most commonly used functionality in EPiServer CMS supporting larger amounts of data in key tables.

So, here you have the script: Index_automatic_rebuild.sql