SQL Server 2005 Performance Dashboard Reports

Last week our dba resolved serious SQL Server performance issues very effectively using SQL Server Performance Dashboard Reports.
Our server's four dual core processors were averaging 99.73% for a couple of days. After monitoring the situation with SQL Server 2005 Performance Dashboard Reports and implementing the suggested performance enhancements, our server's CPUs are now working at between 12% and 60%.

More about Microsoft SQL Server 2005 Performance Dashboard Reports:

"The Microsoft SQL Server 2005 Performance Dashboard Reports are used to monitor and resolve performance problems on your SQL Server 2005 database server."

"The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server."

Download it here.

Update all index statistics after upgrading a db from SQL Server 2000 to 2005

After upgrading a database from SQL Server 2000 to SQL Server 2005, our application's performance degraded significantly. It was eventually resolved by creating two new indexes in the database.

However, I could not understand how database performance could be impacted negatively by an upgrade. All the indexes which were in the SQL 2000 db exist in the upgraded database.

After googling the topic for a while I came across the following article: Interview with Greg Linwood. Below is an important tip from the article.

Most upgrades I have been involved with have been fairly smooth but some degree of tuning has been required in most cases. One tip worth remembering is to update all index statistics after an upgrade (or simply rebuild all indexes) so that SQL Server’s cost optimizer has the most recent information possible by which to make the best query execution decisions. SQL Server 2005’s cost optimizer is significantly more sophisticated and will generally make the same or better decisions in most cases than it’s predecessor. These improved decisions rarely get noticed (as no-one complains when things improve!) but those which degrade performance sometimes cause the upgrade process to come under un-warranted criticism. Hence, I recommend closely monitoring performance during and after upgrades, just in case the query optimizer makes decisions which “appear” better to it but end up not working out.

My Development Motto

SET FMTONLY

When you want to retrieve the metadata for a table, view, stored procedure, etc without having any records returned, use SET FMTONLY ON. Only the column data will be returned.