Chapter 10. The self-healing database

 

This chapter covers

  • The self-healing database and automation
  • Automatically recompiling slow routines
  • Automatically performing index and statistics maintenance
  • Automatically disabling or dropping unused indexes
  • Automatically creating the most-important missing indexes

The concept of a self-healing database relates to the ability of SQL Server to self-correct problems and potential problems before they become noticeable to users. You can implement this by creating a series of SQL Server agent jobs that run periodically. This chapter covers a miscellany of functionality that collectively can automatically improve the performance of your database queries.

The scripts contained in this chapter will improve the performance of your slow-running SQL queries. They accomplish this by recompiling routines that have become slow, improving the retrieval of data from your indexes, intelligently updating index statistics upon which your optimal query plans will be based, and creating or removing indexes as necessary.

All the scripts build up dynamic SQL, which is subsequently executed via the system stored procedure sp_executesql. The dynamic nature of the creation of the SQL to execute allows you to apply further filtering conditions, for example, limiting the SQL to a given database. Each script contains an EXECUTE command to run the SQL and a PRINT command to show what’s being executed.

10.1. Self-healing database

10.2. Recompiling slow routines

10.3. Automatically rebuild and reorganize indexes

10.4. Intelligently update statistics

10.5. Automatically updating a routine’s statistics

10.6. Automatically implement missing indexes

10.7. Automatically disable or drop unused indexes

10.8. Summary