Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Performance Dashboard Using SSRS


SQL Server 2005 Performance Dashboard Using SSRS

Author
Message
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
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.
Mike Bennett-683749
Mike Bennett-683749
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
Rome-828069
Rome-828069
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
Sam-633226
Sam-633226
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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
Rome-828069
Rome-828069
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 88
Try the steps I listed in my posts.
Sam-633226
Sam-633226
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 22
No luck still getting the same error..
dma-669038
dma-669038
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 1035
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.
Laura Grob
Laura Grob
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 460
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?
Laura Grob
Laura Grob
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 460
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.
Laura Grob
Laura Grob
Mr or Mrs. 500
Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)Mr or Mrs. 500 (543 reputation)

Group: General Forum Members
Points: 543 Visits: 460
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search