January 5, 2009 at 5:36 pm
We have over 100 stored procedures that are used mainly by crystal reports. I'm currently trying to find all the unused stored procedures and delete them. Is there a system table that holds information about when the object was last used or is there any other way to find out if these objects are getting used on not.
I had a look through sysobjects but could not find anything
January 5, 2009 at 6:11 pm
There is no such useful table. However, you can use profiler to track the activity against sql server database and then make an conclusion as to which procedures were never been called up.
MJ
January 5, 2009 at 6:13 pm
SELECT TOP 100 qt.TEXT AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = 'Your_DataBase_Name')
ORDER BY qs.total_physical_reads DESC
Hope at the End of the results you will find them, or change the ORDER BY qs.total_physical_reads DESC >>>>to >>>> ORDER BY qs.total_physical_reads ASC and you will see them on top!
Hope it will help you!
Dugi
:hehe:
January 5, 2009 at 6:37 pm
Does the script above available for sql 7 and/or 2000?
"-=Still Learning=-"
Lester Policarpio
January 5, 2009 at 6:46 pm
Lester Policarpio (1/5/2009)
Does the script above available for sql 7 and/or 2000?
No it doesn't work, because I'm using here CROSS Apply which is not supported both versions of SQL (7 or 2000)! Sorry I didn't see the the section of thread! Anyway it will work in SQL Server 2005 very nice!
:hehe:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply