Have you ever noticed one of your SQL Server instances running unusually slowly?
Have you had difficulty quickly identifying what areas could be affecting its performance?
One of our customer support Escalation Engineers, Keith Elmore, specializes in understanding these types of problems. He has worked with customers all over the world on diagnosing their toughest and most critical performance bottlenecks. Keith has put together a set of Reporting Services reports that will help DBAs diagnose potential performance problems. These reports were designed to leverage the new Custom Reports functionality in SQL Server Management Studio (Service Pack 2).
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.
I encourage you to check them out. Feel free to comment here with your thoughts and questions.
SQL Server 2005 Performance Dashboard Reports:http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en
Paul A. Mestemaker IIProgram ManagerMicrosoft SQL Server Manageability
Regarding the "The 'version_string' parameter is missing a value" when running the database_overview.rdl" error.
A quick and dirty fix is to delete all references to version_string in database_overview.rdl. Version_string (1.0) is the version of DashboardReports functionality. Not really useful to have it there anyway...
I have posted 3 times indicating the error with datetime difference but no one seems to have an answer. Is this product even supported by Microsoft? Will there be an update or basically we have it as is until SQL 2008 is out?
I would really like to know.
Thanks for your fix for SQL Server 2005 Performance Dashboard Setup.SQL by changing the following line from:
sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
Worked perfectly to resolve the "Difference of two datetime columns caused overflow at runtime" error.
Great! The fix works perfect on "Difference of two datetime columns caused overflow at runtime" error.
i have the same problem that Heinrich posted on May 16,
"when i click on any blue region in the chart for the SQL CPU utilization on the performance dashboard where you normally would get further infos (reports for the last 15 minutes).
There comes no repport but an Error: Arithmetic overflow error conferting expression to data type datetime.
This happens on two different servers with SQL Server 2005 Enterprise Editions, but all other driil through is functioning. What' s the problem, is it a problem in recent_cpu.rdl or performance_dashboard_main.rdl or in the called stored procedures. Please help, it is a really good tool, but not good when functioning only partly."
Thanks for help
Has anyone found a fix for this particualr error:
An error has occurred during report processing.
Cannot read the next data row for the data set SESSION_REQUEST_ACTIVITY.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I applied the fix for the conversion issue, worked great....but still would like to get this other one knocked out.
Did anyone ever find a solution to this issue:
It's a different query altogether from the fix that was listed.
Thank you for an exellent tool!
Does anyone know if its possible to exclude replication queries from the report?
I have a fix for the ability to drill down into the blue zone when you have a server which has been running for 24 or more days:
Go to the recent_cpu.rdl file located in (default):
C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard
go to line number 3271, you should see the "From" statement of a sql query...
paste what follows thru line 3306 of the original file...
(PS... save your ORIGINAL FILE)!!!
Don't haze me if this doesn't work for you!
It works for me!
from (select s.session_id,
s.status as session_status,
s.cpu_time as session_cpu_time,
r.cpu_time as request_cpu_time,
-- s.logical_reads as session_logical_reads,
-- r.logical_reads as request_logical_reads,
r.start_time as request_start_time,
r.status as request_status,
master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(r.plan_handle) as plan_handle,
-- Steve: Fixes begin here:
when convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond,
DATEADD ( minute,DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() ))
then convert(float, r.cpu_time) / convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000
+ DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() )) else convert(float, 1.0) end
isnull (datediff(ms, case when r.start_time < @WithActivitySince then @WithActivitySince else r.start_time end, getdate()), 0)
case when s.login_time > getdate() then convert(float, s.cpu_time) / (datediff(dd, s.login_time, getdate()) * cast(86400000 as bigint) + datediff(ms, dateadd(dd, datediff(dd, s.login_time, getdate()), s.login_time), getdate())) else convert(float, 1.0)
end as avg_session_cpu_per_ms,
convert(bigint,isnull(datediff(s, case when s.login_time < @WithActivitySince then @WithActivitySince else s.login_time end, case when r.request_id is null then s.last_request_end_time else getdate() end), 0) )* 1000
from sys.dm_exec_sessions s
left join sys.dm_exec_requests as r on s.session_id = r.session_id and s.session_id = 1
-- Steve: Fixes end here
where (s.last_request_end_time > @WithActivitySince or r.request_id is not null)) as d
where (avg_request_cpu_per_ms * request_ms_in_window) + (avg_session_cpu_per_ms * session_ms_in_window) > 1000.0</CommandText>
...potreste avere un errore nella visualizzazione dei report della Performance Dashboard. Questo è dovuto
Great solution David. Microsoft needs to hire some people like you.
I have posted this question to about a dozen different boards and have not received any response...does Microsoft even support this thing or read the posts people put out there?!?
I originally got the conversion error. I applied the fix that was mentioned. Things worked fine. I am nor getting this:
Difference of two datetime columns caused overflow at runtime.
Does ANYONE know what this is and where I can go to fix it???
I would like to let some of my developers see the dashboard. I ahve not given them SA rights. When they try to view the rep[orts they get 'Error you do not have permission to run sys.traces .
My question is how do I grant them permissions to the sys. schema without granting SA rights?
The main report has a table for user requests and user sessions? What are the threshold figures for the items in this table?
The expensive queries section of the screenshot has several links. Are the stats collected for queries ran on a db where the sql code is in the programming language and not in TSQL?
It would be nice if the fixes to the setup.sql and to the recent_cpu.rdl were rolled-up into a new download msi. Or will these be fixed when SQL 2005 SP3 is released!?