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 6:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 10:01 AM
Points: 1,136, Visits: 697
amc (7/28/2009)
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.


Check out the first post of this discussion by sibir1us. I think that is the solution to what you are seeing.

Post #761210
Posted Wednesday, July 29, 2009 5:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 6:34 AM
Points: 3, Visits: 13
I also had an issue with usp_Main_GetSessionInfo due to the max value for milliseconds in the DateDiff function being less than 24 Days (etc). Therefore, to maintain a good degree of accuracy still (and having had the problem before), I rewrote it like so :-
USE [msdb]
GO
ALTER PROCEDURE [MS_PerfDashboard].[usp_Main_GetSessionInfo]
AS
BEGIN
SELECT COUNT(*) AS num_sessions,
SUM(s.total_elapsed_time) AS total_elapsed_time,
SUM(s.cpu_time) AS cpu_time,
SUM(s.total_elapsed_time) - SUM(s.cpu_time) AS wait_time,
SUM(s.login_time) - SUM(s.total_elapsed_time) AS idle_connection_time,
cache_hit_ratio=(CASE WHEN SUM(s.logical_reads) > 0 THEN (SUM(s.logical_reads) - ISNULL(SUM(s.reads),0)) / CONVERT(FLOAT,SUM(s.logical_reads))
ELSE NULL END)
FROM (SELECT session_id,
CONVERT(BIGINT,total_elapsed_time) AS total_elapsed_time,
CONVERT(BIGINT,cpu_time) AS cpu_time,
CONVERT(BIGINT,DATEDIFF(ss,login_time,GETDATE()))*1000 AS login_time,
logical_reads, reads
FROM sys.dm_exec_sessions
WHERE is_user_process = 0x1
AND login_time UNION
SELECT session_id,
CONVERT(BIGINT,total_elapsed_time) AS total_elapsed_time,
CONVERT(BIGINT,cpu_time) AS cpu_time,
CONVERT(BIGINT,DATEDIFF(ms,login_time,GETDATE())) AS login_time,
logical_reads,reads
FROM sys.dm_exec_sessions
WHERE is_user_process = 0x1
AND login_time>DATEADD(minute,-35791,GETDATE())) s
END
Post #761415
Posted Wednesday, July 29, 2009 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 1, 2009 7:50 AM
Points: 21, Visits: 88
Bob Griffin (7/28/2009)
nathan.holtrop (7/28/2009)
I am having the same problem as mcasner, any suggestions?


For the permissions problem, make sure you have select privilege on view server state...here's some info on that:

from:
http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx
To query a server scoped DMV, the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV, the user must have SELECT privilege on VIEW DATABASE STATE.

GRANT VIEW SERVER STATE to
GRANT VIEW DATABASE STATE to


Just so others know...

GRANT VIEW SERVER STATE to
GRANT VIEW DATABASE STATE to

For these commands to work the user had to exist in the msdb db and the master db.

As well as

grant alter trace to



Post #761514
Posted Monday, August 3, 2009 3:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 27, 2010 4:33 PM
Points: 4, Visits: 22
While running the script ,me getting this error...need help

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.

Thanks in advance...Sam
Post #764421
Posted Tuesday, August 4, 2009 6:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 1, 2009 7:50 AM
Points: 21, Visits: 88
Try the steps I listed in my posts.
Post #764742
Posted Tuesday, August 4, 2009 3:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 27, 2010 4:33 PM
Points: 4, Visits: 22
No luck still getting the same error..
Post #765150
Posted Tuesday, August 25, 2009 2:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 19, 2014 9:26 AM
Points: 312, Visits: 1,028
Thank you for the great article. It works for me if I ask it to prompt for login and I give my credentials. But if I use the service account that has sysadmin privileges I get an error like this:

An error has occurred during report processing.
Cannot impersonate user for data source 'DataSource1'.
Logon failed.
Logon failure: the user has not been granted the requested logon type at this computer. (Exception from HRESULT: 0x80070569)

Also if I use my credentials the authentication is not passed down to the smaller reports - it only works on main performance dashboard. Any suggestions much appreciated, thanks.
Post #777061
Posted Wednesday, May 12, 2010 4:32 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:03 PM
Points: 525, Visits: 327
Most of it works great published in SSRS and modified to allow the server name and instance name to manually entered except when I go into a sub, sub report. For example if I run the main performance dashboard report, then click on expensive queries by duration all is well. When I click on one of the queries returned by that sub report I get a screen that shows all of the prompts but does not return data. I went into BIDS and checked what the blank parameter was and found it was servername. If I fill that in I still receive an error message regarding a dataset. Any suggestions?
Post #920909
Posted Wednesday, May 12, 2010 4:43 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:03 PM
Points: 525, Visits: 327
I had a similar issue when trying to use windows integrated security. When I used credentials supplied by the user and checked the box for use as windows credentials when connecting to the data source I was able to run the reports. The user does have to enter their full domain\lanid and password but, it worked for me as long as there was a trust between the domains.
Post #920912
Posted Wednesday, May 12, 2010 4:44 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:03 PM
Points: 525, Visits: 327
Laura Grob (5/12/2010)
I had a similar issue when trying to use windows integrated security. When I used credentials supplied by the user and checked the box for use as windows credentials when connecting to the data source I was able to run the reports. The user does have to enter their full domain\lanid and password but, it worked for me as long as there was a trust between the domains.


Sorry this was in response to SSC Veteran's question


Post #920913
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse