CODEWITHSUNDEEP
sqlsql-serversql-server-2005stored-procedures
Sachin
Sachin

Reputation: 267

How to calculate SQL server stored procedure execution time

I have a stored procedure. I want to check how much time it takes to execute. Please note that I don't have access to profiler. I am using SQL server 2005

I tried using the following batch query

SELECT CONVERT( VARCHAR(24), GETDATE(), 121)
EXEC cif_query_trans_by_card_Final2 '0060F423F28A6095AD070CEFFD7E81D245D7D0CD7B', '11223345'
SELECT CONVERT( VARCHAR(24), GETDATE(), 121)

But I am getting same value in both timestamp as 2014-12-30 18:09:52.653

Any help will be greatly appreciated.

Thanks in advance.

Upvotes: 1

Views: 13971

Answers (3)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12575

Use like below:

set statistics time on

-- your query

set statistics time off

Upvotes: 1

gvee
gvee

Reputation: 17171

SET STATISTICS TIME ON

http://msdn.microsoft.com/en-us/library/ms190287.aspx

When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed.

The setting of SET STATISTICS TIME is set at execute or run time and not at parse time.

  1. Open a new query window

  2. Run the following SQL SET STATISTICS TIME ON

  3. In the same window, now run your stored procedure

  4. Switch to the Messages window to see the resultant statistics

Example:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(65536 row(s) affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 272 ms.

Upvotes: 4

user_0
user_0

Reputation: 3363

You need to place 2 GO:

SELECT CONVERT( VARCHAR(24), GETDATE(), 121)
GO
EXEC cif_query_trans_by_card_Final2 '0060F423F28A6095AD070CEFFD7E81D245D7D0CD7B', '11223345'
GO
SELECT CONVERT( VARCHAR(24), GETDATE(), 121)

But: even without your function you will se a delay of few milliseconds.

Upvotes: 0

Related Questions