Stored Procedure history

  • 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 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

  • Have you tried using sys.dm_exec_procedure_stats DMV?

  • 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.

  • 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

  • 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
  • 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
  • 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