Will calling stored procedures via callproc cause cache bloat?

Question

I am invoking stored procedures as follows:

conn.autocommit(True)
with conn.cursor(as_dict=False) as cur:
    cur.callproc(proc_name, query_params)
    return list(cur)

The Profiler shows these calls:

RPC:Completed exec dbo.SelectCustomer 'Jane', 'Doe'
(snip)
RPC:Completed exec dbo.SelectCustomer 'John', 'Doe'

Can this cause cache bloat on the server?

Answer 1

No it won't. As long as the query plan remains in the plan cache it will be reused when that stored procedure is called. You can check for query plans within the plan cache with this query:

SELECT *
FROM sys.dm_exec_cached_plans a
CROSS APPLY sys.dm_exec_sql_text(plan_handle) b
CROSS APPLY sys.dm_exec_query_plan(plan_handle) c
WHERE text LIKE '%SprocName%'
AND b.dbid = DB_ID('DatabaseName')
ORDER BY a.size_in_bytes desc

There are a few reasons a query plan can be purged from the plan cache:

1. Memory Pressure - when memory is needed for new plans, the old, unused ones get cleared out.
2. Changes to the database, including tables used within the SP, changes to the SP, changes made to indexes used by the SP, changes to statistics, and more.

Microsoft elaborates on this topic here.