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.

Orphaned User After Migration To SQL 2005

After migrate SQL 2000 to SQL 2005 the SQL-Logins lost the relationship (Database <-> Server).

To fix the problem:

Find the SQL-Logins which orphaned: Exec sp_change_users_login ‘Report’

Run following statement to resolve the problem: Exec sp_change_users_login ‘UpdateOne’, ‘Name’, ‘Name’

Note: This will only work for SQL-Logins.

There is a parameter “Auto_Fix“ available. I don’t use it due to security reasons.