Keeping Your SQL Database Tuned
There are several processes that should be run regularly to keep your SQL database running at peak efficiency. These are best setup and scheduled to run automatically. How this is done, has been addressed in recent blogs.
Probably the processes that have the biggest influence on performance, are rebuilding indexes, and updating statistics.
When indexes are rebuilt, they are able to be used in the most efficient manner. If you have an active database, where a large number of additions and deletions occur, the indexes can lose efficiency quickly. When records are added, or deleted, the indexes on the table need to be updated. MS-SQL is not very good at keeping it’s indexes efficient during these updates. So, if you have a large number of updates, the time it takes to access records can increase significantly.
If you rebuild the indexes, then they can be used to quickly locate specific records in the database, and the time your queries take to execute can drop drastically.
I prefer to rebuild indexes every night, if there is much activity at all on the database. The task can be setup in the SQL Management Studio, under maintenance tasks, or scripted and setup as a recurring job.
After rebuilding indexes, you should also update statistics. These are statistics that MS-SQL tracks regarding how the data is actually being accessed. In other words, what the actual SQL queries are that access the data. MS-SQL uses this information to attempt to optimize data access, based on how it is actually being used. Again, I recommend that this be done every night, if there is much activity on the database.
I have seen complex processes take several minutes to complete. Then, after rebuilding indexes, and updating statistics, those same processes have finish 30 seconds. As you can see, these two tasks, when regularly performed, can greatly improve you SQL performance.
Dave.