How can I tell what the parameter values are for a problem stored procedure?

I have a stored procedure that causes blocking on my SQL server database. Whenever it does block for more than X amount of seconds we get notified with what query is being run, and it looks similar to below.

   CREATE PROC [dbo].[sp_problemprocedure] (
    @ord开发者_如何学运维erid INT
    --procedure code

How can I tell what the value is for @orderid? I'd like to know the value because this procedure will run 100+ times a day but only cause blocking a handful of times, and if we can find some sort of pattern between the order id's maybe I'd be able to track down the problem.

The procedure is being called from a .NET application if that helps.

---

Have you tried printing it from inside the procedure?

http://msdn.microsoft.com/en-us/library/ms176047.aspx

---

If it's being called from a .NET application you could easily log out the parameter being passed from the .net app, but if you don't have access, also you can use SQL Server profiling. Filters can be set on the command type i.e. proc only as well as the database that is being hit otherwise you will be overwhelmed with all the information a profile can produce.

Link: Using Sql server profiler

---

• rename the procedure
• create a logging table
• create a new one (same signature/params) which calls the original but first logs the params and starting timestamp and logs after the call finishes the end timestamp
• create a synonym for this new proc with the name of the original

Now you have a log for all calls made by whatever app...

You can disbale/enable the logging anytime by simply redefining the synonym to point to the logging wrapper or to the original...

---

The easiest way would be to run a profiler trace. You'll want to capture calls to the stored procedure.

Really though, that is only going to tell you part of the story. Personally I would start with the code. Try and batch big updates into smaller batches. Try and avoid long-running explicit transactions if they're not necessary. Look at your triggers (if any) and cascading Foreign keys and make sure those are efficient.

---

easiest way is to do the following:

• 1) in .NET, grab the date-time just before running the procedure
• 2) in .Net, after the procedure is complete grab the date-time
• 3) in .NET, do some date-time math, and if it is "slow", write to a file (log) those start and end date-times, user info, all the the parameters, etc.