Adding logging statements at start and end of all stored procedures

Question

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.

Answer 1

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.

Answer 2

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.