Performance Issues After Migration To SQL 2005

During reading support forums i found many issues related to performance after migrating to SQL 2005.

Some advice to increase performance:

- Rebuild indexes (DBCC DBREINDEX)

- Update statistics (SP_UPDATESTATS)

- Check the compatibility level of the database in question.

If the compatibility level is set to 80 (SQL 2000)  change it to 90 (SQL 2005).

The compatibility level is not really related to performance but needed for use new features released with SQL 2005.

Split your I/O

There are two different types of physical disk I/O: sequential and random

For our work it is very helpful to know which type of I/O occurred on our system.

The sql server transaction log is a example for sequential I/O.
This files are written sequentially and there is only a small move of the disk drive head.

On the other side there are our database files.
Due to the need of search data there are many seeks and rotations on the disk needed.
This type of I/O is much slower.

To speed up your performance it is a good solution to split your I/O.

Depending of your budget use as many disks as possible.

Consider to use a own physical disk array for:

• SQL Server Transaction Logs (sequential)
• Backup Files (sequential)
• Program Files, OS Files (random)
• Database Files (random)
• TempDB (random)