To help identify the obsolete procedures I started by creating a simple table to hold my usage statistics.
I then created this little SQL snippet. I identified procs that I suspected were no longer in use and added the snippet. Putting a date in the comment is the easiest way to keep track of how long it has been active.
At a later date I would run the following query to see what's active. I can comment out the snippet from more active procs such as spOMInsertFill_1_0. You might want to keep a note of the high usage procs so you can profile them for performance at a later date.
| FunctionName | LastRun | NumberOfRuns |
|---|---|---|
| spOMUpdateOrder_1_1 | 2011-05-24 17:03:56.060 | 60 |
| spOMBreakLinksOnGTCOrder_1_0 | 2011-05-24 16:50:02.270 | 12 |
| spOMGetCommonTraders | 2011-05-24 16:50:01.317 | 2 |
| spOMGetAllAccountTypes | 2011-05-24 16:43:55.843 | 1 |
| spOMGetStatus | 2011-05-24 16:14:46.773 | 1973 |
| spOMInsertFill_1_0 | 2011-05-24 05:25:00.580 | 13687 |
| spOMRptSG | 2011-05-23 17:10:02.260 | 3 |
| spOMDeleteOrderFill | 2011-05-23 15:52:10.513 | 2618 |
The above list provides all the procs that have been called but what about the ones that haven't? Running the following query will list all the procs where you have added your code snippet but don't appear in the table.
| Name |
|---|
| dbo.spOMAdjustCamelliaAttention |
| dbo.spOMBreakLinksOnGTCOrder_1_0 |
| dbo.spOMConvertTTToOMS_1_0 |
| dbo.spOMDeleteOrderFill |
| dbo.spOMGetAlertColours |
| dbo.spOMGetAllAccountTypes |
| dbo.spOMGetArchiveOrderFillGroup |
| dbo.spOMGetArchiveSingleOrder |
| dbo.spOMGetArchiveStrategy |
| dbo.spOMGetArchiveStrategyOrders |
| dbo.spOMGetArchiveStrategyOrders_1_0 |
| dbo.spOMGetAvailableDesks |
This process is manual and intrusive but I only clean up old procs once or twice a year. It also helps that I have access to the procs to inject my code. One possible addition that may add value is to include APP_NAME() in the table so you know what application is calling the proc.
For those in larger (and more restrictive) corporate environments SQL Profiler may be the only option. It would be an interesting challenge to identify obsolete procs passively.
