Frequently Run Stored Procedures: Find Them in the Cache

Frequently run stored procedures leave a trail in the plan cache: an execution count for each one. That count is the fastest way to spot a procedure that runs in a loop it should not.

A Quiet Night With a Busy Server

A client’s application stopped running in the middle of a Saturday night. Weekend traffic was light, yet the server worked hard. CPU stayed near 100 percent for a long stretch. Disk IO was close to nothing. Memory sat steady at about 90 percent of the max server memory setting.

High CPU with low IO points to code that runs without touching tables. A procedure caught in a loop fits that picture. The plan cache held the answer, and one query found it. A procedure ran continuously in a loop, with no real code inside.

What the Cache Remembers

SQL Server keeps one row for each cached stored procedure in sys.dm_exec_procedure_stats. The row holds the execution count, the worker time, the logical reads and the time the plan was cached. Older scripts counted statements through sys.dm_exec_query_stats, which splits a procedure into pieces. The procedure view counts the procedure itself, which makes it the right place to look for frequently run stored procedures.

The demo builds a small database with three procedures. QueuePoll does nothing. GetPlantPrice reads one row. MonthlySummary scans 50,000 sales rows. The first script creates everything, and the second runs the procedures in bounded loops.

IF DB_ID(N'ProcRunCountDemo') IS NULL CREATE DATABASE ProcRunCountDemo;
GO
USE ProcRunCountDemo;
GO
DROP PROCEDURE IF EXISTS dbo.QueuePoll;
DROP PROCEDURE IF EXISTS dbo.GetPlantPrice;
DROP PROCEDURE IF EXISTS dbo.MonthlySummary;
DROP TABLE IF EXISTS dbo.Sales;
DROP TABLE IF EXISTS dbo.Plants;
GO
CREATE TABLE dbo.Plants (PlantID int NOT NULL PRIMARY KEY, Price decimal(6,2) NOT NULL);
INSERT INTO dbo.Plants (PlantID, Price) VALUES (1, 4.50), (2, 6.00), (3, 7.25);
CREATE TABLE dbo.Sales (SaleID int NOT NULL PRIMARY KEY, SaleDate date NOT NULL, Amount decimal(8,2) NOT NULL);
INSERT INTO dbo.Sales (SaleID, SaleDate, Amount)
SELECT TOP (50000) n, DATEADD(DAY, n % 365, '2025-01-01'), 5 + n % 40
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x
ORDER BY n;
GO
CREATE PROCEDURE dbo.QueuePoll AS
BEGIN
    SET NOCOUNT ON;
    RETURN 0;
END;
GO
CREATE PROCEDURE dbo.GetPlantPrice @PlantID int, @Price decimal(6,2) OUTPUT AS
BEGIN
    SET NOCOUNT ON;
    SELECT @Price = Price FROM dbo.Plants WHERE PlantID = @PlantID;
END;
GO
CREATE PROCEDURE dbo.MonthlySummary AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Months int;
    SELECT @Months = COUNT(*)
    FROM (SELECT MONTH(SaleDate) AS SaleMonth, SUM(Amount) AS Revenue FROM dbo.Sales GROUP BY MONTH(SaleDate)) AS m;
END;

DECLARE @i int = 0, @p decimal(6,2);
WHILE @i < 900
BEGIN
    EXEC dbo.QueuePoll;
    SET @i += 1;
END;
SET @i = 0;
WHILE @i < 500
BEGIN
    EXEC dbo.GetPlantPrice @PlantID = 2, @Price = @p OUTPUT;
    SET @i += 1;
END;
EXEC dbo.MonthlySummary;
EXEC dbo.MonthlySummary;
EXEC dbo.MonthlySummary;

Rank Them by Execution Count

The next query ranks the cached procedures of this database by how many times they ran. It reads the logical reads next to the count, because the pair tells the story.

SELECT OBJECT_NAME(ps.object_id, ps.database_id) AS ProcName,
       ps.execution_count AS ExecutionCount,
       ps.total_logical_reads / ps.execution_count AS AvgLogicalReads,
       ps.total_logical_reads AS TotalLogicalReads
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id = DB_ID()
ORDER BY ps.execution_count DESC;

QueuePoll tops the list with 900 calls and no reads. MonthlySummary ran three times, yet each run read 132 pages. A procedure with a huge count and no reads is the one from the story. Sorting by total reads instead would put GetPlantPrice first and QueuePoll last, so each ranking answers a different question. In the client’s case, the loop kept the CPU near 100 percent while disk IO stayed low.

Turn the Count Into a Rate

A count alone can mislead, because it grows from the moment the plan was cached. An old plan with a big count can belong to a quiet procedure. A young plan with a small count can belong to the loop. Divide by the time since caching to compare them fairly.

SELECT OBJECT_NAME(ps.object_id, ps.database_id) AS ProcName,
       ps.execution_count AS ExecutionCount,
       ps.cached_time AS CachedSince,
       ps.execution_count * 60000.0 / NULLIF(DATEDIFF_BIG(MILLISECOND, ps.cached_time, SYSDATETIME()), 0) AS CallsPerMinute,
       ps.total_worker_time / ps.execution_count AS AvgCpuMicroseconds
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id = DB_ID()
ORDER BY CallsPerMinute DESC;

The rate and the CPU values change on every run, so no sample is shown. The order is the point. QueuePoll comes first, then GetPlantPrice, then MonthlySummary, which is the slowest per call and the rarest. To look beyond one database, drop the database filter and add the database and schema names.

SELECT TOP (20) DB_NAME(ps.database_id) AS DatabaseName,
       OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS SchemaName,
       OBJECT_NAME(ps.object_id, ps.database_id) AS ProcName,
       ps.execution_count AS ExecutionCount
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id <> 32767
ORDER BY ps.execution_count DESC;

The filter removes the hidden resource database. Run this one on a server you know, since the rows depend on what the cache holds.

Find the Caller

Once a procedure looks suspicious, find who runs it. The next query lists the sessions that run a stored procedure right now. It adds the machine and application behind each session. It shows only the present moment, so repeat it for a procedure that finishes quickly.

SELECT r.session_id, s.host_name, s.program_name,
       OBJECT_NAME(t.objectid, t.dbid) AS ProcName
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE t.objectid IS NOT NULL
  AND r.session_id <> @@SPID;

The host and program names point at the application that owns the loop. Ask that team why the procedure runs without a pause.

What the Cache Forgets

The cache starts empty after a restart. Memory pressure and a manual plan cache clear remove plans too. Wait until a server has run through a normal day before you trust a ranking of frequently run stored procedures. A procedure that is not cached has no row at all. A procedure that recompiles on every call can be missing or show a small count.

You could argue that Query Store answers this better, because its history survives a restart. It does, where it is switched on. The plan cache works on every server today with no setup, so it is the first look. Stored Procedure Execution Count and Average Elapsed Time adds elapsed time to the same view.

What to Remember

To find frequently run stored procedures, rank by execution count and read the reads beside it. A high count with no reads is a loop that does no work. Then rank by rate, so the age of the plan does not hide the real culprit.

Fix the caller, not only the procedure. Find who calls it in a loop and why. Run the cleanup script when you finish.

USE master;
GO
DROP DATABASE IF EXISTS ProcRunCountDemo;

A busy procedure is not a problem, it is a clue about who keeps calling it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.