SQL Server procedure optimization testing pattern improvement -

i've been doing sql server procedures optimization lately , looking testing pattern (time , result wise). i've came solution far:

set nocount on; ---------------------------------------------------------------------------------------------------------------- -- procedures data , performance testing pattern ---------------------------------------------------------------------------------------------------------------- -- prepare test queries (most taken logs.proceduretracedata (datauk/dataus servers) -- procedures should insert records temporary table, can compare results using except -- if result set columns fixed (i.e. no dynamic sql used), can create temporary tables inside script --  , insert records in them comparison , truncate them @ end of loop. --  example here: http://stackoverflow.com/a/654418/3680098 -- if there're data discrepancies or record counts different, displayed in tracelog table ---------------------------------------------------------------------------------------------------------------- -- create own tracelog table keep records ---------------------------------------------------------------------------------------------------------------- /* create table temporary._eb_tracelog (     id int not null identity(1, 1) constraint pk_temporary_eb_tracelog_id primary key     , currentexecutiontime int     , tempexecutiontime int     , currentexecutionresultscount int     , tempexecutionresultscount int     , isdifferent bit constraint df_temporary_eb_tracelog_isdifferent default 0 not null     , timediff currentexecutiontime - tempexecutiontime     , percentagediff cast(((cast(currentexecutiontime decimal)/ cast(tempexecutiontime decimal)) * 100 - 100) decimal(10, 2))     , textdata nvarchar(max) );  select * temporary._eb_tracelog;  truncate table temporary._eb_tracelog; */  insert temporary._eb_tracelog (textdata) select textdata temporary._eb_getdata_timeouts except select textdata temporary._eb_tracelog;  declare @counter int;  select @counter = min(id) temporary._eb_tracelog currentexecutiontime null     or tempexecutiontime null     or currentexecutionresultscount null     or tempexecutionresultscount null;  while @counter <= (select max(id) temporary._eb_tracelog) begin     declare @sqlstringcurr nvarchar(max);     declare @sqlstringtemp nvarchar(max);     declare @starttime datetime2;     select @sqlstringcurr = replace(textdata, 'dbo.getdata', 'temporary._eb_getdata_orig')         , @sqlstringtemp = replace(textdata, 'dbo.getdata', 'temporary._eb_getdata_mod')     temporary._eb_tracelog     id = @counter;      ----------------------------------------------------------------------------------------------------------------     -- drop temporary tables in script, these numbers don't figure in sp execution time     ----------------------------------------------------------------------------------------------------------------     if object_id(n'temporary._eb_test_orig') not null         drop table temporary._eb_test_orig;      if object_id(n'temporary._eb_test_mod') not null         drop table temporary._eb_test_mod;      ----------------------------------------------------------------------------------------------------------------     -- actual testing     ----------------------------------------------------------------------------------------------------------------     -- take time snapshot , execute original procedure, inserts records temporary table     -- when done - measurements updated on tracelog table     ----------------------------------------------------------------------------------------------------------------     select @starttime = current_timestamp;     execute sp_executesql @sqlstringcurr;      update t     set t.currentexecutiontime = datediff(millisecond, @starttime, current_timestamp)     temporary._eb_tracelog t     t.id = @counter;      ----------------------------------------------------------------------------------------------------------------     -- take time snapshot , execute optimized procedure, inserts records temporary table     -- when done - measurements updated on tracelog table     ----------------------------------------------------------------------------------------------------------------     select @starttime = current_timestamp;     execute sp_executesql @sqlstringtemp;      update t     set t.tempexecutiontime = datediff(millisecond, @starttime, current_timestamp)     temporary._eb_tracelog t     t.id = @counter;      ----------------------------------------------------------------------------------------------------------------     -- check if there data discrepancies     -- if there any, set isdifferent 1, can find root cause     ----------------------------------------------------------------------------------------------------------------     if exists (select * temporary._eb_test_mod except select * temporary._eb_test_orig)     or exists (select * temporary._eb_test_orig except select * temporary._eb_test_mod)     begin         update t         set t.isdifferent = 1         temporary._eb_tracelog t         t.id = @counter;     end      ----------------------------------------------------------------------------------------------------------------     -- update record counts each execution     -- can check if there aren't different record counts tho results same     -- except clause removes duplicates when doing checks     ----------------------------------------------------------------------------------------------------------------     update t     set t.currentexecutionresultscount = (select count(*) temporary._eb_test_orig)         , t.tempexecutionresultscount = (select count(*) temporary._eb_test_mod)     temporary._eb_tracelog t     t.id = @counter;      ----------------------------------------------------------------------------------------------------------------     -- print iteration number , proceed on next 1     ----------------------------------------------------------------------------------------------------------------     print @counter;     set @counter += 1; end  select * temporary._eb_tracelog; 

this works quite far, include io , time statistics in each iteration. possible?

i know can using:

set statistics io on; set statistics time on; 

but there way grab summed values , put them in tracelog table?

and on top of that, there doesn't make sense in piece of code?

thanks

you can use query

select total_elapsed_time sys.dm_exec_query_stats  sql_handle in (select most_recent_sql_handle sys.dm_exec_connections cross apply sys.dm_exec_sql_text(most_recent_sql_handle) session_id = (@@spid))