Disclaimer: Database maintenance tasks are outside of the scope of support. The following information is provided as-is and not warrantied.


What does this mean?

Imagine that your job is to maintain a paper phone book of all the residents of a certain city. On your first day of work, the phone book is in order and up-to-date. As people move into or out of your city, you need to update the phone book accordingly. When someone moves away, the process is simple - just erase their name. When someone moves in though, things get tricky. You can go to the page that the person's phone number would wind up in. If someone has moved out and there is available space on that page, you can add the new person there. But if the page is already full, you have a problem. So you just create a new page and write the new resident's name on it. However, your phone book is already bound - you have to stick the new page at the end. As more and more people move to your city, you keep adding names to extra pages at the end. Now when you have to look up someone's phone number, you have to check both where their name should be and in the extra pages at the end. 


This is similar to what happens with your database indexes. Indexes allow your database to quickly and efficiently sort and find your data - similar to a card catalog at the library. However, as data is added and removed, indexes start to get jumbled and fragmented. SQL Server does not have time to completely re-align indexes each time data is added and often ends up storing it inefficiently. That is why it is important to force your database to periodically rebuild the indexes. 


Why is this important?

Poor indexes can cause slow performance, replication errors, and even data corruption. Additionally, since database issues fall outside of your support agreement, any time spent on problems caused by poorly-maintained database indexes could be billable.


How are problems identified? (Warning: Technical information)

The DB Health Check uses the Scan Density percentage of the 10 biggest tables in your database to look for problems. This percentage is pulled from the SQL Server function DBCC SHOWCONTIG. Any scan density less than 90% is considered a problem. In certain conditions (i.e. very small databases or tables with high transaction volume), this may not be an accurate indicator of how often indexes are rebuilt. In general though, a lower Scan Density means that indexes are not being properly maintained.


How do I fix this?

Typically, we recommend that indexes be rebuilt run at least once a week. A database that has its indexes rebuilt weekly should not have any results for the INDEX check during the DB Health Check.

The process of rebuilding your database indexes is fairly resource intensive, so we recommend that it be run during off hours. Depending on the size of your database, the hardware available to your server, and the amount of time since indexes were last built, the process may take anywhere from a few seconds to a few hours. For an average CounterPoint DB (5-20 GB), plan on a weekly rebuild taking around 30 minutes.

There are numerous ways that a rebuild task can be launched. The method that is best for each organization may vary. Note that since this responsibility is outside of the scope of your support agreement, we will not be able to assist you in setting up, configuring, or maintaining this task. 


The simplest method to rebuild indexes is running the following two lines of code:


exec sp_msforeachtable "DBCC DBREINDEX('?',' ',90)"

exec sp_msforeachtable "UPDATE STATISTICS ?"


That can be set up as a SQL Agent Job, a batch file (using SQLCMD or OSQL), or some other method. 


Alternatively, refer to the following KB article for a simple method to create a minimal backup and maintenance service:


Consult an IT professional to determine which task would be best for you and for assistance with this process.