SQL Server 2005 Performance Dashboard Using SSRS

  • Bob Griffin

    SSCertifiable

    Points: 5976

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

  • sibir1us

    SSC Eights!

    Points: 828

    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

  • timblackwell

    Grasshopper

    Points: 23

    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.

  • Bob Griffin

    SSCertifiable

    Points: 5976

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

  • MattW2010

    Default port

    Points: 1472

    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

  • MattW2010

    Default port

    Points: 1472

    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

  • SanjayAttray

    SSChampion

    Points: 13157

    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.

  • amc-885860

    SSC-Addicted

    Points: 493

    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?

  • mcasner

    SSC Journeyman

    Points: 94

    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?

  • Bob Griffin

    SSCertifiable

    Points: 5976

    AMC,

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

  • amc-885860

    SSC-Addicted

    Points: 493

    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.

  • Rome-828069

    Say Hey Kid

    Points: 673

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

  • MattW2010

    Default port

    Points: 1472

    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?

  • Bob Griffin

    SSCertifiable

    Points: 5976

    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...

  • mcasner

    SSC Journeyman

    Points: 94

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

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

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