CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
soren.qvist
soren.qvist

Reputation: 7416

Rerouting all stored procedure calls through a single stored procedure for logging purposes

I'm playing with the idea of rerouting every end-user stored procedure call of my database through a logging stored procedure. Essentially it will wrap the stored procedure call in some simple logging logic, who made the call, how long did it take etc.

Can this potentially create a bottleneck? I'm concerned that when the amount of total stored procedure calls grows this could become a serious problem.

Upvotes: 0

Views: 68

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

Routing everything through a single point of entry is not optimal. Even if there are no performance issues, it is still something of a maintenance problem as you will need to expose the full range of Input Parameters that the real procs are accepting in the controller proc. Adding procs to this controller over time will require a bit of testing each time to make sure that you mapped the parameters correctly. Removing procs over time might leave unused input parameters. This method also requires that the app code pass in the params it needs to for the intended proc, but also the name (or ID?) of the intended proc, and this is another potential source of bugs, even if a minor one.

It would be better to have a general logging proc that gets called as the first thing of each of those procs. That is a standard template that can be added to any new proc quite easily. This leaves a clean API to the app code such that the app code is likewise maintainable.

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

SQL can run the same stored procedure concurrently, as long as it doesn't cause blocking or deadlocks on the resources it is using. For example:

CREATE PROCEDURE ##test

AS
BEGIN 

SELECT 1
WAITFOR DELAY '00:00:10'
SELECT 2

END

Now execute this stored procedure quickly in two different query windows to see it running at the same time:

--Query window 1
EXEC ##test

--Query window 2
EXEC ##test

So you can see there won't be a line of calls waiting to EXECUTE the stored procedure. The only problem you may run into is if you are logging the sproc details to a certain table, depending on the isolation level, you could get blocking as the logging sproc locks pages in the table for recording the data. I don't believe this would be a problem unless you are running the logging stored procedure extremely heavily, but you'd want to run some tests to be sure.

Upvotes: 0

Related Questions