from my MSDN blog - September 6, 2016
Performance Dashboard
SQL Server Management Studio version 17.2 and later includes the Performance Dashboard
新版SSMS內建Performance Dashboard Report，所以無需執行以下安裝步驟
請注意Expensive queries的計算TSQL如下，與直接查詢sys.dm_exec_query_stats的結果會不同
exec sp_executesql @stmt=N'SELECT
text as query_text,
master.dbo.fn_varbintohexstr(query_hash) as query_hash,
master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
statement_start_offset,
statement_end_offset,
querycount,
queryplanhashcount,
execution_count,
total_elapsed_time,
min_elapsed_time,
max_elapsed_time,
average_elapsed_time,
total_CPU_time,
min_CPU_time,
max_CPU_time,
average_CPU_time,
total_logical_reads,
min_logical_reads,
max_logical_reads,
average_logical_reads,
total_physical_reads,
min_physical_reads,
max_physical_reads,
average_physical_reads,
total_logical_writes,
min_logical_writes,
max_logical_writes,
average_logical_writes,
total_clr_time,
min_clr_time,
max_clr_time,
average_clr_time,
max_plan_generation_num,
earliest_creation_time,
query_rank,
charted_value,
master.dbo.fn_varbintohexstr(plan_handle) as plan_handle
FROM (SELECT s.*,
Row_number() OVER(ORDER BY charted_value DESC) AS query_rank
FROM (SELECT CASE @OrderBy_Criteria
WHEN ''Logical Reads'' THEN SUM(total_logical_reads)
WHEN ''Physical Reads'' THEN SUM(total_physical_reads)
WHEN ''Logical Writes'' THEN SUM(total_logical_writes)
WHEN ''CPU'' THEN SUM(total_worker_time) / 1000
WHEN ''Duration'' THEN SUM(total_elapsed_time) / 1000
WHEN ''CLR Time'' THEN SUM(total_clr_time) / 1000
END AS charted_value,
query_hash,
MAX(sql_handle_1) sql_handle,
MAX(statement_start_offset_1) statement_start_offset,
MAX(statement_end_offset_1) statement_end_offset,
COUNT(*) querycount,
COUNT (DISTINCT query_plan_hash) queryplanhashcount,
MAX(plan_handle_1) plan_handle,
MIN(creation_time) earliest_creation_time,
SUM(execution_count) execution_count,
SUM(total_elapsed_time) total_elapsed_time,
min(min_elapsed_time) min_elapsed_time,
max(max_elapsed_time) max_elapsed_time,
SUM(total_elapsed_time)/SUM(execution_count) average_elapsed_time,
SUM(total_worker_time) total_CPU_time,
min(min_worker_time) min_CPU_time,
max(max_worker_time) max_CPU_time,
SUM(total_worker_time)/SUM(execution_count) average_CPU_time,
SUM(total_logical_reads) total_logical_reads,
min(min_logical_reads) min_logical_reads,
max(max_logical_reads) max_logical_reads,
SUM(total_logical_reads)/SUM(execution_count) average_logical_reads,
SUM(total_physical_reads) total_physical_reads,
min(min_physical_reads) min_physical_reads,
max(max_physical_reads) max_physical_reads,
SUM(total_physical_reads)/SUM(execution_count) average_physical_reads,
SUM(total_logical_writes) total_logical_writes,
min(min_logical_writes) min_logical_writes,
max(max_logical_writes) max_logical_writes,
SUM(total_logical_writes)/SUM(execution_count) average_logical_writes,
SUM(total_clr_time) total_clr_time,
SUM(total_clr_time)/SUM(execution_count) average_clr_time,
min(min_clr_time) min_clr_time,
max(max_clr_time) max_clr_time,
MAX(plan_generation_num) max_plan_generation_num
FROM (
-- Implement my own FIRST aggregate to get consistent values for sql_handle, start/end offsets of
-- an arbitrary first row for a given query_hash
SELECT
CASE when t.rownum = 1 THEN plan_handle ELSE NULL END as plan_handle_1,
CASE WHEN t.rownum = 1 THEN sql_handle ELSE NULL END AS sql_handle_1,
CASE WHEN t.rownum = 1 THEN statement_start_offset ELSE NULL END AS statement_start_offset_1,
CASE WHEN t.rownum = 1 THEN statement_end_offset ELSE NULL END AS statement_end_offset_1,
*
FROM (SELECT row_number() OVER (PARTITION BY query_hash ORDER BY sql_handle) AS rownum, *
FROM sys.dm_exec_query_stats) AS t) AS t2
GROUP BY query_hash
) AS s
WHERE s.charted_value > 0
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
where query_rank <= 20
order by charted_value desc
',@params=N'@OrderBy_Criteria NVarChar(max)',@OrderBy_Criteria=N'CPU'============================
SQL Server Management Studio version 17.2以下的版本才需要執行以下安裝步驟
Microsoft® SQL Server® 2012 Performance Dashboard Reports
https://www.microsoft.com/en-us/download/details.aspx?id=29063
Supported Operating System
Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2
Works with the following SQL Server versions: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014
1.安裝
2.設定
Getting Started With the Performance Dashboard Reports
1. In each SQL Server instance, run the script %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard\setup.sql
2. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory and open the performance_dashboard_main.rdl file.
Default Path: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl
Default Path:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl
3.使用
安裝完成之後，在上一個步驟按下run，就會開啟Performance Dashboard Reports
https://www.microsoft.com/en-us/download/details.aspx?id=29063
Supported Operating System
Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2
Works with the following SQL Server versions: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014
1.安裝
2.設定
Getting Started With the Performance Dashboard Reports
1. In each SQL Server instance, run the script %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard\setup.sql
2. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory and open the performance_dashboard_main.rdl file.
Default Path: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl
Default Path:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl
3.使用
安裝完成之後，在上一個步驟按下run，就會開啟Performance Dashboard Reports
沒有留言:
張貼留言