SQL Server 2005 Performance Dashboard Using SSRS

Question

SQL Server 2005 Performance Dashboard Using SSRS

Viewing 15 posts - 31 through 45 (of 51 total)

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

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 1

The dataset would also need the server name you are reporting on. That's an interesting modification. I'm not sure you'll be able to make both the report and the data source dynamic.

Laura Grob SSC Eights! Points: 926 More actions · Answer 2

The interesting part is it works for all the other sub reports - this is the only one that is having a problem.

Bob Griffin SSCertifiable Points: 5982 More actions · Answer 3

sibir1us (7/28/2009)
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,

I ran into to this problem, "Difference of two datetime columns caused overflow at runtime" on sql2005 today for the first time. Your code above worked....Thanks! I just wonder what changed to cause it to start happening....????

Laura Grob SSC Eights! Points: 926 More actions · Answer 4

What changed is the number of days the system was up and that is what caused the issue.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

Nice read Robert. But I still find SSRS incometent to exploit the benefits of enterprise dashboarding.

Raunak J

ianstirk Ten Centuries Points: 1310 More actions · Answer 6

Hi,

Nice article.

You can discover a lot more about DMVs in this forthcoming book http://www.manning.com/stirk. Chapter 1 can be downloaded for free and includes scripts for:

A simple monitor

Finding your slowest queries

Find your missing indexes

Identifying what SQL is running now

Quickly find a cached plan

Thanks

Ian

Boxer SSC-Addicted Points: 495 More actions · Answer 7

Boxer

SSC-Addicted

Points: 495

December 3, 2010 at 6:11 am

#1258340

Good one!!

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 8

Great Article!

I'd love to install the reports on a management server and have it email me the results from each of our servers, haven't quite started looking at how to programatically change the connection for each subscription. This way I coould have 1 set of RDL's that hit a different connection for each subscription.

Anyone have any thoughts?

Laura Grob SSC Eights! Points: 926 More actions · Answer 9

Anyone know if there are performance dashboard reports available for SQL 2008 and SQL 2008R2?

medwards-789575 SSC Rookie Points: 42 More actions · Answer 10

After running the SETUP.SQL script, I'm getting the following errors:

Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27

'object_schema_name' is not a recognized built-in function name.

Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42

Incorrect syntax near the keyword 'as'.

Msg 15151, Level 16, State 1, Line 1

Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission.

Any help would be spiffy.

Laura Grob SSC Eights! Points: 926 More actions · Answer 11

Are you running the script on the MSDB in a SQL 2005 instance that is SP2 or higher?

TalkSql SSC Enthusiast Points: 189 More actions · Answer 12

Matt,

I had the same error too, after spending few mins in Google I found this article which solved the issue. Hope this helps.

http://sqlblogcasts.com/blogs/neilhambly/archive/2010/08/27/sql-2005-reports-using-the-dmv-dm-os-sys-info-tweaks-for-2008.aspx

Let me know if this solved your problem

Best,

Bala.

medwards-789575 SSC Rookie Points: 42 More actions · Answer 13

I did run it against the MSDB just to be sure and it gave the same error.

Bala, I looked at the link you posted and it did not help.

TalkSql SSC Enthusiast Points: 189 More actions · Answer 14

OK, I am not sure about your error, but assuming that this might be error you are getting..

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.

So, try this post which asks us to change the directory for the "Performance Dashboard "

[Download and install the performance dashboard reports as normal (but save the files in the Program Files\Microsoft SQL Server\100\Tools\PerformanceDashboard directory) and then modify the setup.sql file as shown below before running it against your SQL Server 2008 instance.]

http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx

Once you are done with this, then come back check this post.

Then also again go back to this post where you have to change a SP "MS_PerfDashboard.usp_Main_GetCPUHistory" If you are using SQL Server 2008.

This is how i made it work, Hope this will work for you too. Let me know.

BEST,

Bala

medwards-789575 SSC Rookie Points: 42 More actions · Answer 15

Bala, I'm sorry, I forgot to mention that I'm running 2005 SP2.