-
unused stored procedures using monitoring tables
Hello Experts,
can anyone please let me know, how to find the unused stored procedures using monitoring tables of master database in sybase.
actually, i already wrote 2 different stored procedures using monitoring tables to find the unused indexes and tables using monopenObjectactivity table. and those stored procedures are running in production to capture the data for around 2 months and then later i will write a query on permanant table which i created as part of stored proc to save the historical data and get the unused objects and indexes.
In the same way i need to create the stored procedure to monitor the historical data for unused stored procedures in the server and monitor the stored procedures for 2 production releases and then if those stored procs are never used from past 4 to 5 months in production then i need to drop them. This is my task.
can anyone please suggest me how do i write a stored procedure to monitor the unused stored procs and save the data to one permanant table and that should stored proc which i write dba's would schedule it to run on daily basis to gather the historical data. then i need to query the permanant table and list all the unused stored procedures. so that the developers would review them and decide whetther to really drop those stored procedure or not......
please experts, give me the query but please don't give me the links, as i have already googled and i'm unable to find a way...
http://www.sybase.com/sb_content/102...MDA-042406.pdf
this above pdf has the query for analyzing the stored procedure performance queries...by considering these information
I thought the below query would give the list of unused stored procedures when i run in the production server. But i observed that, this query is only pulling the stored procedures which are only called by applications in production.
select object_name(ProcedureID,DBID), ProcName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),
DBName = isnull(db_name(DBID), "UNKNOWN"),SPID, CpuTime, LogicalReads, PhysicalReads, MemUsageKB, StartTime, EndTime
LineNumber,
ElapsedTime = datediff(ms, StartTime, EndTime), KPID, BatchID
from master..monSysStatement
where ProcedureID != 0 and isnull(db_name(DBID), "UNKNOWN") not in ( 'sybsystemprocs ' , 'tempdb')
unable to figureout which monitoring table would help to know the unused stored procedures in the databases of productions server. can anyone please help me?
Thanks alot in advance.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|