Tracking Stored Procedure Use

[Chris]

Trying to determine a way to see if stored procedures are being used. I am not sure of a way to see when and if a stored procedure is being called although I have thought of using sp_recompile to set the stored procedure to recompile but I am not sure in which table that flag is set so I can see which ones were not recompiled. Any assistance or guidance would be appreciated. Thanks.

[kjsdad]

You might try creating a stored procedure that writes to a table with the name of the stored proc as the column. Add this to each stored proc in question not all just the ones your not sure of... then later query your new table against the system tables to determine which ones haven't been used. You might key in the obvious ones that are being used so the don't show up in your results...


------------
Chris at 4/30/2002 1:35:22 PM

Trying to determine a way to see if stored procedures are being used. I am not sure of a way to see when and if a stored procedure is being called although I have thought of using sp_recompile to set the stored procedure to recompile but I am not sure in which table that flag is set so I can see which ones were not recompiled. Any assistance or guidance would be appreciated. Thanks.

[Chris]

That is what I am considering right now. I am hoping that there is another way to do it that does not require me to modify the existing SPs. I am looking into setting all of the store procedures to recompile and then monitoring which SPs were recompiled and which ones were not.

Thanks,
Chris

------------
kjsdad at 5/13/2002 10:51:58 PM

You might try creating a stored procedure that writes to a table with the name of the stored proc as the column. Add this to each stored proc in question not all just the ones your not sure of... then later query your new table against the system tables to determine which ones haven't been used. You might key in the obvious ones that are being used so the don't show up in your results...


------------
Chris at 4/30/2002 1:35:22 PM

Trying to determine a way to see if stored procedures are being used. I am not sure of a way to see when and if a stored procedure is being called although I have thought of using sp_recompile to set the stored procedure to recompile but I am not sure in which table that flag is set so I can see which ones were not recompiled. Any assistance or guidance would be appreciated. Thanks.

[Bill Russell]

Chris

A lot of third party tools will do this for you But if you just need to know what is being used frequently do this The one I like the best is SQL Probe

Select usecounts,dbid,sql
from syscacheobjects
order by usecounts desc

Good luck

Bill


------------
Chris at 4/30/2002 1:35:22 PM

Trying to determine a way to see if stored procedures are being used. I am not sure of a way to see when and if a stored procedure is being called although I have thought of using sp_recompile to set the stored procedure to recompile but I am not sure in which table that flag is set so I can see which ones were not recompiled. Any assistance or guidance would be appreciated. Thanks.