Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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="mark"
SELECT * FROM USERLIST WHERE user="markzzz"

(in which mark/markzzz 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!

share|improve this question

migrated from stackoverflow.com Dec 19 '11 at 14:52

This question came from our site for professional and enthusiast programmers.

    
No stored procedure doesn't cached data-results –  Gaurav Agrawal Dec 19 '11 at 10:30
1  
the execution plan is cached, not the results –  NimChimpsky Dec 19 '11 at 10:35
3  
Tell your friends to go back to reading documentation. From word 1. Every word. They have made up a lot of bad information. –  TomTom Dec 19 '11 at 10:36
    
just answered now for you by marc_s stackoverflow.com/questions/8559443/… –  Nighil Dec 19 '11 at 10:37
4  
Incidentally, Oracle 11.2 does cache in this case. –  Gaius Dec 21 '11 at 10:10

4 Answers 4

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: enter image description here

share|improve this answer
    
-1 The question is about caching data not execution plans. This answer would be better in the OP's other question –  Martin Smith Dec 19 '11 at 11:09
    
this is regarding caching.. i have shown this diagram to let him know that data cache is created and specified the link to check this in details.. that section have detailed information about this when i have picked that diagram... well i am updating the answer too.. –  Niranjan Kala Dec 19 '11 at 11:19
    
Your answer is (at the moment) all about plan caching whereas the question is about caching of results (which SQL Server does not do). More relevant would be discussion of caching of database pages in the buffer cache. –  Martin Smith Dec 19 '11 at 11:22
    
I have updated this one.. i think it is now relevant to question now.. –  Niranjan Kala Dec 19 '11 at 11:35
    
You need to focus on the storage engine vs. the QOP... –  Anon246 Dec 19 '11 at 17:30

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)

share|improve this answer

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

share|improve this answer
    
"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." : yeah, also i I don't using SP I guess... that's a Database optimization, that doesnt matter with Sp, right? –  markzzz Dec 19 '11 at 10:40
    
@markzzz: almost all SQL is compiled to an execution plan, whether a direct UPDATE, SELECT or a stored procedure –  gbn Dec 19 '11 at 10:42

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.