SQL Server 2005 Performance Dashboard Using SSRS

Question

SQL Server 2005 Performance Dashboard Using SSRS

Bob Griffin

SSCertifiable

Points: 5982
More actions
July 28, 2009 at 12:26 am

#212594

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

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

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

sibir1us SSC Eights! Points: 828 More actions · Answer 1

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 More actions · Answer 2

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: 5982 More actions · Answer 3

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

MattW2010 SSCommitted Points: 1532 More actions · Answer 4

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 SSCommitted Points: 1532 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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: 5982 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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

MattW2010 SSCommitted Points: 1532 More actions · Answer 12

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: 5982 More actions · Answer 13

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 More actions · Answer 14

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