Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL Server 2005 Performance Dashboard Using SSRS Expand / Collapse
Author
Message
Posted Tuesday, July 28, 2009 12:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
Comments posted to this topic are about the item SQL Server 2005 Performance Dashboard Using SSRS
Post #760534
Posted Tuesday, July 28, 2009 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:08 AM
Points: 24, Visits: 295
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
Post #760689
Posted Tuesday, July 28, 2009 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:28 AM
Points: 1, Visits: 105
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.
Post #760745
Posted Tuesday, July 28, 2009 8:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
I haven't ran into either of those problems yet. Thanks for the tips though!
Post #760768
Posted Tuesday, July 28, 2009 9:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:29 PM
Points: 58, Visits: 398
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

Post #760834
Posted Tuesday, July 28, 2009 9:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:29 PM
Points: 58, Visits: 398
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
Post #760837
Posted Tuesday, July 28, 2009 9:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #760879
Posted Tuesday, July 28, 2009 11:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 19, 2013 11:53 AM
Points: 17, Visits: 559
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?

Post #760960
Posted Tuesday, July 28, 2009 11:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 3, 2014 11:24 AM
Points: 6, Visits: 55
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?
Post #760975
Posted Tuesday, July 28, 2009 11:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
AMC,
Just make sure your created it as a "report server project" on the opening "create project" screen and the folder should be there.
Post #760976
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse