How to Identify Most Called Stored Procedures in SQL Server

In managing a SQL Server, one of the key responsibilities is ensuring the server runs efficiently. The efficiency of your stored procedures can greatly impact the overall performance of your database. The first step in optimizing stored procedures is identifying which procedures are called most often.

Why Optimizing Stored Procedures is Important?

Stored procedures are pre-compiled SQL statements that are stored in the database. These statements can be reused over and over again by applications, which can improve performance because the database engine doesn't have to parse, compile, and optimize the SQL statement every time it is run.

Poorly written or inefficiently structured stored procedures can degrade the performance of your database, leading to slower application performance and increased resource consumption. This is why optimizing stored procedures is essential.

How to Identify Most Called Stored Procedures

When you execute the following SQL statement, you will get a list of stored procedures, orders by most called

SELECT
    OBJECT_NAME(object_id) AS [ProcedureName],
    execution_count AS [ExecutionCount]
FROM
    sys.dm_exec_procedure_stats
WHERE not OBJECT_NAME(object_id) is null
ORDER BY execution_count DESC

This SQL Query sorts the results by the execution count attribute, effectively showing the stored procedures that have been called the most.

How to Reset Stored Procedures Statistics + Implications

Resetting the stored procedure's statistics can be done using the DBCC FREEPROCCACHE command. It clears the plan cache for the SQL Server instance, effectively resetting the statistics for all stored procedures.

DBCC FREEPROCCACHE

Warning keep in mind that resetting statistics using DBCC FREEPROCCACHE should be used with caution. It removes all elements from the plan cache, which forces the SQL Server to recompile all subsequent execution plans, causing a temporary decrease in performance.

In addition, any plans that are being used will continue to reside in memory. Therefore, the operation doesn't necessarily free up a significant amount of memory. The DBCC FREEPROCCACHE command is most useful when you want to test stored procedure performance with a fresh cache or when you know a significant change has occurred in your data distribution and you want to ensure the stored procedures compile with the most up-to-date statistics.

In summary

optimizing stored procedures is an essential task for maintaining efficient database operation. Begin by identifying which stored procedures are called the most frequently, then focus on these procedures when you're optimizing. Remember to use caution when resetting statistics, considering the potential performance implications.