InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:09:13 GMT20http://www.sqlservercentral.com/Forums/Topic723890-146-1.aspxBelow 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_textfrom (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 qtwhere 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_textfrom (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 qtwhere 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_textfrom (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 qtwhere 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_textfrom (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 qtwhere 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_textfrom (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 qtwhere 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_textfrom (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 qtwhere 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_msfrom sys.dm_os_wait_statswhere 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_identifiedfrom (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_idwhere 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_msfrom 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.Thu, 28 May 2009 00:56:52 GMTGURSETHIhttp://www.sqlservercentral.com/Forums/Topic723890-146-1.aspxI require urgently ....Thu, 28 May 2009 00:12:34 GMTParesh Prajapatihttp://www.sqlservercentral.com/Forums/Topic723890-146-1.aspxI want Script/Query to generate SQL server Health , DashBorad and Status report. And send to client everyday by sql server.Is there any idea?Wed, 27 May 2009 03:19:56 GMTParesh Prajapati