SQL Server How to find stored procedures not used in data layer code?

qybjjes1  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(128)

We have a C# application with data access code written in C# that calls stored procedures in SQL Server.

I noticed some of the stored procedures are no longer called by the data access code. Is there a way we could find those stored procedures and delete them?

We tried the dm_exec_procedure_stats , but since this application is still in staging phase we found the stats to be unreliable.

0yg35tkg

0yg35tkg1#

You have two options for an application that is actively used:

  1. Configure extended events to record each instance of stored procedure call (OR use can use SQL Server Profiler to achieve the same)
  2. Enable Query Store to monitor query performance. Query store views can be used to retrieve object_id of an object (SP, UDF, Trigger etc.) that the query belongs to.

I would suggest Query store as it also lets you monitor overall database performance as well as identify unused code.

As was pointed out in the comments you do need to run this process for a long time (a month or more) to ensure that all "rare" events are captured.

Once unused objects are identified I suggest quarantining them (by renaming them) for a few months before finally removing them.

If your application is still in development then the best way is (as @Brad suggested) is static code analysis. It does get somewhat involved though if you have stored procedures calling other stored procedures. PowerShell should help you automate this thought.

相关问题