May 27, 2009 at 3:19 am
I want Script/Query to generate SQL server Health , DashBorad and Status report. And send to client everyday by sql server.
Is there any idea?
May 28, 2009 at 12:56 am
Below Scripts are captured by running Profiler at the back end and then by running SQL 2005 Performance Dash Board Tool.
Top CPU
**********
exec sp_executesql @stmt=N'select
query_rank,
charted_value,
master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(plan_handle) as plan_handle,
statement_start_offset,
statement_end_offset,
creation_time,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
case when LEN(qt.query_text) 0) as qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
where qs.query_rank <= 20 -- return only top 20 entries',@params=N'@OrderBy_Criteria NVarChar(max)',@OrderBy_Criteria=N'CPU'
TOP Duration
***********
exec sp_executesql @stmt=N'select
query_rank,
charted_value,
master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(plan_handle) as plan_handle,
statement_start_offset,
statement_end_offset,
creation_time,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
case when LEN(qt.query_text) 0) as qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
where qs.query_rank <= 20 -- return only top 20 entries',@params=N'@OrderBy_Criteria NVarChar(max)',@OrderBy_Criteria=N'Duration'
Logical Reads
***********
exec sp_executesql @stmt=N'select
query_rank,
charted_value,
master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(plan_handle) as plan_handle,
statement_start_offset,
statement_end_offset,
creation_time,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
case when LEN(qt.query_text) 0) as qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
where qs.query_rank <= 20 -- return only top 20 entries',@params=N'@OrderBy_Criteria NVarChar(max)',@OrderBy_Criteria=N'Logical Reads'
Physical Reads
************
exec sp_executesql @stmt=N'select
query_rank,
charted_value,
master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(plan_handle) as plan_handle,
statement_start_offset,
statement_end_offset,
creation_time,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
case when LEN(qt.query_text) 0) as qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
where qs.query_rank <= 20 -- return only top 20 entries',@params=N'@OrderBy_Criteria NVarChar(max)',@OrderBy_Criteria=N'Physical Reads'
Logical Writes
************
exec sp_executesql @stmt=N'select
query_rank,
charted_value,
master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(plan_handle) as plan_handle,
statement_start_offset,
statement_end_offset,
creation_time,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
case when LEN(qt.query_text) 0) as qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
where qs.query_rank <= 20 -- return only top 20 entries',@params=N'@OrderBy_Criteria NVarChar(max)',@OrderBy_Criteria=N'Logical Writes'
CLR TIME
**********
exec sp_executesql @stmt=N'select
query_rank,
charted_value,
master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(plan_handle) as plan_handle,
statement_start_offset,
statement_end_offset,
creation_time,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_clr_time,
last_clr_time,
min_clr_time,
max_clr_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time,
case when LEN(qt.query_text) 0) as qs
cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt
where qs.query_rank 0',@params=N''
IO Statistics
***********
exec sp_executesql @stmt=N'select db_name(d.database_id) as database_name,
quotename(object_schema_name(d.object_id, d.database_id)) + N''.'' + quotename(object_name(d.object_id, d.database_id)) as object_name,
d.database_id,
d.object_id,
d.page_io_latch_wait_count,
d.page_io_latch_wait_in_ms,
d.range_scans,
d.index_lookups,
case when mid.database_id is null then ''N'' else ''Y'' end as missing_index_identified
from (select
database_id,
object_id,
row_number() over (partition by database_id order by sum(page_io_latch_wait_in_ms) desc) as row_number,
sum(page_io_latch_wait_count) as page_io_latch_wait_count,
sum(page_io_latch_wait_in_ms) as page_io_latch_wait_in_ms,
sum(range_scan_count) as range_scans,
sum(singleton_lookup_count) as index_lookups
from sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
where page_io_latch_wait_count > 0
group by database_id, object_id ) as d
left join (select distinct database_id, object_id from sys.dm_db_missing_index_details) as mid on mid.database_id = d.database_id and mid.object_id = d.object_id
where d.row_number <= 20',@params=N''
exec sp_executesql @stmt=N'select
m.database_id,
db_name(m.database_id) as database_name,
m.file_id,
m.name as file_name,
m.physical_name,
m.type_desc,
fs.num_of_reads,
fs.num_of_bytes_read,
fs.io_stall_read_ms,
fs.num_of_writes,
fs.num_of_bytes_written,
fs.io_stall_write_ms
from sys.dm_io_virtual_file_stats(NULL, NULL) fs
join sys.master_files m on fs.database_id = m.database_id and fs.file_id = m.file_id',@params=N''
I Hope this should help you out. Other wise you can simply Export Individual Report.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply