Search code examples
sql-serverunit-testingtsqlt

Assert MSSQL stored procedure execution time with tSQLt

I want to create SQL unit tests to measure the execution time of my stored procedures. Is there a way to assert the duration time of each execution?

Solution i am using finally:

--run my stored procedure

exec mySP

--Get the metrics 

  SELECT d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',   
    d.cached_time, d.last_execution_time, d.total_elapsed_time,  
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_procedure_stats AS d  
where OBJECT_NAME(object_id, database_id) = 'mySP'
ORDER BY [total_worker_time] DESC;

--Rum my assertions based on last_elapsed_time and avg_elapsed_time

Solution

  • You can look at sys.dm_exec_procedure_stats available from SQLServer 2008..this DMV provides some usefull info like

    enter image description here

    You also can look at SET STATISTICS TIME ON as well

    set statistics time on
exec usp_test
set statistics time off

    You can see time in messages tab like below

    SQL Server Execution Times:
   CPU time = 171 ms,  elapsed time = 5060 ms.