Maintenance Tips

Updating Statistics is critical for the SQL Server optimizer to have the latest information about the database to select the best query plan. Unfortunately, I am hesitant to enable AUTO UPDATE STATISTICS at a database level or for specific tables. I am concerned that this command could run during core business hours or during a big batch operation. Does SQL Server 2005 offer any new features over SQL Server 2000 that will give me the confidence to enable AUTO UPDATE STATISTICS at a database level?

If you're like me, you have a SQL Agent job in place to rebuild or reorganize only the indexes in your databases that truly require such actions. If you rely on the standard maintenance plans in Microsoft SQL Server, a Scorched-Earth policy of rebuilding all indexes occurs. That is whether such actions are required or not for a specific index, a rebuild of the index and all the locking and churning in the logs occurs. That is why so many of us "roll our own" index maintenance solutions as it were. It is also one of my biggest peeves with Microsoft - why was this not built into SQL Server 2008? Ah, at any rate, by only maintaining indexes that are fragmented, statistics updates do not occur globally against the tables/indexes...

In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands. A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.

When I have performed database maintenance in the past the transaction log backups were huge and I was unable to restore the transaction logs. This caused more problems for me so I have stopped performing SQL Server maintenance. My SQL Server performance is now an issue, so I am looking for a process to be able to perform database maintenance for my databases in full recovery mode during a pre-defined maintenance window.

With any database systems there are a couple of key items that help with performance. These items include: good database design, properly written SQL code, correct indexes, current statistics and defragmented indexes and data. The last two items can be handled by maintenance tasks that can be run on a periodic basis to ensure that the SQL statements are running as efficiently as possible.

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.

In some of your tips you reference sp_recompile as a stored procedure that needs to be executed during some maintenance related processes. After doing some basic research it looks like I can execute sp_recompile against a particular stored procedure or against a table so that all objects, typically stored procedures, that reference that object can be recompiled. I know that sp_updatestats exists to update statistics against all objects in the database. Does a similar stored procedure exist for sp_recompile?

In some of your tips you reference sp_recompile as a stored procedure that needs to be executed during some maintenance related processes. After doing some basic research it looks like I can execute sp_recompile against a particular stored procedure or against a table so that all objects, typically stored procedures, that reference that object can be recompiled. I know that sp_updatestats exists to update statistics against all objects in the database. Does a similar stored procedure exist for sp_recompile?

In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands. A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.

If you're like me, you have a SQL Agent job in place to rebuild or reorganize only the indexes in your databases that truly require such actions. If you rely on the standard maintenance plans in Microsoft SQL Server, a Scorched-Earth policy of rebuilding all indexes occurs. That is whether such actions are required or not for a specific index, a rebuild of the index and all the locking and churning in the logs occurs. That is why so many of us "roll our own" index maintenance solutions as it were. It is also one of my biggest peeves with Microsoft - why was this not built into SQL Server 2008? Ah, at any rate, by only maintaining indexes that are fragmented, statistics updates do not occur globally against the tables/indexes...

With any database systems there are a couple of key items that help with performance. These items include: good database design, properly written SQL code, correct indexes, current statistics and defragmented indexes and data. The last two items can be handled by maintenance tasks that can be run on a periodic basis to ensure that the SQL statements are running as efficiently as possible.

Updating Statistics is critical for the SQL Server optimizer to have the latest information about the database to select the best query plan. Unfortunately, I am hesitant to enable AUTO UPDATE STATISTICS at a database level or for specific tables. I am concerned that this command could run during core business hours or during a big batch operation. Does SQL Server 2005 offer any new features over SQL Server 2000 that will give me the confidence to enable AUTO UPDATE STATISTICS at a database level?

When I have performed database maintenance in the past the transaction log backups were huge and I was unable to restore the transaction logs. This caused more problems for me so I have stopped performing SQL Server maintenance. My SQL Server performance is now an issue, so I am looking for a process to be able to perform database maintenance for my databases in full recovery mode during a pre-defined maintenance window.

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.

In some of your tips you reference sp_recompile as a stored procedure that needs to be executed during some maintenance related processes. After doing some basic research it looks like I can execute sp_recompile against a particular stored procedure or against a table so that all objects, typically stored procedures, that reference that object can be recompiled. I know that sp_updatestats exists to update statistics against all objects in the database. Does a similar stored procedure exist for sp_recompile?

If you're like me, you have a SQL Agent job in place to rebuild or reorganize only the indexes in your databases that truly require such actions. If you rely on the standard maintenance plans in Microsoft SQL Server, a Scorched-Earth policy of rebuilding all indexes occurs. That is whether such actions are required or not for a specific index, a rebuild of the index and all the locking and churning in the logs occurs. That is why so many of us "roll our own" index maintenance solutions as it were. It is also one of my biggest peeves with Microsoft - why was this not built into SQL Server 2008? Ah, at any rate, by only maintaining indexes that are fragmented, statistics updates do not occur globally against the tables/indexes...

In two previous tips we discussed how to automate full backups and transaction log backups by creating scripts that iterate through each of your databases and then execute the backup commands. A reader requested information about how to automate the process of deleting older backup files, so this tip explains one approach for getting rid of older backup files that are generated.

Updating Statistics is critical for the SQL Server optimizer to have the latest information about the database to select the best query plan. Unfortunately, I am hesitant to enable AUTO UPDATE STATISTICS at a database level or for specific tables. I am concerned that this command could run during core business hours or during a big batch operation. Does SQL Server 2005 offer any new features over SQL Server 2000 that will give me the confidence to enable AUTO UPDATE STATISTICS at a database level?

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.

When I have performed database maintenance in the past the transaction log backups were huge and I was unable to restore the transaction logs. This caused more problems for me so I have stopped performing SQL Server maintenance. My SQL Server performance is now an issue, so I am looking for a process to be able to perform database maintenance for my databases in full recovery mode during a pre-defined maintenance window.

With any database systems there are a couple of key items that help with performance. These items include: good database design, properly written SQL code, correct indexes, current statistics and defragmented indexes and data. The last two items can be handled by maintenance tasks that can be run on a periodic basis to ensure that the SQL statements are running as efficiently as possible.