rarely used procedures in db

Question

Post reply

rarely used procedures in db

NewBeeSQL

SSCrazy

Points: 2170
More actions
May 5, 2010 at 2:47 am

#222103

Is there any script which returns the stored procedure names in db which are never or rarely used?
-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 1

Please look at the URL:

http://qa.sqlservercentral.com/articles/Administration/2582/

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

This has given some direction but not serving my need exactly...

can any one please post any query( may be using dmv) to get unused objects list directly..

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

There isn't a way to get the list of unused objects directly. You can only try to determine the objects that are in use. Depending on the method you use, that's only going to be over a varied length of time. You can capture all calls using a server-side trace for some period of time and then determine which objects are never called. Or you can pull objects out of the cache using DMV's and put them into a table over a period of time and then determine which objects are not called, but there isn't a maintained list of things that are called an not called built into SQL Server.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 4

Thanks Grant....am clear now.....

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Eric M Russell SSC Guru Points: 125255 More actions · Answer 5

The following query returns the name, execution count, min/max execution time, etc. for all stored procedures. It can also be extended to include CPU utilization and also performance metrics simply by adding additional columns from sys.dm_exec_query_stats.

Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it. However, it was re-started a few days ago, then it probably hasn't had time to collect data on all the procedures your application execs during a typical usage cycle (ex: month end reports or quarterly re-partitioning of data).

select

so.object_idas ObjectID,

schema_name(so.schema_id) + '.' + so.name as ObjectName,

so.create_date as ObjectCreated,

so.modify_date as ObjectModified,

sum(qs.execution_count) as total_execution_count,

sum(qs.total_worker_time) as total_cpu_time,

min(last_execution_time)as min_execution_time,

max(last_execution_time)as max_execution_time

from sys.dm_exec_query_stats AS qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st

full outer join sys.objects as so

on so.object_id = st.objectid

where

so.type = 'P' and

so.is_ms_shipped = 0

group by

so.object_id,

schema_name(so.schema_id),

so.name,

so.create_date,

so.modify_date

order by

sum(qs.execution_count) desc,

so.create_date desc;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

eric_russell 13013 (5/6/2010)
The following query returns the name, execution count, min/max execution time, etc. for all stored procedures. It can also be extended to include CPU utilization and also performance metrics simply by adding additional columns from sys.dm_exec_query_stats.
Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it. However, it was re-started a few days ago, then it probably hasn't had time to collect data on all the procedures your application execs during a typical usage cycle (ex: month end reports or quarterly re-partitioning of data).
select
so.object_idas ObjectID,
schema_name(so.schema_id) + '.' + so.name as ObjectName,
so.create_date as ObjectCreated,
so.modify_date as ObjectModified,
sum(qs.execution_count) as total_execution_count,
sum(qs.total_worker_time) as total_cpu_time,
min(last_execution_time)as min_execution_time,
max(last_execution_time)as max_execution_time
from sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
full outer join sys.objects as so
on so.object_id = st.objectid
where
so.type = 'P' and
so.is_ms_shipped = 0
group by
so.object_id,
schema_name(so.schema_id),
so.name,
so.create_date,
so.modify_date
order by
sum(qs.execution_count) desc,
so.create_date desc;

A word of caution, this query may not return all stored procedures. As explained by Adam Machanic here, sys.dm_exec_query_stats will ignore procs or scripts that contain an alter table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tony.turner Right there with Babe Points: 783 More actions · Answer 7

eric_russell 13013 (5/6/2010)
Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it

In addition to not all entries being cached, on more active systems entries may be purged from the cache on a more frequent basis; because the cache is full, because the table statistics changed causing the entry to be re-compiled, etc. Check the column creation_time in sys.dm_exec_query_stats to get a feel for the cache lifetime, and record used procs on a more frequent basis

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

tony.turner (5/7/2010)
eric_russell 13013 (5/6/2010)
Note that because it gets this info from database management views, the history only goes back to the last time the SQL Server service was last started. If your service has been running for 30 days or more, then perhaps you can use it
In addition to not all entries being cached, on more active systems entries may be purged from the cache on a more frequent basis; because the cache is full, because the table statistics changed causing the entry to be re-compiled, etc. Check the column creation_time in sys.dm_exec_query_stats to get a feel for the cache lifetime, and record used procs on a more frequent basis

Or worese yet, since sys.dm_exec_query_stats, and other DMV's, is dependent on the cache, if someone were to runn DBCC FREEPROCCACHE, all that information is gone.

This data doesn't go back to the time that the server was started.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Eric M Russell SSC Guru Points: 125255 More actions · Answer 9

How appropriate my previous query is depends on how someone intends to use the information. I wouldn't use this info exclusively to decide which stored procedures can be identified as obsolete and dropped, but it is useful when working familiarizing myself with a new database application, to know what are the main procedures that get called and basically how frequently in the last 24 hours.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQLRNNR SSC Guru Points: 281324 More actions · Answer 10

A solution that I have implemented is to run a trigger that inserts the data for all running stored procedures into a log table. The trigger was a logon trigger and worked because we had a million users a day.

An alternative is to query the DMV on a regular basis via a SQL Agent job and to dump the results into a table. You still may miss any procedures that have table alters in them, but it will preserve most of the data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 11

Thanking all who responded...i got very useful info...

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

SQLRNNR SSC Guru Points: 281324 More actions · Answer 12

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events