How to get a decent SQL Server performance monitor for FREE

Microsoft provided a free decent performance monitor system, for SQL Server 2005 SP2 at:

Quote from Microsoft's own website:

Common performance problems that the dashboard reports may help to resolve include:

  • CPU bottlenecks (and what queries are consuming the most CPU)
  • IO bottlenecks (and what queries are performing the most IO).
  • Index recommendations generated by the query optimizer (missing indexes)
  • Blocking
  • Latch contention

It does even more and my favorites are the SQL Server expensive queries:

  1. By CPU
  2. By Duration
  3. By Logical Reads
  4. By Logical Writes
  5. By Physical Reads

Microsoft download: SQL Server 2005 Performance Dashboard Reports

If Microsoft moves it around: search for: SQL Server 2005 Performance Dashboard Reports

Requirements

  • You need to have SQL Server 2005 SP2
  • Microsoft SQL Server Management Studio v: 9.00.3042.00

Install

  1. Run the downloaded file.
  2. Run the setup.sql file on each instance of SQL Server 2005. If you have not changed the defaults, it will be in: "C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard"
  3. Open the SQL Server Management Studio.
  4. Right click on any object > Reports > Custom Reports > go

2. Open the performance_dashboard_main.rdl file with the Custom Reports functionality new to Management Studio in Service Pack 2.

Custom reports
Custom Reports

Run

Expensive_Queries01
Expensive Queries

You can then select the query and see the plan.

Have fun with it.

  • Don't do any change too quickly.
  • Get enough of time to monitor before deciding on what to improve.
  • Many queries can be improved with better indexing and by removing ALL cursor operations.