cache pages > total pages

i read kalen's article, _managing memory_, and ran all the examples.
one thing i'm still confused about is how my procedure cache can be
bigger than my buffer cache. i understand that my total server memory
can be greater than my max memory size (because of the processes in
memtoleave). however, i assumed that the procedure cache was always a
subset of the buffer cache. here are the values i'm seeing:
max memory size = 92Mb = 94208Kb
sqlserver:buffer manager total pages = 11776 = 94208Kb (pages * 8k)
sqlserver:cache manager cache pages = 16778 = 134224Kb
thanks for all your help so far!
arthurone more thing: dbcc proccache shows the proc cache size at 15356 =122848Kb which is less than the amount in the sqlserver:cache manager
cache pages counter (but still more than max memory size, at 92Mb). is
there some overhead in the perfmon counter that's not accounted for in
dbcc proccache?|||Nowhere does it say that the procedure cache can not be larger than the
buffer cache that I know of. It's all dynamic and if you have lots of adhoc
sql your proc cache can get quite large. If you only read a few rows of
data your buffer cache will be small.
--
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1106669131.794910.49530@.f14g2000cwb.googlegroups.com...
>i read kalen's article, _managing memory_, and ran all the examples.
> one thing i'm still confused about is how my procedure cache can be
> bigger than my buffer cache. i understand that my total server memory
> can be greater than my max memory size (because of the processes in
> memtoleave). however, i assumed that the procedure cache was always a
> subset of the buffer cache. here are the values i'm seeing:
> max memory size = 92Mb = 94208Kb
> sqlserver:buffer manager total pages = 11776 = 94208Kb (pages * 8k)
> sqlserver:cache manager cache pages = 16778 = 134224Kb
> thanks for all your help so far!
> arthur
>|||It probably depends on how you're defining buffer cache. You can talk about
ALL (well, almost all) the memory being buffer cache, and then SQL Server
'borrow' memory from buffer cache to use for plans and other needs. That may
be why you're thinking proc cache can never be larger than buffer cache
because buffer cache must contain proc cache. But I think the values here
are just counting the actual memory used for hashed pages, that are actually
acting as a buffer cache.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106669131.794910.49530@.f14g2000cwb.googlegroups.com...
>i read kalen's article, _managing memory_, and ran all the examples.
> one thing i'm still confused about is how my procedure cache can be
> bigger than my buffer cache. i understand that my total server memory
> can be greater than my max memory size (because of the processes in
> memtoleave). however, i assumed that the procedure cache was always a
> subset of the buffer cache. here are the values i'm seeing:
> max memory size = 92Mb = 94208Kb
> sqlserver:buffer manager total pages = 11776 = 94208Kb (pages * 8k)
> sqlserver:cache manager cache pages = 16778 = 134224Kb
> thanks for all your help so far!
> arthur
>|||hmmm... i just assumed the proc cache was a subset of the buffer pool.
from windowsitpro, _minding memory_, instantdoc# 43419:
sql server uses memory from the buffer pool to support a variety of
needs including the database page cache, the plan cache, workspace
memory, and locks...sql server holds recently used query plans in the
plan cache (aka procedure or sql cache).
in any case, if the procedure cache can grow larger than the buffer
pool, then max server memory isn't really max server memory - it's
really "max server memory, and if there's any other memory left over,
we'll use that too." in any case, what i did to increase the size of
the procedure cache was run stored procedures that all have the same
body (select * from sysobjects), but different names. there are 4000 of
them, and i called each one once. does this qualify as a memory
allocation greater then 8Kb, thereby using memory from the memtoleave
region (or some combination thereof, i.e., first filling up the buffer
pool, then using memtoleave memory)?
thanks,
arthur
ps. kalen, i read your _managing memory_ article and ran the examples,
and some of my own, which is how i got to this point : )|||I believe that the confusion is that we have two different definitions of the buffer pool.
One definition is all pages in the pool. Let's call this definition "A".
Another is all pages actually in use to buffer data pages read from disk. Let's call this definition
"B".
When SQL Server is to create plans, it need to buffer them, and the proc cache actually borrows
memory from the buffer pool (definition "A"). So, of all pages in the pool, some are data pages
(buffers), some are procedure buffers and some are unused. Can you see now how the memory used for
procedure plans can be higher than the data pages actually in use to buffer data pages (definition
"B")?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"arthur" <alangham@.gmail.com> wrote in message
news:1106673109.443028.113780@.c13g2000cwb.googlegroups.com...
> hmmm... i just assumed the proc cache was a subset of the buffer pool.
> from windowsitpro, _minding memory_, instantdoc# 43419:
> sql server uses memory from the buffer pool to support a variety of
> needs including the database page cache, the plan cache, workspace
> memory, and locks...sql server holds recently used query plans in the
> plan cache (aka procedure or sql cache).
> in any case, if the procedure cache can grow larger than the buffer
> pool, then max server memory isn't really max server memory - it's
> really "max server memory, and if there's any other memory left over,
> we'll use that too." in any case, what i did to increase the size of
> the procedure cache was run stored procedures that all have the same
> body (select * from sysobjects), but different names. there are 4000 of
> them, and i called each one once. does this qualify as a memory
> allocation greater then 8Kb, thereby using memory from the memtoleave
> region (or some combination thereof, i.e., first filling up the buffer
> pool, then using memtoleave memory)?
> thanks,
> arthur
> ps. kalen, i read your _managing memory_ article and ran the examples,
> and some of my own, which is how i got to this point : )
>|||ok, i may have just answered some of my own question. it looks like i
got buffer pool and buffer cache mixed up, i.e., buffer pool != buffer
cache, buffer pool contains buffer cache - and procedure cache as well.
and, so now it makes sense that the procedure cache can grow larger
than the buffer cache. however, the sqlserver:buffer manager total
pages should be the total size of the buffer pool - and in stats above,
the procedure cache is larger than the buffer pool - and, i suppose,
that's the part that's confusing me.
arthur|||In think different people and different tools use these terms loosely. I
believe that in the stats you are referring to, when you see the proc cache
larger than the buffer pool, it is really talking about the buffer cache.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106673907.828704.172080@.c13g2000cwb.googlegroups.com...
> ok, i may have just answered some of my own question. it looks like i
> got buffer pool and buffer cache mixed up, i.e., buffer pool != buffer
> cache, buffer pool contains buffer cache - and procedure cache as well.
> and, so now it makes sense that the procedure cache can grow larger
> than the buffer cache. however, the sqlserver:buffer manager total
> pages should be the total size of the buffer pool - and in stats above,
> the procedure cache is larger than the buffer pool - and, i suppose,
> that's the part that's confusing me.
> arthur
>|||which is fine. so my proc cache is larger than my buffer cache. but my
buffer cache is capped at max memory size, yet my proc cache keeps
growing. and this is what i'm trying to understand. why is the buffer
cache bounded, but the proc cache isn't?and what is the extra memory in
the proc cache - is it memtoleave memory?