1

I would like to capture stored procedure execution details which would require altering existing procedures to perform an INSERT at the start and an UPDATE at the end.

I recall finding a blog article some time ago that demonstrated adding statements to the start/end of stored procedures but cannot find it.

How could I make these changes programmatically either in C# or T-SQL ensuring the original stored procedure definition works as expected?

Edit: As a minimum I want to capture the user executing, the stored procedure name, start and end times. Parameters and stored procedure definition are optional.

2 Answers 2

2

I would advise against programmatically modifying all the stored procedures in a database like that. Trying to ensure no edge-case causes a modification to be made erroneously will prove quite difficult.

Instead of modifying all the stored procedures, why not use Extended Events to capture the information you're interested in. This will

  1. Not impact performance nearly as much
  2. Allow you to enable/disable monitoring as required, without modifying any stored procedures.
  3. Not affect execution plans for the stored procedures.

SQLTips has a good example of several ways to capture stored procedure execution history without modifying the stored procedures.

1

Extended Events are great to learn, but in order to capture every single relevant performance metrics, you're looking at half a dozen to a dozen sessions.

  • Query plans (which are incredibly expensive to capture)
  • Wait Stats (also expensive unless you filter heavily)
  • Locking/deadlocks (not bad, but can be hard to correlate)
  • Server level metrics (this could be four different sessions on its own)

Since what you're looking for is pretty involved, I'm going to skip suggesting using free tools and logging them to a table. Just spend a couple thousand bucks on a mature, proven monitoring tool that does all this for you. You get all the metrics, plus a dashboard, reports, historical data, baselines, etc.

Lots of smart people have spent lots of time making their tools not suck. They don't make money if they drag your server down while they try to tell you what's wrong with it.

My personal favorite is SentryOne (I have no monetary relationship with them), but feel to give others a shot as well.

2
  • I have updated my question with the minimum logging requirements
    – mhep
    Commented Apr 5, 2017 at 18:40
  • @mheptinstall that doesn't change my answer at all. Commented Apr 5, 2017 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.