Tuning a database for performance is a never ending task. For SQL Server, you can use automated maintenance implementations, which are usually the default. If the database is configured, it will automatically create statistics when the query optimizer determines a statistic would be useful to determine the best query plan. It will also automatically update statistics whenever data is modified from Insert, Update or Delete commands. These are features a DBA will often disable so that performance is not reduced during database usage.
So, if the automated methods are disabled, then you must maintain those features yourself, or risk degraded performance of your database. How often those statistics need to be maintained depends on how often your data is modified. So, for a highly active OLTP database, you would probably maintain statistics nightly or weekly. For an OLAP database, you would probably maintain them whenever a significant load has been executed.
Integral to those statistics maintenance is the defragmentation of your indexes. This is not something that can be automated by configuration. However, index fragmentation is one of the performance drags for many databases. Some indexes are always fragmented because the table is so small. Any significant sized table will benefit by keeping the fragmentation down.
So, one scheduled task you will find helpful on any database with more than a few records is to defragment your indexes and update your statistics.
What if the statistics that are created aren’t providing the needed value? What if the indexes that have been created don’t server the common queries well? Those are things you will need to monitor on a consistent basis. You can wait until your database performance diminishes, and then look for the worst queries. A better approach is to run the index profiler tool, or review the query plans for common queries to see if the indexes available provide good coverage.
If you’re new to this, maintenance of statistics and indexes would be a great skill to put on the top of your list for this next year. It will take you far when working with SQL Server.
Cheers,
Ben