THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

How do you deprecate a proc in your prod database?

Recently, I got into some discussions on deprecating stored procedures from the production environment. If you want to remove a proc from your production database, what are the steps you take to accomplish that?

 

The act of actually removing a proc from your prod database is trivial in itself. What is not so trivial in many real-world production environments is to make sure that the proc is really not being used. In other words, it's not enough to just consult some know-it-all guru, or check a hopefully-up-to-date application doc. The guru most likely does not know it all or may have already left to pursue some other opportunities, and the documentation is probably not up to date or may not even exist.

 

You need to have in place a robust process to determine if a proc is being used in your production environment. 

 

There are multiple ways to arrive at that determination. If you have a 3rd-praty audit tool, you may use it to record the proc execution. Alternatively, you can always set up a trace and filter on the proc name or proc id. In addition, you can regularly query the plan cache to see if there is a cached plan for the proc.

 

In practice, I’ve found the querying-plan-cache method to be simple and inexpensive in terms of the computing resource consumption, and if you query the plan cache frequently (say, once every minute or so), it is reliable enough that the chance of missing a proc--that is in use but doesn't stay long enough in the plan cache--is quite small. For most stored proc deprecation purposes, I’d recommend this method over setting up a trace. Note that most, if not all the 3rd-party audit tools set up traces behind the scene.

 

But that’s just my experience. I wonder how you deal with this issue in your practice.

Published Wednesday, August 18, 2010 3:51 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Aaron Bertrand said:

I agree that plan cache is a good alternative to trace, and that the interval of your query will be driven by how busy your system is, how much cache you actually have available, and how many procedures you have running ... though there is always the risk that the smallest gap is not small enough, and you may miss a procedure for different reasons than why you may miss it in a trace.

More importantly, you need to be sure that you query through a full business cycle.  There are sometimes procedures that are called extremely infrequently (once a quarter, once a year, within the first few business days of the month), and outside the scope of any source code you can grep.

In other words, you need to know your business rather than just audit it.

August 18, 2010 3:31 PM
 

mjswart said:

Another method worth considering (but that I've never put into practice): <a href= "http://michaeljswart.com/?p=800">Increment a user-settable counter.</a>

August 18, 2010 4:03 PM
 

Kevin Devine said:

I have actually placed code at the end of procs that use sp_send_dbmail to my address whenever they are executed.  Not perfect, but it did find about 20 procs that were no longer in use and greatly helped us in upgrading to SQL 2008.

August 18, 2010 4:05 PM
 

Alexander Kuznetsov said:

I recently used a totally brute force approach, I added a table as follows:

CREATE TABLE [dbo].[ReadLog](

[ReadLogID] [int] IDENTITY(1,1) NOT NULL,

[ReadDate] [datetime] NOT NULL,

[SuserName] [varchar](50) NULL,

[HostName] [varchar](50) NULL,

-- all the parameters the proc was called with

and I added code that populates the table right at the beginning of the candidate for deletion. After 4 months of monitoring, we felt confident we could drop the deprecated module, and we didn't have to worry that our monitoring missed something.

August 18, 2010 5:06 PM
 

Linchi Shea said:

I agree that if you can modify the proc to add logging code, that would be the most reliable. Did that myself as well.

August 18, 2010 5:40 PM
 

davilev said:

I use the proc cache method as well but usually run the query after hours because it can kill performance if the proc cache is large (10 GB). Traces proved to be too risky on some of my higher volume systems. I do intend to look into the extended events / auditing in SQL 2008. It seems like there could be an answer there that makes everyone happy.

August 18, 2010 9:01 PM
 

Shyam Viking said:

I go by using procedure plan cache, and traces quite often to find out any unused database objects. After weeks of analysis I decide on dropping the objects or probably rename as the first step, and drop a little later.

August 19, 2010 12:26 AM
 

GrumpyOldDBA said:

I run replay traces to summarise procedure use every so often, but most of the app procs at my current client log to a table, but not all of course. I prefer the logging to a table method.

August 19, 2010 6:18 AM
 

Chim said:

I think Aaron's words say it all really.

In this case, there is simply no substitute for knowing the business.

The fact that a Procedure that is regularly called say once a month hasn't been called in three months is no guarantee it is no longer in use.  It could be that the event that triggers the Procedure call has simply not occured in a while.

August 19, 2010 7:23 AM
 

jonmcrawford said:

how much time do you allow between starting your log and dropping the object? Would suck if you dropped it only to find out that it was for a report that is only run annually, but is critical because it goes to financial auditors or something.

August 19, 2010 10:41 AM
 

Linchi Shea said:

You probably don't want to rush too much in deprecating a proc. Obviously, logging and tracing alone shouldn't be the only determinging factor. Usually, that should be combined with some knowledge of the business or app to go into the decision input. But in the real world, information is rarely perfect. There is always some risk in deprecating a proc. At some point, the dev mgmt needs to make a call on whether there has been enough logging and go ahead with the cleanup. After all, they are being paid big bucks for doing precisely that. As long as you can quickly put it back, the risk should still be manageable.

August 19, 2010 11:41 AM
 

ivanrdgz said:

I have been using SQL Server Audit (SQL 2008 and up) to audit the executions of certain stored procedures and it does the trick.

Something like this:

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecificationForTestDB]

FOR SERVER AUDIT [Audit_CaptureAllCallsToSPs]

ADD (EXECUTE ON DATABASE::[TestDB] BY [dbo])

GO

It answer the questions who, when and what was executed. The decision to definitely remove the Sp from the database require much more analysis as everyone has mentioned before.

August 19, 2010 1:59 PM
 

Glenn Berry said:

I like to do a dependency check to try to make sure the SP is not called by another SP in the same database (even though that is not completely reliable). If you have access to the source code for your application(s), you can use something like WinGrep to search for the SP name. Looking at the Procedure cache and adding logging code to the SP are pretty reliable. Finally, I alway rename the SP with a zzz prefix prior to deleting it.

August 20, 2010 11:32 AM
 

Nachi said:

Hi,

If I am confident enough the store procedure is not in use then in my normal practice I would rename and leave for a month if any application or anyone complaints about it(If you know all store procs should have been used within certain period of time).

Thanks

Nachi

August 20, 2010 12:14 PM
 

Gabriel said:

I've used the logging solution a few times. Just make sure the application has permission to write to the log table.

August 24, 2010 11:29 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement