patternsqlMajor
Do SQL Server stored procedures cache data results?
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 :
...is it true that the result (list of users, in this case) is stored in cache.
Also, if I have these :
(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!
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
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.