CODEWITHSUNDEEP
sqlsql-server
Nitesh Kumar
Nitesh Kumar

Reputation: 885

How to Trace Procedure execution time?

I have some procedures which are not optimized . I want to analyse that whether these procedures get optimised or not , as there are only not bulk data to check.

I want to put a sql query into all the procedure which will insert in to one table that what is exact time taken by the above query to execute. I will create a table in which my procedure will insert the query execution time.

Is there any way that I can do this.

Upvotes: 2

Views: 2892

Answers (4)

stb
stb

Reputation: 3513

If you want to integrate it in your procedure, set a variable which stores the execution start time at the beginning, and a subtract it with the current time at the end of the procedure.

/* Start of your procedure */
Declare @startTime datetime = GETDATE()
Declare @duration varchar

/* Your procedure */
...

/* End of your procedure */
Set @duration = CONVERT(VARCHAR(8),GETDATE() - @startTime,108)
Insert into statisticTable 
Values ('procedureName', @duration)

Upvotes: 3

Gulli Meel
Gulli Meel

Reputation: 891

Why would you want to do that?There are plenty of tools like DMV's ,trace ,exevents etc. You will add to overhead to your proc if you put that kind of code. For procedure best is dm_procedure_stats DMV but is availble in 2008 R2 and above only.Use the dm_exec_query_stats for older versions. These will give you much better stats about the time,logical io,cputime, and physical IO's etc.

Upvotes: -1

Pablo Claus
Pablo Claus

Reputation: 5920

Did you try to use the SQL Profiler?

In Trace Properties you can can define Start Time and End time of Stored Procedures. It is a very powerful tool.

Upvotes: 1

Akash KC
Akash KC

Reputation: 16310

You can check above mentioned stuffs using Dynamic Management Views....

If you are using old version of SQL SERVER, use SQL Server Profiler....

Upvotes: 0

Related Questions