// Week 24: Database Maintenance
Wow, time goes by! Welcome back to the last and final installment of the SQL Server Performance Tuning series. Today I will talk in more details about database maintenance in SQL Server, especially about index maintenance operations, and how to perform database maintenance.
Index Maintenance
Database maintenance is a very crucial part for your job as a DBA to make sure that you get the best possible performance out of your database. A SQL Server database is like a car: it needs regular inspections to make sure that you run it without any problems, side effects, and the best possible performance. The most important part of a SQL Server database is its indexes and their associated statistics objects. Indexes get fragmented over time, and statistics must be up-to-date, so that the query optimizer can produce a “good enough” execution plan for you.
Let’s have a more detailed look at both things. Indexes (Clustered, Non-Clustered) can get fragmented over time. Fragmentation means that the logical and physical sorting order is not the same any more. If you have stored your database on traditional rotational storage, index fragmentation leads to random I/O in your storage subsystem, which is very expensive compared to fast sequential I/O.
Microsoft recommends the following best practices regarding index maintenance:
- Fragmentation below 10%: Perform nothing
- Fragmentation between 10 - 30%: Perform Index Reorganize operations
- Fragmentation more than 30%: Perform Index Rebuild operations
Besides that, Index Reorganize and Index Rebuild operations should be only applied if your index has at least 10000 pages in the leaf level. If you don’t hit that threshold, you will not notice a negative side-effect of the introduced random I/O. An Index Rebuild operation recreates your complete index from scratch. It’s also one big transaction which gets fully logged in the transaction log. Rolling back your rebuild operation at 99% completion is a very bad idea, because SQL Server has to perform everything again that was already done (in the opposite order). Therefore your rollback also takes a very long time. Because an Index Rebuild recreates your complete index, your statistics will be also updated with a full scan.
In addition, because it’s one big transaction, you will also have problems if you deal with high availability technologies that are based on the transaction log - like Database Mirroring or AlwaysOn Availability Groups. SQL Server has to send your complete transaction to the mirror (or replica). That means you flood your network with a massive amount of transaction log records. This can have a very negative side-effect on your high availability strategy.
As an alternative, an Index Reorganize operation just deals with the leaf level of your index, and rearranges leaf level pages in logical sort order. Because of that, an Index Reorganize operation will not update the statistics for you. An Index Reorganize operation consists of multiple smaller system transactions. Therefore it has less impact on your transaction log, because virtual log files can be marked for reuse much faster. An Index Reorganize operation can also help you with Database Mirroring or AlwaysOn Availability Group, because you are not polluting your network with one huge transaction as this is the case with Index Rebuild operations.
How to perform maintenance operations
One question that I’m always getting asked is how to perform maintenance operations in SQL Server. I never, ever, recommend the Database Maintenance Plans that are provided by SQL Server. With these maintenance plans you work with a sledge hammer against your SQL Server: the maintenance plan will reorganize/rebuild your indexes regardless of their actual fragmentation!
I have already seen maintenance plans, which were running throughout the whole night to perform Index Rebuild operations on indexes with no index fragmentation. You have no way to use the fragmentation levels as input to the maintenance plans that I have mentioned above. It’s an Reorganize or Rebuild operation regardless of the actual fragmentation. Therefore I never ever recommend these plans to anyone!
But what I recommend is the SQL Server Maintenance Solution provided by Ola Hallengren. This solution consists of a set of stored procedures, with which you can performance database consistency checks, backups, and also index maintenance operations. The index fragmentation levels are just parameter values that you provide to the stored procedure. Let’s have a look at the following call to the stored procedure IndexOptimize:
EXEC [master].[dbo].[IndexOptimize]
@Databases = 'AdventureWorks2012',
@FragmentationLow = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 30,
@PageCountLevel = 10000,
@SortInTempdb = 'N',
@MaxDOP = NULL,
@FillFactor = NULL,
@PadIndex = NULL,
@LOBCompaction = 'Y',
@UpdateStatistics = NULL,
@OnlyModifiedStatistics = 'N',
@StatisticsSample = NULL,
@StatisticsResample = 'N',
@PartitionLevel = 'N',
@TimeLimit = NULL,
@Indexes = NULL
@Delay = NULL,
@LogToTable = 'Y',
@Execute = 'Y'
GO
As you can see from the code, you can specify the various fragmentation levels just as parameters (FragmentationLevel1, FragmentationLevel2). And finally you specify what index operation you want to perform within these fragmentation levels (FragmentationLow, FragmentationMedium, FragmentationHigh). It’s a very simple, but really powerful approach to perform index maintenance operations in SQL Server. Give it a try - you will not regret it!
Summary
In today’s last installment of the SQL Server Performance Tuning series we have talked about database maintenance in SQL Server. As you have learned it is very important to perform index maintenance operations regularly to get rid of index fragmentation. Index fragmentation just slows down disk read operations because of the introduced random I/O at the storage subsystem level. In addition I have shown you how you can perform very efficient index maintenance operations by using the SQL Server Maintenance Solution provided by Ola Hallengren.
I hope that you have enjoyed the 24 weeks together with me, and that you have learned some new things about SQL Server and especially about performance tuning & troubleshooting. If you want you can also send me a short email with some feedback/testimonial that I will put on the website of the training plan. Thanks again for signing up to the SQL Server Performance Tuning series, and thanks for listening to me over the last 24 weeks!