Question on SQL Server and the cache

I'm new to databases and I'm trying to understand how the memory cache
works with sql server (I think Oracle calls it the "Block Buffer")
I fire up sql server
I query a table (ie select * from mytable where myid = 10)
This query's plan ends up in cache?
Do the returned rows end up there too?
Does it matter if myid is an index or not for the cache to utilised?
If the query did not have a where clause, would the entire table end up in
memory?
Sorry for the dumb questions... just trying learn
TIA> This query's plan ends up in cache?
Maybe. SQL Server caches plans to avoid the compilation costs. However,
trivial plans (probably this one) are not cached since little work is needed
to compile.

> Do the returned rows end up there too?
Data are read into the buffer cache. Execution plans are kept in separate
plan cache.

> Does it matter if myid is an index or not for the cache to utilised?
No.

> If the query did not have a where clause, would the entire table end up in
> memory?
If there is sufficient buffer cache, yes. If not, cache is reused according
to (roughly) a LRU algorithm so that the most recent and frequently accessed
data stay in cache.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> I'm new to databases and I'm trying to understand how the memory cache
> works with sql server (I think Oracle calls it the "Block Buffer")
> I fire up sql server
> I query a table (ie select * from mytable where myid = 10)
> This query's plan ends up in cache?
> Do the returned rows end up there too?
> Does it matter if myid is an index or not for the cache to utilised?
> If the query did not have a where clause, would the entire table end up in
> memory?
> Sorry for the dumb questions... just trying learn
> TIA
>|||Some additional information inline...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Maybe. SQL Server caches plans to avoid the compilation costs. However,
trivial plans (probably
> this one) are not cached since little work is needed to compile.
Check the syscacheobjects table. Cotains one row for each plan. You can see
how much a plan has been
reused, type of plan etc.

>
> Data are read into the buffer cache. Execution plans are kept in separate plan ca
che.
By "Data", Dan is referring to data pages as well as index pages.

>
> No.
>
> If there is sufficient buffer cache, yes. If not, cache is reused accordi
ng to (roughly) a LRU
> algorithm so that the most recent and frequently accessed data stay in cac
he.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message news:UJednWzblJWP4z7eR
Vn-qA@.comcast.com...
>|||Thanks guys
Some additional questions
What does the cache hit ratio relate to? The data or the index pages being
in memory? Or both?
By LRU algorithm, you mean the lazy writer process, correct? From my
reading, it seems that this sweeps through and ages out stale queries from
the query cache to make room for new queries. At the same time, does it age
out the data and index pages from their data and index caches that were
generated from the aged out query?
You guys are a great help. I struggle a lot because I'm more of a visual
learner (ie I need to see process flows rather than just read text). I
can't seem to find good material.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> Some additional information inline...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
However, trivial plans (probably[vbcol=seagreen]
> Check the syscacheobjects table. Cotains one row for each plan. You can
see how much a plan has been
> reused, type of plan etc.
>
separate plan cache.[vbcol=seagreen]
> By "Data", Dan is referring to data pages as well as index pages.
>
in[vbcol=seagreen]
according to (roughly) a LRU[vbcol=seagreen]
cache.[vbcol=seagreen]
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...[vbcol=seagreen]
in[vbcol=seagreen]
>|||> What does the cache hit ratio relate to? The data or the index pages beingn">
> in memory? Or both?
In perfmon, you have cache hit ration counters for data/index pages as well
as for plan pages.

> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries fro
m
> the query cache to make room for new queries. At the same time, does it a
ge
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
Yes. Each page has a cost associated. A bit simplified, we can say that each
reference to a page
increases the cost by 1. Each time Lazywriter sweeps, the cost for each page
decreases by 1. When
cost goes to 0, the page goes in the free list. The algorithm for plan pages
is a bit different, but
the same principal applies. So apart for the costing algorithms, data/index
pages are handled the
same way as plan pages.

> I
> can't seem to find good material.
I have a feeling that "Inside SQL Server" from MS Press, by Kalen Delaney ha
s some good info on
this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:kqSdnZDRD4fItDnenZ2dnUVZ_vidnZ2d@.co
mcast.com...
> Thanks guys
> Some additional questions
> What does the cache hit ratio relate to? The data or the index pages bei
ng
> in memory? Or both?
> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries fro
m
> the query cache to make room for new queries. At the same time, does it a
ge
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
> You guys are a great help. I struggle a lot because I'm more of a visual
> learner (ie I need to see process flows rather than just read text). I
> can't seem to find good material.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> However, trivial plans (probably
> see how much a plan has been
> separate plan cache.
> in
> according to (roughly) a LRU
> cache.
> news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> in
>