May 5, 2010 at 2:47 am
Is there any script which returns the stored procedure names in db which are never or rarely used?
May 5, 2010 at 3:11 am
Please look at the URL:
http://www.sqlservercentral.com/articles/Administration/2582/
May 5, 2010 at 3:27 am
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..
May 5, 2010 at 6:03 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 10:47 pm
May 6, 2010 at 11:14 am
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
May 6, 2010 at 11:30 am
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
May 7, 2010 at 2:29 am
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
May 7, 2010 at 6:09 am
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 itIn 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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2010 at 8:08 am
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
May 7, 2010 at 11:06 am
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
May 10, 2010 at 12:17 am
May 10, 2010 at 10:07 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply