How To Find Unused Stored Procedures In SQL Server

Code management can be much simpler in smaller, startup environments. As our environment grows and we must coordinate with many teams and colleagues, we can find ourselves in a situation where we have many objects, but questionable how much they are used. Unfortunately for developers or DBAs, we can be tempted to remove objects because we think no one uses it. Later, we find out that many were using these objects and we need them. In the video, TSQL: How To Find Unused Stored Procedures, we look at this situation in the context of stored procedures (though this problem can occur with other objects, including tables that have data). Rather than maintaining code by updating existing logic, often new objects are created with the new logic, deprecating old objects. The result is environments with thousands of unused objects.

Even if we assume that the multitude of objects means that these aren't used, we should have some evidence before removing objects. Some questions that are answered in the video about this exact problem along with some considerations:

• One of the queries listed (see below this) allows us to track this information about when a procedure was last called. What is one reason that this may not be effective? Outside of this video, what's another reason that this is not effective?
• What's another method that we can used that may be effective that's mentioned in the video?
• Suppose that our environment uses entity framework instead of stored procedures and we want to know what queries within our code are being used. How would we track this information?
• Assuming that we use the tracking code mentioned in the video, what are a few techniques that we must consider before we use the code to track procedures' last call time? Why would we need to do these things in order to ensure that we have accurate information? In the video, the below code was referenced to find information about the last stored procedure call - but keep in mind that this information may not be accurate depending on the last restart date:

SELECT t2.name
    , t1.last_execution_time
FROM sys.dm_exec_procedure_state t1
    INNER JOIN sys.procedures t2 ON t1.object_id = t2.object_id

For advanced tracking, you can get the code that is shown from GitHub. This creates a schema, if it doesn't already exist, and this tracks the stored procedure and the last call date through a procedure that is created. This updates the last call date on the table if the stored procedure has been called recently. This procedure should be called on a regular basis that coincides with the time that we may call our stored procedures. In addition, we should be tracking this information as early as possible because there are those rare times that we call a stored procedure once a year and we should be extremely careful about assuming that because a procedure is rarely called, it's never used (may be true or not). This also includes a job, but you'll want to update the schedule because this will depend on your environment.

I suggest that all companies source control all their code, as any removed object can be recovered. Depending on how we source control our code, we may be able to identify procedures that don't exist. This topic requires more discussion, but is worth a mention here. Unfortunately, what I find with some environments is that some code we find in source control, while other code we don't. This means that we have to be careful about the assumptions that we make when looking through it. Just because we don't find a reference to an object doesn't mean that the object is never used. It could be something outside of our source control - or worse, a query from another environment (linked server by a client). This can become even more complex if we source our code by server or function, especially if some functions for applications and logic cross.