SP execution time.

Question

Post reply

SP execution time.

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
March 19, 2013 at 9:14 am

#275359

How can I find how long a particular SP runtime?
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

david.alcock SSCertifiable Points: 6021 More actions · Answer 1

SET STATISTICS TIME ON, run the SP and check the messages tab.

Alternatively this view sys.dm_exec_query_stats has elapsed time columns.

'Only he who wanders finds new paths'

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 2

SELECT o.NAME, ps.execution_count FROM sys.dm_exec_procedure_stats ps

INNER JOIN sys.objects o ON ps.object_id = o.object_id

WHERE o.name = 'sprocname'

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 3

Bobby Glover

SSCarpal Tunnel

Points: 4143

March 19, 2013 at 9:23 am

#1598495

Whoops mis read you post

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Profiler or extended events work well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 5

Still if you select the columns you need my query will do it for you.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 6

Thanks for your reply.

If I have to monitor the performance/timing of a particular SP, can I automate this? If so, how?

SueTons.

Regards,
SQLisAwe5oMe.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Server-side Trace or Extended Events session.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

ScottPletcher SSC Guru Points: 101105 More actions · Answer 8

Or, depending on your specific needs, you could add your own logging/pseudotrace code to the proc itself, and trigger it with an optional parameter:

CREATE PROCEDURE dbo.proc_name

@...,

@do_trace bit = 0,

@debug tinyint = 0

AS

SET NOCOUNT ON

--...<other code>

DECLARE @GUID uniqueidentifier

SET @GUID = NEWID()

--...<other code>

IF @do_trace = 1

INSERT INTO dbo.pseudotrace_table ( ... )

SELECT DB_ID(), @@PROCID, @GUID, 1, GETDATE(), ...

--...rest.of.proc.code.here...

--INSERT INTO dbo.pseudotrace_table SELECT @GUID, 100, ... --log intermediate step(s) if desired

IF @do_trace = 1

INSERT INTO dbo.pseudotrace_table (... )

SELECT DB_ID(), @@PROCID, @GUID, 9999, GETDATE(), ...

RETURN

GO

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".