TSQL Dynamically determine parameter list for SP/Function

Question

I want to write a generic logging snip-it into a collection of stored procedures. I'm writing this to have a quantitative measure of our front-user user experience as I know which SP's are used by the front-end software and how they are used. I'd like to use this to gather a base-line before we commence performance tunning and afterward to show the outcome of tunning.

I can dynamically pull the object name from @@PROCID, but I've been unable to determine all parameters passed and their values. Anyone know if this is possible?

EDIT: marking my response as the answer to close this question. Appears extended events are the least intrusive item to performance, however i'm not sure if there is any substantial difference between minimal profiling and extended events. Perhaps something for a rainy day.

Answer 1

I don't know how to do this off the top of my head, but I would consider running a trace instead if I were you. You can use SQL Server Profiler to gather only information for the stored procedures that you specify (using filters). You can send the output to a table and then query the results to your heart's content. The output can include IO information, what parameters were passed, the client userid and machine, and much much more.

After running the trace you can aggregate the results into reports that would show how many times a procedure was called, what parameters were used, etc...

Here is a link that might help:

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

Answer 2

I can get the details of the parameters taken by the proc without parsing its text (at least in SQL Server 2005).

select * from INFORMATION_SCHEMA.PARAMETERS where 
SPECIFIC_NAME = OBJECT_NAME(@@PROCID)

And I guess that this means that I could, with some appropriately madcap dynamic SQL, also pull out their values.

Answer 3

Appears the best solution to my situation is to do profiling gathering only SP:starting and SP:completed and writing some TSQL to iterate through data and populate a tracking table.

I personally preferred code-generation for this, but politically where i'm working they preferred this solution. We lost some granularity in logging, but this is a sufficient solution to my problem.

EDIT: This ended being an OK solutions. Even profiling just these two items degrades performance to a noticeable degree. :( I wish we had a MSFT provided way to profile a workload that didn't degrade production performance. Oracle has nice solution to this, but it's has its tradeoff's as well. I'd love to see MSFT implement something similar. The new DMV's and extended events help to correlate items. Thanks again for the link Martin.