Many application performance problems can be traced to poorly performing database queries; however, there are many ways you can improve database performance. SQL Server 2005 gathers a lot of information that you can use to identify the causes of such performance issues.
In the January issue of MSDN Magazine, Ian Stirk highlights areas where performance can be improved by using information that is already being gathered by SQL Server 2005. Ian demonstrates how to obtain this information, discusses the underlying DMVs, identifies any caveats to be aware of when interpreting the data, and points you to additional areas where you might be able to realize performance improvements.
Ian includes a series of SQL scripts that detail the various aspects of the data collected by SQL Server 2005. The complete and fully commented scripts can be explored online or downloaded from the MSDN Magazine Web site.