HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Do SQL Server stored procedures cache data results?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedproceduressqlcacheserverresultsdata

Problem

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!

Solution

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

Context

StackExchange Database Administrators Q#9380, answer score: 23

Revisions (0)

No revisions yet.