What is the cost of an unused sproc

Question

I've inherited a database with a surprisingly high number of unused sprocs. I've extracted the names of the used sprocs from the DLL that access it (all access is via that DLL), and from EXEC statements in those sprocs, and there are a couple hundred that are not called. Further, almost all have suspiciously verbose, uniform names: I suspect that they were generated by a tool.

The question I have is, what is the cost of keeping an unused sproc in the system? There's the additional maintenance, but I'm more interested in if there's any increase in memory consumption, processing time, etc.

I should say here that I'm not intending to remove the unused sprocs. This is simply something I want to know. The back story was just to get your interest and prompt an informed answer.

This is SQL Server 2008.

Answer 1

If they are truly unused stored procedures then there won't be any increase in processing time - as they are not being executed.

There'll be a cost in the (marginal) increase in the size of the database to store these procedures, but when compared to the size of the tables I would expect it to be minimal (unless there are 100s of them).

Answer 2

In addition to ChrisF's answer, if the sprocs were autogenerated as you suspect, then they were created for a reason - to create a uniform API for accessing the dB.

So pruning the unused sprocs will prune the API, which is all well and good until someone comes along expecting that missing API call to still be there.