CODEWITHSUNDEEP
sqlsql-serverstored-procedures
developer82
developer82

Reputation: 13713

SQL measure stored procedure execution time

I would like to measure the time it take to execute a Stored Procedure in SQL Server. But instead of just measuring the entire stored procedure execution I would also like to measure the time each operation inside the stored procedure takes, so I could find bottlenecks.

In c# to accomplish such a thing when testing code execution time I would save the date before execution starts and when execution ends I would print the TimeSpan object that is given to me by subtracting the start time from the current time (after execution).

I was wondering how I could achieve such thing with SQL Server inside a stored procedure where I could print the time span I measure between operation within the stored procedure.

Thanks

Upvotes: 9

Views: 14432

Answers (3)

ColdMountain
ColdMountain

Reputation: 71

What worked for me was the suggestion posted here: https://www.codeproject.com/tips/1151457/a-simple-method-to-measure-execution-time-in-sql-s

Declare @StartTime DateTime2

SET @StartTime = SysUTCDateTime()

-- SQL Execution

Print 'Time taken was ' + cast(DateDiff(millisecond, @StartTime, SysUTCDateTime()) as varchar) + 'ms'

SET @StartTime = SysUTCDateTime()

-- Second SQL Execution

Print 'Time taken was ' + cast(DateDiff(millisecond, @StartTime, SysUTCDateTime()) as varchar) + 'ms'

Upvotes: 5

Raj
Raj

Reputation: 10843

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO

EXECUTE <yourSP>;

GO

Upvotes: 6

gbjbaanb
gbjbaanb

Reputation: 52659

You need the actual execution plan. You can see these from running the SQL query in SQL Management studio with the right button pressed (they'll appear as a tab in the results window). This will show you what parts of the sproc is being run and the time taken up by each bit - just what you want to tune.

Upvotes: 0

Related Questions