解决Performance Dashboard 两个datetime 列的差别导致了运行时溢出
原文出处：http://dbaonline.wordpress.com/2008/07/10/troubleshooting-sql-2005-performance-dashboard/
Joe.TJ翻译整理。
在使用SMSS Performance Dashboard 时，也许会出现一些问题。在MSDN上找到一些问题的解决方案。但是它们的思路不清晰，所以我就收集起来整理成一个清晰的描述。
注意：在尝试这些解决方案前请备份你的文件，并自负风险。
问题一： 两个 datetime 列的差别导致了运行时溢出
打开 C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\setup.sql.搜索以下代码：
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,
问题二：蓝色区域问题（当Server连续运行超过24天）
打开C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\recent_cpu.rdl.
删除3271到3306行，并用下面的代码替代它们：
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>
<QueryParameters>
<QueryParameter
Name=”@WithActivitySince”>
<Value>=Parameters!WithActivitySince.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataSource1</DataSourceName>
</Query>
问题三：转换表达式的数据类型时出现算术溢出
这个问题大多出现在点击蓝色CPU区域。打开 C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\recent_cpu.rdl.
搜索WithActivitySince参数，并将它从String换成Datetime.
