SQL Server 2005 Performance Dashboard Reports -- Now Available!

Those ever productive folks at MS « Getting used to an upside down life

Those ever productive folks at MS « Getting used to an upside down life — Thu, 08 Mar 2007 04:44:50 GMT

PingBack from http://mostgrandadventure.wordpress.com/2007/03/08/those-ever-productive-folks-at-ms/

SQL Server 2005 Performance Dashboard Reports

EXEC dbo.LongTermMemory__Archive — Thu, 08 Mar 2007 11:17:16 GMT

Keith Elmore, an escalation engineer at Microsoft, has created a "Performance Dashboard" for SQL Server...

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Juan Alvarado — Thu, 08 Mar 2007 20:16:39 GMT

Excellent job .. it's very useful for look where is the problem. One feature that i suggest is if you can indicate the T-SQL that have table scans or index scans in a dashboards, its a good information when have performance problems.

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Jerry Hung — Thu, 15 Mar 2007 18:21:16 GMT

Great report

I wish MS releases more of these for free

I like the "Missing Index" best thus far

but the word "Dashboard" just turns me on :P

or Business Scorecard

Per vedere dove SQL Server 2005 va piano

Tecnologie .NET (Dotnet): Sharepoint, BizTalk e .NET — Fri, 16 Mar 2007 00:35:01 GMT

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Matt — Wed, 28 Mar 2007 21:16:17 GMT

This is awesome stuff!!!

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Rick — Mon, 02 Apr 2007 20:58:12 GMT

I'm getting an error message when i try and open the reports now because of the line:

convert(bigint, datediff(ms, login_time, getdate()))

inside usp_Main_GetSessionInfo.

The difference between login_time and getdate() is more than 24 days so the datediff fails.... i get the following message:

Difference of two datetime columns caused overflow at runtime.

thanks.

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Mike — Tue, 03 Apr 2007 01:10:02 GMT

The dashboard was working splendid until this morning with the following error:

Difference of two datetime columns caused overflow at runtime.

Have reloaded the msi package (local server/client) and deleted sp's/functions and re-ran setup.sql script on remote server with the same results.

Any suggestions appreciated..

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

David — Thu, 05 Apr 2007 13:58:03 GMT

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

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,

then it will work

hopes this helps the rest of you who have the same problem.

SQL Server 2005 Performance Dashboard Reports

SQL Server 開発チームブログ — Thu, 05 Apr 2007 16:43:10 GMT

マイクロソフト星川です。先日 SQL Server 2005 Performance Dashboard Reports と呼ばれる新しいツールをリリースしました。 http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Ron Landers — Thu, 05 Apr 2007 23:50:47 GMT

I have run the setup.sql script and then the performance_dashboard_main.rdl.

I am getting errors when I try to run most the reports.

Errors are:

--"The 'version_string' jparameter is missing a value"

--"The "Report parameter 'sql_handle' was not found"

--"Report parameter 'include_system_processes' was not found"

Any help available on this?

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

cori — Wed, 09 May 2007 20:11:12 GMT

David;

thanks for the fix. very helpful!

cori

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

romeolanuza@verizon.net — Fri, 11 May 2007 15:35:30 GMT

Did i do it thr right way....!!!!!????? serever management......?????

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

daddyev — Fri, 11 May 2007 18:47:05 GMT

I am getting "The 'version_string' parameter is missing a value" when running the database_overview.rdl

Does anyone have a fix for this problem? H-E-L-P!!

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Naras — Thu, 17 May 2007 00:54:41 GMT

Thanks. Good work.

Naras

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Heinrich — Wed, 23 May 2007 16:50:34 GMT

Hello,

i have a problem when i click on any blue region in the chart for the SQL CPU utilization on the performance dashboard where you normally would get further infos (reports for the last 15 minutes).

There comes no repport but an Error: Arithmetic overflow error conferting expression to data type datetime.

This happens on two different servers with SQL Server 2005 Enterprise Editions, but all other driil through is functioning. What' s the problem, is it a problem in recent_cpu.rdl or performance_dashboard_main.rdl or in the called stored procedures. Please help, it is a really good tool, but not good when functioning only partly.

Thanks for help

Heinrich

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Lars D — Wed, 30 May 2007 10:56:08 GMT

I also have problem when I click on the blue region for the CPU (CPU System Utilization), and it happens on only one server, other servers works fine

Following error

---------------------------------

An error has occurred during report processing.

Cannot read the next data row for the data set SESSION_REQUEST_ACTIVITY.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

---------------------------------

Any ideas how to fix it?

Lars

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

DKRoul — Wed, 30 May 2007 12:06:39 GMT

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select *

from datetable

where cast(('May 29 2007 6:30:00:000PM' - endtime) as int) >=2

and cast(('May 29 2007 6:30:00:000PM' - endtime) as int)<=3

and datetable_id = 102

order by datetable_id desc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id 102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can i handle this?

Thanks

~DKRoul

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Heinrich — Mon, 11 Jun 2007 18:55:19 GMT

Hello,

as i have wrote before (May 23, 2007) i wasn't able to use the Performance Dashboard because of the described failure. A week later the report functions without changing something. It works fine for some days until 08. June 07, then in the morning i want to look again in the chart for SQL CPU utilization and nothing functions again, but now i get the failure as described from other users, that is: Difference of two datetime columns caused overflow at runtime. I have implemented the fix as described by David, but it doesn't help, i think there must be more bugs in the programming with datetime-conversion etc.

Now again, i am not able to use this fantastic tool.

Does anyone have a fix for this problem? Please help.

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Beatrice — Wed, 13 Jun 2007 16:48:07 GMT

No bug.

In my case it was an inconsistency in the language settings between the web server and SQL server (or SQL server user).

Check that the user you're using to browse the reports has british english as a language.

Beatrice.

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Mic — Tue, 19 Jun 2007 11:26:13 GMT

Hi,

About the "Arithmetic overflow error converting expression to data type datetime." after clicking the blue-CPU-Line:

This is an conversion error between the main and the recent_cpu report. You can solve this by changing the type of the "WithActivitySince" Parameter from String to DateTime (at the top of the XML file).

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Shahryar G. Hashemi — Wed, 25 Jul 2007 02:14:27 GMT

Hi,

I applied the fix as identified above back in May 2007 to my setup.sql script and it was working for a short time. However now I am receiving the same "Difference of two datetime columns caused overflow at runtime." error. I have reloaded the fixed setup.sql script on my box and I continue to get the error. The problem happens when clicking on the CPU utilization graph to see what processes were running on a given server at a specific time.

Can anyone help?

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

David — Sun, 29 Jul 2007 03:19:28 GMT

I'm getting the parameter issues mentioned by Ron above. Any help?

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

David — Sun, 29 Jul 2007 03:44:11 GMT

Nevermind. Duh...should have read directions. For others...open through the performance_dashboard_main.rdl which supplies params.

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Randy In Marin — Thu, 09 Aug 2007 22:16:59 GMT

The datetime value is days - fractional part is time. 86400000 = 24 * 60 * 60 * 1000 converts days to ms. This should should avoid overflow here and in most cases. I have not tried extreme dates - perhaps the difference won't be in the range of a valid datetime?

CAST(FLOOR(86400000 * CAST(getdate() - login_time as float)) as bigint) -- ms

Приборная панель производительности SQL Server 2005

Alexander Gladchenko — Tue, 14 Aug 2007 11:36:11 GMT

Microsoft SQL Server 2005 Performance Dashboard Reports предназначен для контроля и получения необходимой

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Shahryar G. Hashemi — Tue, 21 Aug 2007 19:29:07 GMT

Randy,

I saw your update. Do you know where I need to do the CAST update? Also when will Microsoft update the Performance Dashboard?

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Don — Mon, 27 Aug 2007 17:41:58 GMT

I'm getting the "Difference of two datetime columns caused overflow at runtime" error when trying to drill down into the Blue SQL Server CPU usage on the main dashboard screen. I understand the problem with uptime (server has been up for more than 24 days), datediff, and ms but I'm having some problems tracking down where the change from ms to ss needs to be made for this drill down functionality to work again. I tried a profiler trace, but the only procedure I see executing is the usp_CheckDependencies. After that I just see a bunch of execute_sql statements.

Any help would be appreciated

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Vipin — Fri, 07 Sep 2007 17:51:32 GMT

Any idea if there are any issues while converting from timestamp to big int???

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Shahryar G. Hashemi — Wed, 12 Sep 2007 06:10:48 GMT

Hi,

I do where the following change in the datetime difference should be made:

CAST(FLOOR(86400000 * CAST(getdate() - login_time as float)) as bigint) -- ms

I get the error now on any SQL Server environment where connections have been online for more than a month.

Can someone please help me. Or can you let me know when the Performance Dashboard will be updated?

Shahryar

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Marios Philippopoulos — Wed, 24 Oct 2007 00:19:56 GMT

Regarding the "The 'version_string' parameter is missing a value" when running the database_overview.rdl" error.

A quick and dirty fix is to delete all references to version_string in database_overview.rdl. Version_string (1.0) is the version of DashboardReports functionality. Not really useful to have it there anyway...

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Shahryar G. Hashemi — Wed, 21 Nov 2007 01:42:52 GMT

Hi,

I have posted 3 times indicating the error with datetime difference but no one seems to have an answer. Is this product even supported by Microsoft? Will there be an update or basically we have it as is until SQL 2008 is out?

I would really like to know.

Shahryar

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Dak Peoples — Wed, 21 Nov 2007 21:34:09 GMT

@ David

Thanks for your fix for SQL Server 2005 Performance Dashboard Setup.SQL by changing the following line from:

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,

Worked perfectly to resolve the "Difference of two datetime columns caused overflow at runtime" error.

-Dak

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

harryaya — Wed, 05 Dec 2007 08:33:11 GMT

Great! The fix works perfect on "Difference of two datetime columns caused overflow at runtime" error.

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

zerep — Thu, 06 Dec 2007 19:53:48 GMT

Hello,

i have the same problem that Heinrich posted on May 16,

"when i click on any blue region in the chart for the SQL CPU utilization on the performance dashboard where you normally would get further infos (reports for the last 15 minutes).

There comes no repport but an Error: Arithmetic overflow error conferting expression to data type datetime.

Thanks for help

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Anthony Robinson — Wed, 02 Jan 2008 20:14:30 GMT

Has anyone found a fix for this particualr error:

An error has occurred during report processing.

Cannot read the next data row for the data set SESSION_REQUEST_ACTIVITY.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

I applied the fix for the conversion issue, worked great....but still would like to get this other one knocked out.

Thanks!!

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Anthony Robinson — Thu, 03 Jan 2008 01:25:44 GMT

Did anyone ever find a solution to this issue:

An error has occurred during report processing.

Cannot read the next data row for the data set SESSION_REQUEST_ACTIVITY.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

It's a different query altogether from the fix that was listed.

Thanks!

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

JornNystad — Mon, 07 Jan 2008 12:12:33 GMT

Thank you for an exellent tool!

Does anyone know if its possible to exclude replication queries from the report?

Thanx

Jørn Nystad

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Steve Schneider — Sat, 12 Jan 2008 01:03:16 GMT

I have a fix for the ability to drill down into the blue zone when you have a server which has been running for 24 or more days:

Go to the recent_cpu.rdl file located in (default):

C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard

go to line number 3271, you should see the "From" statement of a sql query...

paste what follows thru line 3306 of the original file...

(PS... save your ORIGINAL FILE)!!!

caveat emptor!!!

Don't haze me if this doesn't work for you!

It works for me!

from (select s.session_id,

r.request_id,

s.login_time,

-- s.host_name,

s.program_name,

s.login_name,

s.status as session_status,

s.last_request_start_time,

s.last_request_end_time,

s.cpu_time as session_cpu_time,

r.cpu_time as request_cpu_time,

-- s.logical_reads as session_logical_reads,

-- r.logical_reads as request_logical_reads,

r.start_time as request_start_time,

r.status as request_status,

r.command,

master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,

master.dbo.fn_varbintohexstr(r.plan_handle) as plan_handle,

r.statement_start_offset,

r.statement_end_offset,

case

-- Steve: Fixes begin here:

when convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond,

DATEADD ( minute,DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() ))

> 0

then convert(float, r.cpu_time) / convert(bigint, CAST ( DATEDIFF ( minute, start_time, getdate()) AS BIGINT)*60000

+ DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, start_time, getdate() ), Start_time ),getdate() )) else convert(float, 1.0) end

as avg_request_cpu_per_ms,

isnull (datediff(ms, case when r.start_time < @WithActivitySince then @WithActivitySince else r.start_time end, getdate()), 0)

as request_ms_in_window,

case when s.login_time > getdate() then convert(float, s.cpu_time) / (datediff(dd, s.login_time, getdate()) * cast(86400000 as bigint) + datediff(ms, dateadd(dd, datediff(dd, s.login_time, getdate()), s.login_time), getdate())) else convert(float, 1.0)

end as avg_session_cpu_per_ms,

convert(bigint,isnull(datediff(s, case when s.login_time < @WithActivitySince then @WithActivitySince else s.login_time end, case when r.request_id is null then s.last_request_end_time else getdate() end), 0) )* 1000

as session_ms_in_window

from sys.dm_exec_sessions s

left join sys.dm_exec_requests as r on s.session_id = r.session_id and s.session_id = 1

-- Steve: Fixes end here

where (s.last_request_end_time > @WithActivitySince or r.request_id is not null)) as d

where (avg_request_cpu_per_ms * request_ms_in_window) + (avg_session_cpu_per_ms * session_ms_in_window) > 1000.0</CommandText>

<Value>=Parameters!WithActivitySince.Value</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>DataSource1</DataSourceName>

</Query>

Quando SQL Server va troppo bene...

Impedance Mismatch — Wed, 30 Jan 2008 18:23:46 GMT

...potreste avere un errore nella visualizzazione dei report della Performance Dashboard. Questo è dovuto

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Vince — Wed, 13 Feb 2008 22:12:09 GMT

Great solution David. Microsoft needs to hire some people like you.

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Anthony Robinson — Mon, 03 Mar 2008 21:30:49 GMT

I have posted this question to about a dozen different boards and have not received any response...does Microsoft even support this thing or read the posts people put out there?!?

I originally got the conversion error. I applied the fix that was mentioned. Things worked fine. I am nor getting this:

An error has occurred during report processing.

Cannot read the next data row for the data set SESSION_REQUEST_ACTIVITY.

Difference of two datetime columns caused overflow at runtime.

Does ANYONE know what this is and where I can go to fix it???

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Russ — Mon, 14 Apr 2008 11:53:39 GMT

I would like to let some of my developers see the dashboard. I ahve not given them SA rights. When they try to view the rep[orts they get 'Error you do not have permission to run sys.traces .

My question is how do I grant them permissions to the sys. schema without granting SA rights?

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Brad — Tue, 29 Apr 2008 05:49:32 GMT

The main report has a table for user requests and user sessions? What are the threshold figures for the items in this table?

The expensive queries section of the screenshot has several links. Are the stats collected for queries ran on a db where the sql code is in the programming language and not in TSQL?

re: SQL Server 2005 Performance Dashboard Reports -- Now Available!

Michael Lacy — Wed, 02 Jul 2008 17:32:53 GMT

It would be nice if the fixes to the setup.sql and to the recent_cpu.rdl were rolled-up into a new download msi. Or will these be fixed when SQL 2005 SP3 is released!?

Приборная панель производительности SQL Server 2005

Alexander Gladchenko — Mon, 06 Apr 2009 10:57:50 GMT

Microsoft SQL Server 2005 Performance Dashboard Reports предназначен для контроля и получения необходимой