Chapter 10. The self-healing database
This chapter covers
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.