Validating That An Object Has Been Used
Chris Shaw
After your database matures and goes through a number of incremental changes over the years, there is a potential for objects to be sitting in your database that are not used. The negative impact of these objects is pretty negligible; however some administrators may want to keep their system clean. How can you tell what objects have been used? How can you tell what objects you can safely remove from your system? In this article we will focus on specific stored procedures and how you can begin to make these decisions.
I am not aware of any SQL Server DMV, Trigger or other item that you can look at to get the direct answer, however, there are a couple ways that you can accomplish what you want to do to get the same results. The first one that could be used is a positive validation that the stored procedure has been called. The second option is to see what we can find in the cache.
Let’s look at the first one, the positive validation. This one can be completed by first creating a logging table that will store all of your stored procedure calls. If this process is acceptable with the performance of your other stored procedures, you may want to consider leaving this in the system long term. With long term storage, you may want to consider storing the duration of the stored procedure as well. My logging table looks like this.
Create Table ProcDurationTracker
(
ProcName varchar(100) not null,
CompleteDuration int not null
)
The first method that I tested was having a procedure log itself each time that it executed. A number of years ago I used the same methodology to help me understand how the users were using the database. After some time of looking at the statistics, I realized that I could do a quick query of the table to see usage patterns, but if I added the completed duration time, I had the ability to see what procedures would offer the biggest performance gains from tuning. To explore this some more, consider a simple stored procedure that would return all the rows from a movies title table:
Create Procedure ProcTest2
AS
Declare @beginTime datetime
Declare @endtime datetime
Set @beginTime = GETDATE()
-- My Normal Procedure Here
Select [Movie Name], [Name Extended]
From Movies
-- End Procedure Here
Set @endtime = GETDATE()
Insert into ProcDurationTracker Values('ProcTest2',DATEDIFF(SS,@beginTime, @endtime))
GO
The ProcDurationTracker table becomes even more helpful when you add the completed date and time to the table. A simple table like this would not add much time to the execution of a stored procedure but would add a lot of information to the nature of the database such as:
· Procedures not used
· Procedures used
· Procedures used most often
· Total working seconds per procedure, per day.
· Usage Patterns
In the past I have even logged the values passed to the procedure so I could get an idea of what information was searched on the most often. For example, I can tell you when I was working with online yellow page directories that restaurants and physicians were the terms that were searched for most often. Interestingly enough, the word “restaurants” was the word most often misspelled in the searches.
The second option is to see what we can find in the cache. The dynamic management views can give us an insight to what the SQL Server is doing. There are a couple of issues with this method that need to be kept in mind. First is that this only captures what is in the cache. To find stored procedures that are not in the cache would mean that it has a potential or is likely not being used. This means that if the procedures are only used once a month, like a month end report, the procedure may not end up in the cache for some time.
To put it in simple terms, doing it this way shows you the procedures that are being used by showing they’ve been thrown into cache.
The idea with this method is to remove from question if a procedure is being used by validating that it is. If you can validate 80% of your procedures, the research time is dramatically reduced when there are only 20% of the procedures to look at.
The initial step requires me to create a table to store my data long term. In a situation where I have monthly and even yearly reports that call a number of stored procedures this could help to catch these in the cache. The table I used for my testing looked like this.
Create Table ProcTracker
(
DatabaseName Varchar(55) not null,
ProcName varchar(100) not null
)
The next step required is to find the stored procedures in the cache and insert them into the table. This could be run manually or it could be run as a job. I believe I would run it as a job so that I could schedule it to occur long term to include “off hours” to catch any batch process that may be running. I found a great script written by Michelle Ufford. I altered the script to not get as much information and to insert the data into the table I was using for the tracking. The core of the code I tested looked like:
INSERT Into ProcTracker
SELECT DB_NAME(dest.[dbid]) AS 'databaseName',
OBJECT_NAME(dest.objectid, dest.[dbid]) AS 'procName'
FROM sys.dm_exec_query_stats AS deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.[dbid] IS Not Null -- exclude ad-hocs
GROUP BY DB_NAME(dest.[dbid])
, OBJECT_NAME(dest.objectid, dest.[dbid])
ORDER BY databaseName, procName
A short couple notes about this process:
· The frequency that the job may be called will vary. The more often you run this process the more stored procedures will be identified.
· If you use the same process that I do, you may want to consider removing the duplicates.
· I have left the process the way it is because this process is designed to record information other than a procedure that has been executed.
The list of stored procedures that are in the cache are now located in the tracking table. After a period of tracking what procedures are in the cache, I can run a simple query to identify the procedures that are not in the cache by by executing:
Select name from sys.objects so
Where type_desc like 'SQL_STORED_PROCEDURE'
And name Not in(Select ProcName from ProcTracker)
The results of the above query tell me where I need to focus my attention when I am trying to identify what stored procedures are not in use in the database. Once I have that list I can begin discussions to see if anyone knows what they are used for.
If given the option, I prefer to use the first process described in this article. By inserting a line into the stored procedures, I not only collect the execution but I can also retrieve the number of executions.
When I first started to bring this subject up with other SQL Server Professionals, there was the question of “is this something that really needs to be done?” There is an argument to be made that if the procedure was worth creating you may want to keep it around for the history, or it’s re-usability. I tend to fall more into the camp of a clean system is an easier system to manage.
