Stored Procedures are an essential part of SQL Server databases. They allow you to store and execute a set of SQL statements as a single unit, providing better performance and security. However, over time, databases can become cluttered with unused or unnecessary Stored Procedures, which can impact performance and maintenance.

In this article, we will explore a method to identify and analyze Stored Procedures in a SQL Server database using SQL Profiler and Excel. This approach can help you determine which Stored Procedures are being used and which ones can be safely removed.

Step 1: Capturing Stored Procedure Usage

The first step is to capture the usage of Stored Procedures using SQL Profiler. SQL Profiler is a powerful tool that allows you to monitor and capture SQL Server events. We will create a trace that captures the “SP:Starting” event, which represents the start of a Stored Procedure execution.

By filtering the trace on the desired database, we can collect data on all the Stored Procedures called by one or more applications during a specific period of time. This trace data will be stored in a local table for further analysis.

Step 2: Analyzing Stored Procedure Usage with Excel

Once we have captured the trace data, we can import it into Excel for analysis. Excel provides powerful pivoting capabilities that allow us to summarize and analyze the data effectively.

To import the trace data into Excel:

1. Open Excel and go to the “Data” ribbon.
2. Choose “From Other Sources” and then select “From SQL Server”.
3. Connect to the database where you stored the trace data and follow the wizard to select the correct table.
4. Choose “Table” as the import option.
5. You now have a dynamic Excel table with all the rows from the trace data.

Next, we can add calculated columns to analyze the duration of each Stored Procedure execution. This information can help us identify which Stored Procedures take the longest to complete.

Finally, we can create a PivotTable to summarize the data. Drag the “ObjectName” field to the “Row Labels” area and any other fields to the “Values” area. This will give us a count of how many times each Stored Procedure was called and the average, minimum, and maximum duration of execution.

Step 3: Identifying Unused Stored Procedures

Now that we have analyzed the usage of Stored Procedures, we can compare it with a list of all the Stored Procedures in the database to identify which ones are unused.

In SQL Server Management Studio, we can create a view that retrieves all the Stored Procedures in the database. We can then create a new worksheet in Excel and connect it to the database to retrieve this view.

In the Excel worksheet, we can add a column next to the Stored Procedure names and use a formula to check if each Stored Procedure is present in the PivotTable. If a Stored Procedure is not present, it means it is unused.

With this complete list of unused Stored Procedures, you can carefully review and delete them from the database. However, it is important to exercise caution and consider any administrative or unknown purposes that these Stored Procedures may serve.

Remember to keep the SQL Profiler trace running for a sufficient period of time to ensure that you capture all usage scenarios. It is recommended to test the impact of removing unused Stored Procedures in a non-production environment before making any changes in a production database.

By following this method, you can effectively identify and manage Stored Procedures in your SQL Server database, improving performance and reducing clutter.

Happy coding!