RockSolid Automation Platform

Operational Management

RockSolid contains best practice based management functions for all common operational DBA tasks. This includes automated management of backups, index maintenance, database validation, statistics and capacity management.

Get Started

RockSolid contains a number of automated management processes. These processes are enabled and configured as part of your policy definition for your site. The management jobs deployed by RockSolid to your SQL Server Instances are a combination of your requirements, from your policy, and best practice based management routines.

RockSolid contains best practice based automated management functions for the following DBA tasks:

Recoverability

  • SQL Server FULL database backups*
  • SQL Server Differential database backups*
  • SQL Server Transaction Log backups*

*RockSolid includes support for SQL Server native backups, Red Gate Software’s SQLBackup & Quest Software’s Litespeed backups.

Index Maintenance

  • SQL Server Database Index Rebuilds
  • SQL Server Database Online Index Rebuilds
  • SQL Server Database Online Index Defragmentation

Performance Management

  • Database Statistics Maintenance

Capacity Management

  • SQL Server Database Data File Space Management

Automated Index Management

At RockSolid when a new feature is added, time is taken to re-evaluate what truly is best practice, rather than just accepting current industry standards. One such area is that of database index maintenance, and this article explains our approach to index management.

Index maintenance is the most common method of maintaining database indexes for a SQL Server database. It is an important process for improving performance because it:

  • reduces fragmentation, which improves scan performance; and
  • adds free space (fill) into the index which avoids page splits, improving insert/update performance.

Usually time is scheduled outside of business hours for index maintenance. Effectively there is a system outage while the maintenance process runs through all indexes on an instance, sequentially carrying out a “rebuild” (effectively a recreation of the index).

This is common, but is it best practice? There are a number of issues that can occur, when database maintenance windows are decreased, and the requirement to have the database online and operational increases.

The first issue is how long should the database maintenance window be? How long can application users, the web site manager, the developer etc be told that the database will be “busy” performing maintenance? What time can they start their batch process, their invoice run, their data entry? A DBA can calculate the time required to carry out maintenance for a few databases, and recalculate these maintenance times routinely as the databases grow. But as many enterprise sites exceed 1000 databases they cannot easily calculate exact window requirements for all databases organization-wide. So a catch all timeframe, eg half a day or all day Sunday, is allocated that is sufficient to cover all requirements. Regardless of whether a database is being maintained for 10 minutes, 30 minutes or 2 hours, it is effectively considered under maintenance and unavailable for the duration of the generic window.

The second issue is how effective and efficient is the index maintenance process? A half day on Sunday to rebuild all database indexes is useful, but do all the indexes in a database need to be rebuilt weekly? Or, are there indexes that should be rebuilt more frequently? Again something that can be easily determined for a few databases, but not so easy to accurately determine for hundreds or thousands of databases.

The third issue is what options should be applied when rebuilding a given index? What is the optimal fill factor? Should sorting take place in memory or tempdb? Should online or offline rebuilds take place? When there are hundreds or thousands of databases, there are usually tens of thousands, or hundreds of thousands of indexes. Again, not a practical question for a DBA to answer using traditional methods. So, what does RockSolid do to address these issues and evolve a new best practice?

Given the large number of databases under their control, it can be very difficult for a DBA to accurately know what maintenance windows are available. RockSolid provides a workload analysis tool, which will analyze historical database use and find times when little or no activity is occurring on the database. Using this tool the DBA receives recommendations for when maintenance can be scheduled. Typically they would then confirm these windows with the application users before implementation.

Instead of allocating large blocks of time for rebuilding indexes, and adjusting business and application requirements around that maintenance, RockSolid can work with the time available. Maybe there is one hour on a Tuesday, and 30 minutes between batch jobs on a Friday, and 15 minutes between processes on a Sunday. Also tell RockSolid if that time is considered an outage, so no application processes are expected to run, or if the database should remain online during the window allocation (online maintenance).

When RockSolid is given a maintenance window to use, at run time it carries out an analysis of a number of factors to determine which indexes will provide the best return in terms of performance, given the time allocated to the maintenance process. RockSolid evaluates factors such as fragmentation, index usage, index size and of course the time allocated to determine the best results. The time allocated is important because RockSolid will never exceed the boundaries of a maintenance window, for any type of maintenance process that it manages.

RockSolid also looks at all factors necessary to decide the most appropriate use of Fill Factors (index usage, read/write ratios, time to next maintenance window etc) and rebuild options (sort in tempdb, online/offline based on the window type). This ensures every index is rebuilt using an optimal strategy, not only for the rebuild itself but for the performance of that index for the intended purpose following the rebuild.

Finally, RockSolid provides feedback on the windows allocated for database maintenance. Are the windows too large or too many? Are there too few maintenance windows? For example, RockSolid may recommend that by adding a daily 30 minute maintenance window, instead of a weekly window, there will be an estimated n% improvement in performance. RockSolid pro-actively provides this analysis and feedback, and is constantly re-evaluating the maintenance requirements for the lifetime of a database.

This approach is redefining how database maintenance is performed, resulting in improvements in availability of the database, but also resulting in improved performance through more effective index management.

Backup Optimization

RockSolid automatically manages when individual backups run during backup windows. Backup start times for each database are automatically micro adjusted on an ongoing basis. RockSolid does this to ensure window boundaries are honored, yet at the same time minimizing concurrent I/O impact and optimizing database backup performance.

seperator

 

Get Started

To learn more about the RockSolid Platform, and to organise an online demo, please provide us some details about you and your environment.

First Name: Last Name:
   
Company:
 
Email:
   
Phone:
 
How many SQL Servers are in your Environment?: