SQL Server Health , DashBorad and Status report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL Server Health , DashBorad and Status...

SQL Server Health , DashBorad and Status report

Rate Topic

Display Mode

Topic Options

Author

Message

Paresh Prajapati

Posted Wednesday, May 27, 2009 3:19 AM

Old Hand

Group: General Forum Members
Last Login: Friday, August 31, 2012 6:08 AM
Points: 322, Visits: 459

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?

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj

Post #723890

Paresh Prajapati

Posted Thursday, May 28, 2009 12:12 AM

Old Hand

Group: General Forum Members
Last Login: Friday, August 31, 2012 6:08 AM
Points: 322, Visits: 459

I require urgently ....

_____________________________________________________________________________________________________________
Paresh Prajapati

+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj

Post #724820

GURSETHI

Posted Thursday, May 28, 2009 12:56 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:57 PM
Points: 92, Visits: 280

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) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N''...'' end as query_text
from (select s.*, row_number() over(order by charted_value desc, last_execution_time desc) as query_rank from
(select *,
CASE @OrderBy_Criteria
WHEN ''Logical Reads'' then total_logical_reads
WHEN ''Physical Reads'' then total_physical_reads
WHEN ''Logical Writes'' then total_logical_writes
WHEN ''CPU'' then total_worker_time / 1000
WHEN ''Duration'' then total_elapsed_time / 1000
WHEN ''CLR Time'' then total_clr_time / 1000
END as charted_value
from sys.dm_exec_query_stats) as s where s.charted_value > 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) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N''...'' end as query_text
from (select s.*, row_number() over(order by charted_value desc, last_execution_time desc) as query_rank from
(select *,
CASE @OrderBy_Criteria
WHEN ''Logical Reads'' then total_logical_reads
WHEN ''Physical Reads'' then total_physical_reads
WHEN ''Logical Writes'' then total_logical_writes
WHEN ''CPU'' then total_worker_time / 1000
WHEN ''Duration'' then total_elapsed_time / 1000
WHEN ''CLR Time'' then total_clr_time / 1000
END as charted_value
from sys.dm_exec_query_stats) as s where s.charted_value > 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) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N''...'' end as query_text
from (select s.*, row_number() over(order by charted_value desc, last_execution_time desc) as query_rank from
(select *,
CASE @OrderBy_Criteria
WHEN ''Logical Reads'' then total_logical_reads
WHEN ''Physical Reads'' then total_physical_reads
WHEN ''Logical Writes'' then total_logical_writes
WHEN ''CPU'' then total_worker_time / 1000
WHEN ''Duration'' then total_elapsed_time / 1000
WHEN ''CLR Time'' then total_clr_time / 1000
END as charted_value
from sys.dm_exec_query_stats) as s where s.charted_value > 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) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N''...'' end as query_text
from (select s.*, row_number() over(order by charted_value desc, last_execution_time desc) as query_rank from
(select *,
CASE @OrderBy_Criteria
WHEN ''Logical Reads'' then total_logical_reads
WHEN ''Physical Reads'' then total_physical_reads
WHEN ''Logical Writes'' then total_logical_writes
WHEN ''CPU'' then total_worker_time / 1000
WHEN ''Duration'' then total_elapsed_time / 1000
WHEN ''CLR Time'' then total_clr_time / 1000
END as charted_value
from sys.dm_exec_query_stats) as s where s.charted_value > 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) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N''...'' end as query_text
from (select s.*, row_number() over(order by charted_value desc, last_execution_time desc) as query_rank from
(select *,
CASE @OrderBy_Criteria
WHEN ''Logical Reads'' then total_logical_reads
WHEN ''Physical Reads'' then total_physical_reads
WHEN ''Logical Writes'' then total_logical_writes
WHEN ''CPU'' then total_worker_time / 1000
WHEN ''Duration'' then total_elapsed_time / 1000
WHEN ''CLR Time'' then total_clr_time / 1000
END as charted_value
from sys.dm_exec_query_stats) as s where s.charted_value > 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) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N''...'' end as query_text
from (select s.*, row_number() over(order by charted_value desc, last_execution_time desc) as query_rank from
(select *,
CASE @OrderBy_Criteria
WHEN ''Logical Reads'' then total_logical_reads
WHEN ''Physical Reads'' then total_physical_reads
WHEN ''Logical Writes'' then total_logical_writes
WHEN ''CPU'' then total_worker_time / 1000
WHEN ''Duration'' then total_elapsed_time / 1000
WHEN ''CLR Time'' then total_clr_time / 1000
END as charted_value
from sys.dm_exec_query_stats) as s where s.charted_value > 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'CLR Time'

TOP Wait Category
****************

exec sp_executesql @stmt=N'select
wait_type,
msdb.MS_PerfDashboard.fn_WaitTypeCategory(wait_type) as wait_category,
waiting_tasks_count as num_waits,
wait_time_ms as wait_time,
max_wait_time_ms
from sys.dm_os_wait_stats
where waiting_tasks_count > 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.

Post #724833

« Prev Topic | Next Topic »

Permissions