SQLSimon.com

Databases that have been in use for a long time and / or are very large can end up having performance issues. There are a lot of possible reasons for this and a lot of different things that can be checked in order to improve performance. Here is a list of some of the things to try:-

• Check the fragmentation of index stats and rebuild if necessary.
• Check that all tables have a clustered index.
• Check that all foreign keys have a non-clustered index.
• Check for specifically badly performing queries. The structure of them can affect performance e.g. the order of the joins, nested sub-queries etcetera.
• Check that table design is sound. In particular, ensure that the data types of primary and foreign keys match correctly.
• Check for any unnecessary indices. DML statements will be affected by this.
• Check that the transaction log is on a separate disk to the datafiles. This helps to avoid disk contention issues for SQL Server.
• Check that stored procedures are not being recompiled.
• Check that AUTO SHRINK is not enabled. This causes the datafiles to be more fragmented.
• Check that AUTO CLOSE is not enabled. This causes the database to be shut down after all logins are disconnected.
• Consider partitioning large tables e.g. orders could be separated into years or months. Each partition can be on a separate disk to avoid contention issues or moved to an archive table at regular intervals.