Since SP2 for SQL Server 2005 we have some Standard Reports available when right-clicking either server or database in the Object Explorer in Management Studio. One of my favorites is the Index Usage Statistics report.
If you have the task to optimize performance of your server or database, Microsoft has provided an interesting additional download called the Performance Dashboard. When you’ve installed the package, there will be some .RDL files and an interesting .CHM file in our SQL Server folder, for instance C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\.
Among this bunch of .RDLs there is one main report called performance_dashboard_main.rdl. All other reports are used in drillthrough scenarios from the main Performance Dashboard report. The main report can be opened by right-clicking your server in SSMS, then choose Reports > Custom Reports… and browse to the RDL.
For a more detailed explanation on the installation procedure, have a look at this Database Journal article. And for an explanation of each report plus some screenshots, part two of that article is certainly useful as well.
Additional info is also available in the PerfDash.chm file located in the same folder as the RDLs.
Earlier this week I came across a bug in one of the stored procedures that the main Performance Dashboard report is using. When I tried executing the report I got the following error:
Msg 535, Level 16, State 0, Line 1
Difference of two datetime columns caused overflow at runtime.
This issue manifests itself when there are open connections which have been open for a period longer than approx. 24.85 days.
You can simulate the same error by executing the following select:
select datediff(ms, '2007-12-27 08:49:50.853', GetDate())
The bug is known by Microsoft, see this Microsoft Connect page, but will not be fixed as these reports are not supported. In the feedback you can get enough info on how to fix the issue. I also got the above query from there although I added an m to select milliseconds instead of seconds (which would not generate the error).
I’ve solved it by modifying the usp_Main_GetSessionInfo stored procedure like this:
CREATE PROCEDURE MS_PerfDashboard.usp_Main_GetSessionInfoas BEGIN select count(*) as num_sessions, sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time, sum(convert(bigint, s.cpu_time)) as cpu_time, sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) as wait_time, /* following line gives an error when login_time > 24 days see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=324906 the line: sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time, */ sum(convert(bigint, datediff(s, login_time, getdate()))) * 1000 - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time, case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads)) else NULL end as cache_hit_ratio from sys.dm_exec_sessions s where s.is_user_process = 0x1 END