We have a third party (vendor) application which uses a SQL Server 2000 database, and for which we (the internal IS department) are responsible for developing crystal reports (using stored procedures).

Currently we have in excess of 1000 reporting stored procedures and each time we get a vendor upgrade each one of these procedures needs to be checked against database structure changes, verified and sometimes modified to work with any database changes.

By tracking which reports have actually been used (and hence which procedures have been used) we can remove the unnecessary stored procedures and (hopefully) reduce the number of database objects that we need to check each application version upgrade. 😀

Assuming each procedure check takes 10 min, 1000 checks = 10000 minutes = 166 hrs = 20 days (8 hrs per day). That's a developer for almost a month, every time we get a version upgrade. That's a hefty investment in payroll and other things not getting done.

If we can remove even 100 procedures, that's 1000 minutes - that's a 2-day time saving. Gotta be happy with that.