Database 101-3: What is Index Maintenance?

When data is inserted into, deleted from, or updated in your SQL tables, the indexes update to reflect those changes.

As indexes are modified the information in them becomes fragmented, or scattered. This means you’ll want to schedule index maintenance to rebuild, reorganize, defrag, and update statistics to run efficiently.

Thank you, Ola Hallengren!

The good news is that doing index maintenance is a very easy task in SQL Server because a nice guy over in Sweden wrote a whole solution that he published on the web, available completely for free. 

His name is Ola Hallengren and you can find his solution on his site or on GitHub. I’ve been a SQL Server DBA for ~15 years, and this is, by far, the best solution I’ve seen for backups and maintenance.

Ola’s solution has 3 main components:

Backups: This component allows you to generate BACKUP DATABASE commands with nearly every option you could ever need. All of the options are documented here: https://ola.hallengren.com/sql-server-backup.html

• For any mission-critical databases, I would suggest setting them to a FULL recovery model, along with setting up a scheduled job to execute a FULL backup once a week, DIFFERENTIAL backups nightly, and LOG backups every hour, with @Compress = "Y".

• This setup allows you to recover databases to a point in time with, at most, 1 hour of data loss without being onerous in terms of disk space dedicated to backup files.

• Please ensure the backup files are being written somewhere other than the local server. Because if the server crashes, you can't recover.

Integrity checks: This component will check for data page corruption in every object in every database on your server. Checking for corruption is extremely important, as the corrupt data pages can lie idle in your database, undetected until the data page is accessed by a query. The corruption could only be minutes old.  It could be years old. Once it's found, the only option to fix it without data loss is to restore from the backup. But the corruption will exist in your backup files as well if it's never detected, which is why this step is so vital. 

• I would suggest setting up a scheduled job to check USER databases nightly if your business operations allow, or at minimum once a week. All of the options are documented here: https://ola.hallengren.com/sql-server-integrity-check.html

• To improve the run-time of the check, I would suggest setting the @NoIndex parameter to "Y". This option will skip checking for corruption in NONCLUSTERED indexes. While it never hurts to check indexes, this check is not vital, as any corrupt NONCLUSTERED index can simply be rebuilt.

Index optimization: This component allows you to check the fragmentation level of all of your indexes, and take action to keep them in order, along with updating statistics on tables and indexes as well. Not keeping your indexes maintained can be very detrimental to the performance of your database server. Fragmented indexes can make even the perfectly optimized query perform badly, and out-of-date statistics can cause the optimizer to build query execution plans that aren't even close to optimal.

• Like the integrity checks, I would suggest setting up a scheduled job to rebuild fragmented indexes nightly if business operations allow, or at minimum once a week. The same goes for statistics updates. If your business operations won't allow nightly index rebuilds, I would still attempt to update statistics nightly, as it's a much more lightweight operation than checking and fixing index fragmentation.

• You can find all of the options for this component here: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Let’s Explore Some Cautionary Tales

Database Best Practices

• Run daily / weekly Index Defragmentation

• Ensure proper backups/disaster recovery

• Install appropriate database integrity checks

What happens when the above isn’t executed? Here are some real-world issues that have been called into SoftWriters, and how each situation resulted.

Issue: Over the last few months our system has slowed down.

• Discovery: There was no blocking on the SQL server. Ran an index fragmentation script. Over 100 indexes were over 80% fragmented.

• Solution: Ran index maintenance overnight.

• Results: Improved performance speeds achieved.

Best Practice: Run index maintenance at least once per week.

Issue: No one can log in. It shows an error about something missing?!

• Discovery: FrameworkLTC error that a stored procedure was missing. SQL was missing the entire FWDB; other databases were in a state of disarray. The ticketing system showed a recent upgrade from an older version that required a database migration. IT at the pharmacy had reverted from a backup of a virtual machine that was mid-migration. SQL backups were being run daily but were left on the virtual machine.

• Solution: Restored the databases.

• Results: The pharmacy lost three weeks of data.

Best Practice: Establish more efficient disaster recovery procedures like moving backups offsite for disaster recovery.

Issue: Complete pharmacy down!

• Discovery: All users were receiving the same error. Upon checking SQL, “FWDB (Suspect)” = Data corruption

• Solution: Ran index maintenance overnight

• Results: Restored the database

Best Practice: Follow better database maintenance procedures such as running index maintenance at least once per week.