How to reset performance data on reports?

Last Post 24 Mar 2007 01:00 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
MrRobot
New Member
New Member

--
07 Mar 2007 09:05 AM
Hey guys,

I'm looking for a way to reset performance statistics data on the summary / reports view for servers (like object execution statistics) on management studio (other than restarting the service, I need to do it while the db is online)

I've modified some stored procedures. I need to reset the performance statistics, so I'll be able to see the totals for all procedures in the same time period with right percentages.
SQLUSA
New Member
New Member

--
07 Mar 2007 10:52 PM
That data is coming from dynamic management views/functions and systems views. See below. I don't beleive that there is an sp to reset it, unless unpublished.

Kalman Toth
SQLUSA: http://www.sqlusa.com
=================================================================

begin try
use [AdventureWorks] ;
declare @dbid int;
set @dbid = db_id();
declare @grand_total_worker_time float ;
declare @grand_total_IO float ;
declare @sql_handle_convert_table table(
row_id int identity
, schema_name nvarchar(128) collate database_default
, t_sql_handle varbinary(64)
, t_display_option varchar(140) collate database_default
, t_display_optionIO varchar(140) collate database_default
, t_sql_handle_text varchar(140) collate database_default
, t_SPRank int
, t_obj_name sysname collate database_default null
, t_obj_type sysname collate database_default null
, t_SQLStatement varchar(max) collate database_default
, t_execution_count int
, t_plan_generation_num int
, t_last_execution_time datetime
, t_avg_worker_time float
, t_total_worker_time float
, t_last_worker_time float
, t_min_worker_time float
, t_max_worker_time float
, t_avg_logical_reads float
, t_total_logical_reads float
, t_last_logical_reads float
, t_min_logical_reads float
, t_max_logical_reads float
, t_avg_logical_writes float
, t_total_logical_writes float
, t_last_logical_writes float
, t_min_logical_writes float
, t_max_logical_writes float
, t_avg_logical_IO float
, t_total_logical_IO float
, t_last_logical_IO float
, t_min_logical_IO float
, t_max_logical_IO float
);
declare @objects table (
obj_rank int
, total_cpu bigint
, total_reads bigint
, total_writes bigint
, total_io bigint
, avg_cpu bigint
, avg_reads bigint
, avg_writes bigint
, avg_io bigint
, cpu_rank int
, total_cpu_rank int
, read_rank int
, write_rank int
, io_rank int
);

insert into @sql_handle_convert_table
Select case when sch.name is null then '' else '['+sch.name+'].' end as schema_name
, sql_handle
, sql_handle as chart_display_option
, sql_handle as chart_display_optionIO
, master.dbo.fn_varbintohexstr(sql_handle)
, dense_rank() over (order by s2.objectid) as SPRank
, s3.name as [Obj Name]
, s3.type as [Obj Type]
, (select top 1 substring(text,(s1.statement_start_offset+2)/2, (case when
s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2 else
s1.statement_end_offset end - s1.statement_start_offset) /2 ) from
sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement]
, execution_count
, plan_generation_num
, last_execution_time
, ((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time]
, total_worker_time/1000.0
, last_worker_time/1000.0
, min_worker_time/1000.0
, max_worker_time/1000.0
, ((total_logical_reads+0.0)/execution_count) as [avg_logical_reads]
, total_logical_reads
, last_logical_reads
, min_logical_reads
, max_logical_reads
, ((total_logical_writes+0.0)/execution_count) as [avg_logical_writes]
, total_logical_writes
, last_logical_writes
, min_logical_writes
, max_logical_writes
, ((total_logical_writes+0.0)/execution_count + (total_logical_reads+0.0)/executio
MrRobot
New Member
New Member

--
23 Mar 2007 05:06 PM
Thanks guys, I've solved this one by cleaning sp cache of the db. Also I've recently found this:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
SQLUSA
New Member
New Member

--
24 Mar 2007 01:00 AM
What does it reset exactly?
You are not authorized to post a reply.

Acceptable Use Policy