SQL Server Performance Dashboard Reports in SSMS – Introduction, Install, DateDiff error & Modified for SQL 2008
Many of you seasoned DBA’s out there would have heard about the SQL Server 2005 Performance Dashboard Reports and used it before. I find the reports highly useful, time saving and just like a car dashboard, allows me to get a picture of how my SQL Server is performing.
For those who don’t know about it, you can download it from here and add it as a custom report to Management Studio (SSMS). It is a free download! Read on below to find out how to install it and use it on your SQL Server. I have also included custom modified files that allow you to use this for a SQL Server 2008 instance. This is provided “as is” with no guarantees. J
IntroductionWell, the reports are mainly intended to quickly identify performance problems and help resolve them. These reports use the DMV’s that came out with SQL 2005, so they are very fast & reliable information when troubleshooting a performance problem. Some of the common problems that can be detected using the Dashboard Reports are:
1) CPU bottlenecks (which queries are consuming the most CPU and Plan for this query)
2) IO bottlenecks (which queries are performing the most IO and Plan for this query).
3) Index recommendations generated by the query optimizer (missing index recommendations pulled from sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats)
5) Latch contention and other Wait Types
1. You need to have Management Studio installed on the machine where you intend to use the Performance Dashboard Reports (could be either client or server machine).
2. Your SQL Server 2005 needs to have at a minimum SP2 applied (build 9.0.3042 or greater)
1. You can install the Performance Dashboard Reports from http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
2. Once you install the above, go to %\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard and run the setup.sql script against the SQL instance that you want to monitor the performance for. Note: The script will create some Stored Procedures, table-valued & scalar functions in the MSDB database. All the SP’s created by Dashboard with have the schema MS_PerfDashboard.
If you want more information on the newly created objects, use the query belowselect * from sys.objects where schema_id = SCHEMA_ID('MS_PerfDashboard')
3. Open a new management studio window, right-click on instance name -> Reports -> Custom Reports. Browse to the location specified above and choose the performance_dashboard_main.rdl and click OK. This will add the performance dashboard to SSMS.
4. Next time when you right-click on the instance and go to Reports, you should see the entry performance_dashboard_main. Clicking on this will launch the Main Dashboard page.
ErrorsWell, I’ve seen some cases where you get the following error when you try to launch the main Dashboard Report.
“Difference of two datetime columns caused overflow at runtime"
This is an issue with datediff overflow in the GetSessionInfo Stored Procedure. To fix this, modify the SP using the query below,USE msdbGO
ALTER procedure MS_PerfDashboard.usp_Main_GetSessionInfo
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
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,
case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) -
isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))
end as cache_hit_ratio
from sys.dm_exec_sessions s
where s.is_user_process = 0x1
The highlighted section above is the actual change in the Stored Proc. After this re-launch the report and it should work fine now. Here is how she looks,
Before I forget, there is one small issue still present with the dashboard report. When you drill-down and open multiple reports, and you use the back button, it loses context for the window and might take to other open query windows instead of the actual previous page. I’m afraid there is no solution to this problem yet. L
Using Performance Dashboard on SQL Server 2008When you install SQL Server 2005 Performance Dashboard Reports in SQL 2008, run the setup.sql, you will get this error:
Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6
Invalid column name 'cpu_ticks_in_ms'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_Main_GetCPUHistory', because it does not exist or you do not have permission.
The error is raised in the following portion of the script: declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
This error is because of changes to some DMV’s in SQL 2008 and inclusion of additional columns. There is no official Dashboard Reports available for SQL Server 2008 (yet!). However with some tweaks/mod’s you can use the same set of RDL files to get dashboard working.Attached are two files, setup.sql1 and a modified performance_dashboard_main.rdl1( rename from .txt to .rdl1)Instead of the file present in the install folder, first run the above setup.sql to create the dashboard objects in the MSDB database. Then replace the attached RDL file with the one present in the install folder. Follow the instructions given above in adding the Main Report to SSMS and viola; you’ve got a dashboard for SQL 2008.
I hope this post was useful in getting you started with Performance Dashboard. Surprise us, when you call in the next time for support on a performance issue and we already find the dashboard installed and ready to go.
Regards, Sudarshan NarasimhanTechnical Lead, Microsoft SQL Server CSS
I made the 'usp_Main_GetCPUHistory' proc universal with this bit of code so you can deploy to all your servers and not worry about the version:
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @ts_now bigint;
DECLARE @IntVariable int;
IF (@@microsoftversion / 0x1000000 & 0xff) > 9 --[VersionMajor]
SET @SQLString = N'SELECT @ts_nowOUT = ms_ticks from sys.dm_os_sys_info';
SET @SQLString = N'SELECT @ts_nowOUT = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info'
SET @ParmDefinition = N'@ts_nowOUT bigint OUTPUT';
@ts_nowOUT = @ts_now OUTPUT;
Will this work for 2008 R2 and/or 2008 R2 SP1?
Thanks for performance_dashboard_main.rdl1, it is working fine but other report are not working, they showing The 'version_string' parameter is missing a value.
Please how slove this problem.