Installere Ytelse Dashboard Report om SQLServer 2005

Hvis the har in SQLServer 2005 SP2 server kan installere egendefinerte the report, og særlig Performance Dashboard report fra Microsoft. Yte rapportene Informasjon om og begrensende faktorer server status, slike spørringer forbruke mest CPU-eller tilgang disk, eller om forslag hvordan man skaper manglende indekser .

Ytelse Dashboard report

I denne artikkelen vile JEG beskrive hvordan of installerer disse rapportene, men OGSA korrigere to irriterende bugs.

Den første tingen gjøre st å laste ned installasjonsfilen på Microsofts nettsted, og det på din deretter installere server. Standard report vil bli i installert C: \ Programfiler \ Microsoft SQL Server \ 90 \ ToolsPerformanceDashboard. Deretter redigested setup.sql ligger i denne og katalogen erstatte Koden:

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 ¹ .

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 ² .

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.

1. Source : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx [ ↩ ]
2. Source : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx [ ↩ ]