The Microsoft SQL Server 2005 Performance Dashboard Reports are used to monitor and resolve performance problems on your SQL Server 2005 database server. The SQL Server instance being monitored and the Management Studio client used to run the reports must both be running SP2 or later.
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
The SQL Server 2005 Performance Dashboard Reports only use Dynamic Management Views and Catalog Views. They do not poll performance counters from the OS, nor do they store a history of your server’s performance over time. These are very light-weight reports that will help diagnose performance problems as they are occurring.
Download SQL Server 2005 Performance Dashboard Reports
Getting the Most From Performance Dashboard Reports
These reports show what is happening right now, so the best time to open them is while users are complaining. If you wait until the slowdown is over, the blocking and running queries you wanted to see may already be gone. It also helps to look at the reports once on a quiet day, so you know what normal looks like.
For the 2005 version, setup had two parts: run the setup script on each server you want to watch, then open the main report file from Custom Reports in Management Studio. If a report fails to open, check that both the server and your client are on SP2 or later.
A few tips for reading the dashboard:
- Start with the main page and look at CPU and waits first, then drill into the queries behind them.
- Treat missing index suggestions as ideas. Compare them with the indexes you already have before you create anything.
- Since the reports keep no history, save the query text or a screenshot as soon as you find the problem.
Newer versions of Management Studio include a Performance Dashboard report built in, under the standard server reports, so the separate download is no longer needed. The same reading tips apply to it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
5 Comments. Leave new
I do recommend to use Performance Dashboard reports. I have not used Index Tuning Wizard in SQL Server 2000 as they did not worked when Temp Table were used. In SQL Server 2005 it should work better as CTEs are introduced. I still have to use that for SQL Server 2005.
Latches are mechanisms for prospecting internal shared data structures, such as lists of currently accessed tables, users accessing data etc. Processes acquire latches when manipulating these internal structures. Latch Contention determines how long a process will loop to try and get a busy latch before going to wait and trying later. In SQL Server latch could become a contention point when there were a high number of users using tempdb at the same time. Proportional fill has been optimized to eliminate this latch. With this change in SQL Server 2005, applications will experience less UP latch contention in tempdb.
Hi Pinal. So do you recommend using Performance Dashboard Reports then? How valuable do you find the index recommendations to be? How do they compare to the Index Wizard in SQL 2000? What is meant by latch contention?
Hi,
I am stuying about the Sql server 2005 Reporting services. I want to devlop a report like Dashboard system, if this is possible? or I have to download any tools to do this? Please help me.
Hello, I have a column that contains a YYYYJJJ which is Year_Day of year type of julian date. How can I convert it to YYYYMMDD?
Thank you.
Hi,
Could you please check it below :
DECLARE @JulianDate char(7) = ‘2013189’
SELECT DATEADD(YEAR, @JulianDate / 1000 – 1900, @JulianDate % 1000 – 1)