August 1, 2014 at 12:24 am
Hi,
could someone of you please guide me how to find the un-used SPs in a database through T-SQL.
I have a list of SPs, 200 > , now I got a task to find the un-used DB objects (tables & SPs as of now), since the list is huge in size, I would like to get the details of its usage programatically.
please help me ASAP. all your inputs are really helpful to me.
Thanks,
Prabhu
August 1, 2014 at 1:02 am
You would need to put a trace on your database for a considerable amount of time to gather what queries are run against it, then you will have a rough idea as to what objects are used. I say rough as you might have processes which happen once a month, quarter, year, decade etc that you might not capture and if you drop them, them processes will no longer work.
The best thing personally would be to go through all the applications code that talks to the databases, finding what it calls from the applications perspective and then you have a better understanding of what objects the app needs to function correctly.
I would create a separate databases and move the objects into that, that are not being used, then you have a fall back to quickly recreate the object should it be used.
August 1, 2014 at 1:06 am
Stored procs:
Add a table which records usage. Add a line to each of the procedures which inserts into that table when called (or updates an existing row) to show when it was called. Run the system that way for an entire business cycle (make sure you catch month end and year end processes)
Tables:
Query sys.dm_dm_index_usage_stats, I'd say daily, but depends on how often the server is restarted. Persist the information into a table. Continue doing this for an entire business cycle, make sure you include month end and year end in the monitoring.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2014 at 1:39 am
Create a logging table.
Add a line into every procedure which inserts or updates that table (your choice) to indicate is has been called.
A year or so later (an entire business cycle) query that table, it will tell you what procedures have been used. From that you can tell which ones haven't.
All methods take time. There's no magic 'what hasn't been used' query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2014 at 3:15 am
thanks Gail..
I will do it and let you know, mean while I try to write a code as below to get the usage of tables.
--Query to Get Dependency of each table in the database.
Use Database
go
set nocount on
--select * from sys.dm_db_index_usage_stats
declare @TableName varchar(200), @i int , @C int
create table #TableUsage (TableName varchar(200), ProcedureName varchar(200))
select distinct identity(int,1,1) id, table_name into #Tables
from INFORMATION_SCHEMA.tables
select @I=min(id), @C=max(id) from #Tables
while @i<=@c
begin
select @TableName=table_name from #Tables where id=@i
insert into #TableUsage(TableName,ProcedureName)
select @TableName TableName,routine_name
from INFORMATION_SCHEMA.routines
where routine_type='PROCEDURE'
and substring(routine_definition,charindex(@TableName,routine_definition),len(@TableName))=@TableName
order by routine_name
print @i print @tablename
set @i+=1
end
go
select *
from #TableUsage
go
if object_id('tempdb..#TableUsage') is not null drop table #TableUsage
if object_id('tempdb..#Tables') is not null drop table #Tables
go
set nocount off
Thanks,
Prabhu
August 1, 2014 at 7:09 am
Good luck with that.... You're going to likely have a large number of false positives to clear out, not to mention you don't know whether or not the procedures are being used.
What about jobs?
What about SSIS packages?
What about ad-hoc queries from the application?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2014 at 7:35 am
You also need to check Application code. It may have some ad-hoc queries that is executing stored procedures or using tables.
What about database objects used in Functions (table value or scalar)?
Check RedGate - SQL Search (free tool) - this may helpful to you to search db objects usage.
Thanks
August 1, 2014 at 8:07 am
Hi Gail,
What about jobs?
What about SSIS packages?
What about ad-hoc queries from the application?
this DB is purely for Analysis and there is no jobs are applications using this one, thats the reason I choosed the above query. but the only one dependency is there few SSIS packages that I need to check with.
Thanks a ton for all your valuable replies.
Hi Hardy,
as there is no applications depends on this DB, there is a huge time saving .
Thanks,
Prabhu
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply