Recently, when I chatted about SQL Server issues, I found the following email:

"Is anyone aware of any freeware/shareware tools that can assist in identifying unused objects in a database? I have inherited a database from a previous developer, and there seems to be a lot of unused database objects
in the database. I know than Apex SQL clean can do the job, but my boss won't agree to the purchase the tool. Any suggestions would be appreciated."

How many times have we had such an answer from the boss!

So, I started to check the ways to define the unused objects without using any tools. I understand that tools may do the job better but I was interesting to do it myself at least just for basic analysis and for fun.

You know that in many cases deletion of unused objects will help make easier and quicker development maintenance and may improve the efficiency and the performance of a database. There are many methods that can be used. At the end, the main task is to answer what objects were not used for the month or two. I am saying a month or two because the time depends on the fact that a database may be used not only by the applications but by the backend processes as well. For example, in my company we have daily, weekly and monthly processes that define the time of how often object may be used. The task can be achieved with many methods, one of them by using SQL Profiler, but it

will require keeping the tool running constantly for a long time which is not practical and may even degrade the system's performance.

In my company the task becomes a little bit easier by knowing the fact that any database can be accessed only via call to a stored procedure. E.g. I have to split the task to the 2 sequential subtasks:

1. Find unused stored procedures and drop them

2. Find the objects that are not referenced by any stored procedure and are not lookup tables at the same time.

The main idea of my method is to constantly query the system cache to find the procedures that have no

execution plan for long periods of time. This can be achieved by using system table syscacheobjects which contains information on how the cache is used. Syscacheobjects belongs to the master database. At first glance, it may be hard to fully appreciate the value of this technique and the information produced by the output report but the method is very easy and provides a very accurate picture for the unused stored procedures. The technique is not as flawless as it may appear, but it offers a good available way for DBAs to find unused stored procedures, functions and the other objects by checking the database-execution plans.

Let's see the step by step and ideas and implementation. Will check an idea by analyzing the cache behavior for one procedure USP_Checkuser

  select name, id from sysobjects where name = 'USP_Checkuser'
name                                                    id         
------------------------------------------------------- ----------- 
USP_Checkuser                                           1093578934
select bucketid, cacheobjtype, objtype, objid, dbid      
 from master.dbo.SYSCACHEOBJECTS
 where dbid = 6 and objid = 1093578934
bucketid   cacheobjtype      objtype  objid       dbid   
---------- ----------------- -------- ----------- ------ 
545        Executable Plan   Proc     1093578934  6
545        Compiled Plan     Proc     1093578934  6
sp_recompile 'dbo.USP_Checkuser'

Object 'dbo.USP_Checkuser' was successfully marked for recompilation

select bucketid, cacheobjtype, objtype, objid, dbid    
 from master.dbo.SYSCACHEOBJECTS
 where dbid = 6 and objid = 1093578934

bucketid    cacheobjtype      objtype  objid      dbid   
----------- ----------------- -------- ---------- ------ 
 
Exec dbo.USP_Checkuser
go
 
select bucketid, cacheobjtype, objtype, objid, dbid     
 from master.dbo.SYSCACHEOBJECTS
 where dbid = 6 and objid = 1093578934
bucketid   cacheobjtype      objtype  objid       dbid   
---------- ----------------- -------- ----------- ------ 
545        Executable Plan   Proc     1093578934  6
545        Compiled Plan     Proc     1093578934  6

If you would like to make sure that object is marked for recompilation the next statement will show you the changes in object base schema and schema versions:

select name, id, base_schema_ver,schema_ver from sysobjects
 where name = 'USP_Checkuser'
BEFORE RECOMPILATION
name                                        id         base_schema_ver schema_ver  
------------------------------------------- ---------- --------------- ----------- 
USP_Checkuser                               1093578934 48              48
AFTER RECOMPILATION
 
name                                        id         base_schema_ver schema_ver  
------------------------------------------- ---------- --------------- ----------- 
USP_Checkuser                               1093578934 64              64

As you can see the fields

base_schema_ver and schema_ver are changing the value from 48 to 64. Every time

the procedure will be marked for recompilation the field's value will be

changing.

Step one is to cleanup the

cache by using the stored procedure sp_recompile that causes stored procedures

and triggers to be recompiled the next time they are run. Or DBCC FREEPROCCACHE

can be used to clear the procedure cache. Freeing the procedure cache would

cause, for example, an ad-hoc SQL statement to be recompiled rather than reused

from the cache. While you do this the object name and id can be written into the

table to show the time the cache for the object was cleared.

create table MONIT_ObjectRecompilation (
  MOR_ID int not null identity(1,1),
  SEQ_ID int,   
  DB_NM varchar(50),      
  Object_NM varchar(200),
  ObjectID int,           
  Object_type varchar(2),           
  Status char(1),
  -- R - recompiled;   
  -- S - from syscaheobject table
  Create_DT datetime default( getdate() )  )

The next step is to add each existing object while marking it for the recompilation. It can be done dynamically by the next batch.

Begin
 declare @minid int,@maxid int, @cmd Nvarchar(1000) 
       , @dbnm varchar(50), @seq_id int, @objectid int
 declare @tmp table ( objectid int, rcmd varchar(1000), tid int identity(1,1))
 
 set @dbnm = db_name()
 select @seq_id = max(seq_id) from dbo.MONIT_ObjectRecompilation
 
 set @seq_id = ISNULL(@seq_id,0) + 1
 insert into @tmp( rcmd, objectid)
   select 'EXEC sp_recompile ['+ name + ']' , id  
 from sysobjects 
 where type in ('P', 'FN', 'TR', 'TF') 
 select @minid = 1, @maxid = max(tid) from @tmp
 while (@minid <= @maxid)
  begin
   select @cmd = rcmd, @objectid = objectid from @tmp where tid = @minid
   EXEC sp_executesql @cmd    
   insert into dbo.MONIT_ObjectRecompilation 
    ( SEQ_ID, DB_NM , Object_NM, objectID, Object_Type, Status)
    select @seq_id, @dbnm, name, id, type, 'R'
  from dbo.sysobjects
     where id =  @objectid
   select @minid = @minid +1
  end
end

Next step will be inserting into the table the objects that are not recompiled yet. I setup a job that inserted a not compiled list of objects to the table. The job is running every 10 minutes.

declare @dbnm varchar(50), @seq_id int
set @dbnm = db_name()
select @seq_id = max(seq_id) from dbo.MONIT_ObjectRecompilation
set @seq_id = ISNULL(@seq_id,0) + 1
insert into dbo.MONIT_ObjectRecompilation (SEQ_ID, DB_NM, Object_NM, objectID, Object_Type, Status)
 select @seq_id, @dbnm, so.name, so.id, so.type, 'S'  
 from dbo.sysobjects so
   left join master.dbo.SYSCACHEOBJECTS sc 
   on sc.objid = so.id
   left join master.dbo.sysdatabases sd
      on sd.dbid = sc.dbid and sd.name = @dbnm
 where so.type  in ('P', 'FN', 'TR', 'TF')
 and sc.objid is null

This job will run for a month before I can define a usage of the stored procedures and functions. Then, let's find unused procedures and functions. If object name is in every insert for non recompiled object then the object is never was used.

declare @seq_id int
 
select @seq_id = max(seq_id)  from adm_support.dbo.MONIT_ObjectRecompilation
select  DB_NM, Object_NM, objectID, Object_Type, Status, count(*)   
 from dbo.MONIT_ObjectRecompilation mor
 where mor.status = 'S' 
 group by DB_NM, Object_NM, objectID, Object_Type, Status    
 having count(*) =  (@seq_id - 1)

Seq_id 1 was used to get all recompiled objects.

The research can be extended by usage of additional columns from table syscacheobjects that will allow you to analyze all databases on a server, how often object is used, and get over unknown ad-hoc queries and users who runs them as well as to see the first 128 characters of the statements.

Conclusion.

Your database may have many stored procedures, tables and views that aren't being used anymore but unless you determine which of your objects fall into this category you will be stuck with them forever. The technique described in the article is not as flawless as it may appear, but it offers a good available way for DBAs to find unused stored procedures, functions and the other objects by checking the database-execution plans without buying any third party tools. The captured information also may offers some great clues about what database objects you need to pay attention.