Performance Audit Toolkit

http://www.quicksqlserver.com/code/SQLSERVER_2000_AuditPlan.xls

Top 10 SQL Server 2005 DW Performance Issues

Good article on Technet , regarding real world issues for Datawarehousing performance.http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx

DBCC MEMORYSTATUS

If your upgrading from 2000 , you may want to know what the equivalent of DBCC MEMUSAGE in 2005. Check DBCC MEMORYSTATUS , there are lots of details in SQL2005 BOL.

How do I monitor performace?

I log the following information by default into a system admin database for future follow up and trending.
1. Waitstats captured once per hour
2. Number of spids broken down by status captured on 5 minute snapshots
3. Database sizes captured once per day
4. IOstalls captured once per day
5. All SQL Server perf counters captured continuously
6. SQLTrace, 5 minute snapshot captured once per hour (if the sysem can withstand a continuous trace then I run 24x7)

There is a multitude of additional stats that I grab for SQL Server 2005. The above is a list of the basics allowing me to analyze performance at any time while also accounting for all of the historical information if I need to.