如果你有一个服务器的SQLServer 2005
性能仪表板报告
在这篇文章中我将介绍如何安装这些报告,但也更正前两个恼人的错误.
文件Program Files \微软SQL Server \ 90 \ ToolsPerformanceDashboard然后编辑该文件在这个目录位于setup.sql并更换代码.:
sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
par
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,
Cela corrige une erreur « Difference of two datetime columns caused overflow at runtime » lorsque l’on charge la page d’accueil du rapport qui se produit lorsque l’uptime du serveur dépasse les 3-4 semaines 1 .
Pour la deuxième correction de bug à effectuer, éditer le fichier recent_cpu.rdl et remplacer le code à partir de la ligne 3271 et jusqu’à la ligne 3305 par :
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>
Cela corrige le même genre de problème que précédemment, cette fois lorsque l’on clique sur les zones bleues (SQL) du graphique System CPU Utilization sur la page d’accueil du rapport 2 .
Pour charger les rapports, il suffit de faire un clic droit sur le serveur SQLServer 2005 SP2 sur lequel l’installation a été effectuée et de sélectionner Reports > Custom Reports puis de sélectionner le fichier C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\performance_dashboard_main.rdl . Si vous souhaitez utiliser ce rapport depuis un poste client il faudra partager le répertoire.
- Source : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx [ ↩ ]
- Source : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx [ ↩ ]
