Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Identifying Unused Objects in a Database

By Leo Peysakhovich,

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.

cacheobjtype

nvarchar(34)

Type of object in the cache:

Compiled Plan
Executable Plan
Parse Tree
Cursor Parse Tree
Extended Stored Procedure

objtype

nvarchar(16)

Type of object:

Stored Procedure
Prepared statement
Ad hoc query (Transact-SQL submitted as language events from isql or osql, as opposed to remote procedure calls)
ReplProc (replication procedure)
Trigger
View
Default
User table
System table
Check
Rule

objid

int

One of the main keys used for looking up an object in the cache. This is the object ID stored in sysobjects for database objects (procedures, views, triggers, and so on). For cache objects such as ad hoc or prepared SQL, objid is an internally generated value.

dbid

smallint

Database ID in which the cache object was compiled.

uid

smallint

Indicates the creator of the plan for ad hoc query plans and prepared plans. -2 indicates the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.

usecounts

int

Number of times this cache object has been used since inception.

sql

nvarchar(256)

Procedure name or first 128 characters of the batch submitted.

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.

Total article views: 22337 | Views in the last 30 days: 44
 
Related Articles
ARTICLE

Optimizing Stored Procedures To Avoid Recompiles

One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of ...

FORUM

dynamic object(database/schema/obj) in stored procedure

how to reference multiple database/objects in stored procedure (parameterization)

FORUM

Reindexing requires recompile of Stored procedures

After reindexing a database application fails, recompile of Sp correct problem.

ARTICLE

The RECOMPILE Options

This artcle attempts to differentiate between the uses of WITH RECOMPILE and OPTION (RECOMPILE) feat...

BLOG

How to list all CLR objects inside your database?

I wrote the following query that returns the list of all CLR functions/procedures/objects exists ins...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones