Post reply

Stored Procedure history

  • May 31, 2017 at 9:39 am

    #326216

    Hi All
    Is there anyway to get the stats (logical reads, physical reads, elapsed time etc) of the last perhaps 20 executions of a particular procedure?

    Thanks
    SQLSACT

  • May 31, 2017 at 9:50 am

    #1944757

    Not without capturing them as you go, no, or maybe with the Query Store in SQL Server 2016 and later.  The best you can do is get the aggregates (max (I think), total and most recent) from the plan cache.

    John

  • May 31, 2017 at 10:11 am

    #1944761

    Have you tried using sys.dm_exec_procedure_stats DMV?

  • June 1, 2017 at 1:42 am

    #1944855

    John Mitchell-245523 - Wednesday, May 31, 2017 9:50 AM

    Not without capturing them as you go, no, or maybe with the Query Store in SQL Server 2016 and later.  The best you can do is get the aggregates (max (I think), total and most recent) from the plan cache.

    John

    Thanks - Yeah, I thought so.

  • June 1, 2017 at 1:43 am

    #1944856

    shahm10 - Wednesday, May 31, 2017 10:11 AM

    Have you tried using sys.dm_exec_procedure_stats DMV?

    Yep - That gives cumulative stats for stored procedures - I'm looking for stats for each of the last 20 executions of a particular stored procedure. 

    Thanks

  • June 1, 2017 at 2:13 am

    #1944857

    SQLSACT - Wednesday, May 31, 2017 9:39 AM

    Hi All
    Is there anyway to get the stats (logical reads, physical reads, elapsed time etc) of the last perhaps 20 executions of a particular procedure?

    Thanks
    SQLSACT

    Not without using Extended Events to capture them.

    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

  • June 1, 2017 at 2:16 am

    #1944859

    John Mitchell-245523 - Wednesday, May 31, 2017 9:50 AM

    Not without capturing them as you go, no, or maybe with the Query Store in SQL Server 2016 and later.  The best you can do is get the aggregates (max (I think), total and most recent) from the plan cache.

    John

    Query Store also aggregates.

    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

  • June 2, 2017 at 6:58 am

    #1945086

    Thanks all for the help!

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

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