SQL Server 2005 Performance Dashboard Using SSRS

  • Comments posted to this topic are about the item SQL Server 2005 Performance Dashboard Using SSRS

  • If you would like to use the Performance Dashboard 2005 on SQL Server 2008 you will have to make the following change in the usp_Main_GetSessionInfo stored procedure.

    Change the line:

    sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

    to

    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,

    Make everything as simple as possible, but not simpler.
    Albert Einstein

  • found I had to to this to get it to work in 2005 otherwise got an overflow error when calcing the difference between 2 datetimes.

  • I haven't ran into either of those problems yet. Thanks for the tips though!

  • When running the statement for GetCPUHistory for 2008, it is telling me that "cpu_ticks_in_ms" is an invalid column for sys.dm_os_sys_info. Can you please advise? Thanks - Matt

  • Also, after running the Performace_Dashboard_Main.rdl for 2008 (which worked), I had to manually insert the default values for every version string parameter. Should the parameter that defaults for the Main report be the same for the rest of them?

    Lastly, what sort of values would I use in order for the report to appear for the query_plan.rdl report?

    Thanks again,

    Matt

  • Thanks for article Bob. We use three different third party tools to monitor same stuff but good to have all in one place.

    SQL DBA.

  • Hi, I'm a new dba trainee.

    I'm not finding the 'reports' folder after I created a new solution project "Performance Dashboard" in the Solutions Explorer.

    Did I miss a step? Anyone?

  • I followed the steps in this post and everything seems to work. However, I get an error "query execution failed for data set "CPU_UTILIZATION_HISTORY" User does not have permission to perform this action.

    I thought that the grant statements in the setup.sql would have taken care of this.

    Anyone have any suggestions?

  • AMC,

    Just make sure your created it as a "report server project" on the opening "create project" screen and the folder should be there.

  • Thanks Bob! That was quick.

    I re-traced my steps and as you just stated that's where I went wrong.

    So I'm back on track now.

    However, When I opened the "performance_dashboard_main.rd.",

    I received this error in the Preview tab:

    An error has occurred.....

    Cannot read the next data row for the data set SESSION_CPU_WAIT_INFO.

    Difference of two datetime colums caused overflow at runtime.

    Please advise.

  • I am having the same problem as mcasner, any suggestions?

  • For 2008, should we use ms_ticks instead of cpu_ticks_in_ms from sys.dm_os_sys_info when creating procedure MS_PerfDashboard.usp_Main_GetCPUHistory?

  • The Performance Dashboard was written for sql 2005. I have not ran on sql 2008, so I will have to defer the rest of the group...

  • I am running SQL Server 2005 and I'm still getting the error I mentioned.

Viewing 15 posts - 1 through 15 (of 51 total)

You must be logged in to reply to this topic. Login to reply