By: Jeremy Kadlec | Comments | Related: > Maintenance
Problem
Performing database maintenance on a 24X7 SQL Server is a unique problem in and of itself. The issues are numerous. First is the realization that database maintenance is important, but not important enough to jeopardize losing data or stopping business operations. Second is that database maintenance is needed to ensure the databases are free of errors and are performing well, so it is up to you to be creative to figure out how perform database maintenance. Third, once you have figured out when you can perform database maintenance, you need to ensure you do not break the high availability\fault tolerant\disaster recovery plan or any down stream processes while performing maintenance.
Solution
In an earlier tip (Performing Maintenance with Databases in Full Recovery mode), we outlined the steps to perform database maintenance with databases in full recovery mode when log shipping is in use and when a maintenance window is available. If you have the luxury of a well defined maintenance window, but the constraint of supporting log shipping, then pursue the path outlined in the previous tip. If you do not have the luxury of a formal maintenance window, then the goal is to minimize the amount of database maintenance that is performed on the production SQL Server.
Do I have a window to perform maintenance?
One of the first questions that needs to be answered, is when and how much time do I have on a daily, weekly or monthly basis to perform database maintenance. Start by monitoring the system usage to determine if you do have low usage periods on a daily, weekly or monthly basis to perform maintenance. Capture reasonable metrics based on the application to determine the usage. On some systems the metric might be orders that are placed, on other systems the metric may be the number of authenticated users or the metric may be based on the shifts the organization follows. Capture these metrics for a reasonable period of time (i.e. week or month), analyze the results to determine reasonable periods of time and then share with your team the time you plan on performing database maintenance.
If this approach does not yield promising results, because you do not have any low usage periods, then another approach is to take a look at the calendar to see if the company will be shut down on a holiday. This may be an opportunity to perform database maintenance. Some organizations have busy times of the year, but have holidays when the business is not operating so this could be a reasonable time when the systems will be available for maintenance.
If both of these options do not yield favorable results begin to break down the application as much as possible to find out if particular portions of the application and subsequent database(s) are not in use during particular portions of the day. Although the overall application is in use, specific tables requiring maintenance may not. For example, reporting and billing may not be used during the evening hours and batch related tables may not be in use during the day. This might give you the opportunity to get creative when you perform specific types of maintenance outlined below, so just hold this thought.
Some systems just run 24X7 and if you support one of these systems then the steps listed above may or may not work. If the other options are not feasible, then look towards building an architecture that can support multiple SQL Servers to handle the core business needs. From a maintenance perspective, then 1 system can be taken down at a time to perform maintenance while the other cloned systems are handling the core business needs.
What types of database maintenance should I execute?
- Validate the database objects and system catalogs are free of corruption
- Determine the fragmentation for tables and indexes
- Rebuild the fragmented indexes
- Update SQL Server's statistics
- Update SQL Server's usage
For more information on SQL Server database maintenance in both SQL Server 2000 and 2005, reference these two tips:
- SQL Server - Performing maintenance tasks
- SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)
Can I off load any of the maintenance to another SQL Server?
Yes - When the production SQL Server is running on a 24X7 basis or the maintenance window is not long enough to support the maintenance above, the following tasks can be off loaded to a backup server which should minimize the time needed on the production system.
- Validate the database objects and system catalogs are free of corruption
- Determine the fragmentation for tables and indexes
By moving these two sets of process to another server this will save significant amounts of time on the production system. The backup server does not need to be of the same caliber as the production system, but the timing might be longer. Although this is the case, the time on the backup server still out weighs the time on the production server where the time is most critical.
What maintenance do I need to execute on the production system?
The key process that needs to be completed on the production system that cannot be off loaded to another SQL Server is rebuilding indexes. The key tenet to watch for here is the timing. Rather that rebuilding all of the indexes for the database, select specific tables and be prepared to rebuild the clustered index and all non-clustered indexes during a single session. As follow-up processes to this task, be sure to update the statistics and usage information, which should be a fraction of the time as the index rebuilds.
- Rebuild the fragmented indexes
- Update SQL Server's statistics
- Update SQL Server's usage
How can I complete all of the maintenance during many short windows?
If the reality is that you only have a limited amount of time on a weekly basis i.e. 2 hours, then what is necessary is time sharing on the system to support the business needs and chip away at the maintenance needs. This approach to maintenance is a marathon, not a sprint, so pace yourself. Determine the most critical maintenance that needs to be performed on the production system, off load any maintenance to a backup system and then plan accordingly. Depending on the size of the database and the fragmentation, parallel index rebuilds during the short maintenance windows can be scheduled if the processing power is available and the user community is not impacted.
How do I know if I will exceed maintenance window?
As is the case with any database related process, testing is critical. So build your scripts with the short windows for database maintenance in mind. On your test servers be sure to complete the maintenance in the allocated time if the servers have similar resources. If the test and production servers are not similarly equipped, then calculate a reasonable ratio of time needed between the test and production systems as a rule of thumb when planning the maintenance.
Next Steps
- Review your systems to determine the last time maintenance was performed
- If you are unsure of when the last set of maintenance was performed, determine
if a backup server is available to perform the maintenance that can be off loaded
to another SQL Server
- Depending on the size of your databases, consider an automated process to complete this portion of the maintenance since it can be completed apart from the production system
- Analyze your system usage to determine available maintenance windows and work with your IT and user community
- If your systems are in need of maintenance, based on your first set of analysis, build a priority list of the maintenance items
- Consider the options above to break down the process into manageable pieces that can be completed during the maintenance windows
- If you are faced with servers with little to no downtime determine what opportunities are available to perform the maintenance and communicate the benefits of performing maintenance to your IT and user community
- Be sure to schedule and communicate the maintenance so your organization is well aware of the steps you are taking to benefit the SQL Server environment
- Check out these related MSSQLTips.com:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
