March 19, 2013 at 9:14 am
How can I find how long a particular SP runtime?
SueTons.
Regards,
SQLisAwe5oMe.
March 19, 2013 at 9:17 am
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'
March 19, 2013 at 9:22 am
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'
March 19, 2013 at 9:23 am
Whoops mis read you post
March 19, 2013 at 9:25 am
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
March 19, 2013 at 9:25 am
Still if you select the columns you need my query will do it for you.
March 19, 2013 at 9:28 am
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.
March 19, 2013 at 10:04 am
Server-side Trace or Extended Events session.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 19, 2013 at 10:48 am
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".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy