Do SQL Server stored procedures cache data results?

Question

I have heard this from friends, but I never investigated whether this is true.

Is it true that the data results of an executed query are stored in cache?

I mean, if I have a stored procedure like :

SELECT * FROM USERLIST

...is it true that the result (list of users, in this case) is stored in cache.

Also, if I have these :

SELECT * FROM USERLIST WHERE user="user"
SELECT * FROM USERLIST WHERE user="userzzz"

(in which user/userzzz are passed as parameters), is it correct that it stores 2 different results on the database cache.

I don't think so, but I want the confirmation from you, experts!

Answer 1

Query results are not cached

However, the source table and index data and metadata will be cached after the 1st use (subject to continued use, load and memory pressure though)

That is, the results of a query will be evaluated every execution but the tables(s) (and any indexes etc) used by the query will most likely be in memory already.

The compiled execution plan will be cached which is where the confusion comes from I suspect

Answer 2

When a stored procedure is executed it is optimized and compiled and the query plan is placed in procedure cache.

Procedures remain in cache for other users, as long as there is space. Procedures are removed using the least recently used (LRU) algorithm.

---

While the initial execution of a stored procedure necessitates retrieval from sysprocedures on disk, it is possible, for subsequent executions, to simply retrieve the optimized plan from procedure cache. This behaviour can lead to significant performance gain.

So, what's in cache is the optimized plan from the stored procedure and not the results of the stored procedure.

Answer 3

When a query is ready to be processed by SQL Server, the SQL Manager looks it up in cache; and if it's not there, it must be compiled. The compilation process encompasses a few things.

When a stored procedure is executed it is optimized and compiled. according this a query plan is placed in procedure cache.

Check the Compilation and Execution section on Microsoft SQL Server Query Processor Internals and Architecture for detailed information about the query processing etc.

The end product of the compilation phase is a query plan, which is put into the procedure cache. SQL query result/operation could be of size in MB, GB so it is not put in the procedure cache or query plan.

Check the following diagram (from MSDN) for stored procedure execution to clear about your question:

Answer 4

SQL Server basically goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

1) syntactically check the query
2) if it's okay - it checks the plan cache to see if it already has an execution plan for that query
3) if there is an execution plan - that plan is (re-)used and the query executed
4) if there is no plan yet, an execution plan is determined
5) that plan is stored into the plan cache for later reuse
6) the query is executed

(copy of Marc_s answer)

Answer 5

As others have stated, query results in SQL Server are not cached.

If you want to cache the results of a query (or an SP), there are workarounds. For example, see this article at the Brent Ozar website.