Post reply

SP execution time.

  • March 19, 2013 at 9:14 am

    #275359

    How can I find how long a particular SP runtime?

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • March 19, 2013 at 9:17 am

    #1598492

    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

    #1598494

    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

    #1598495

    Whoops mis read you post

  • March 19, 2013 at 9:25 am

    #1598496

    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

  • March 19, 2013 at 9:25 am

    #1598497

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

  • March 19, 2013 at 9:28 am

    #1598499

    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

    #1598517

    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

    #1598533

    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) A socialist is someone who will give you the shirt off *someone else's* back.

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

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