sysprocesses.memusage are the pages 8K?

I have found that we have a old VB programme executing raw sql that is using
over 95% of our procedure cache (using sysprocesses.memusage).
All our other apps use stored procs.

However I wanted to understand this figure better. BOL describes
sysprocesses.memusage as the number of pages in the procedure cache.
Are these pages are 8K?

When I run

select KB =  sum(memusage) * 8,
MB = (sum(memusage) * 8)/1024,
GB = (sum(memusage) * 8)/(1024* 1024)
from master..sysprocesses

KB          MB          GB
----------- ----------- -----------
22109720    21591       21

We have a 4GB server that is running fine.
We cannot have 21GB in the proc cache.
Is BOL wrong? Is memusage in bytes?


Regards
Paul Cahill

The best way to tell how much the procedure cache is using is to run DBCC
MEMORYSTATUS.  See here for details:
http://support.microsoft.com/?id=271624

In the Procedure Cache section it will list the number of pages used.  To
find out how much memory in MB's that is used you use this:

(Pages * 8192) / 1024 / 1024



--
Andrew J. Kelly  SQL MVP


"Paul Cahill" <anon@anon.com> wrote in message
news:uS6AE1Z9FHA.1184@TK2MSFTNGP12.phx.gbl...
>
> I have found that we have a old VB programme executing raw sql that is
> using over 95% of our procedure cache (using sysprocesses.memusage).
> All our other apps use stored procs.
>
> However I wanted to understand this figure better. BOL describes
> sysprocesses.memusage as the number of pages in the procedure cache.
> Are these pages are 8K?
>
> When I run
>
> select KB =  sum(memusage) * 8,
>         MB = (sum(memusage) * 8)/1024,
>         GB = (sum(memusage) * 8)/(1024* 1024)
> from master..sysprocesses
>
> KB          MB          GB
> ----------- ----------- -----------
> 22109720    21591       21
>
> We have a 4GB server that is running fine.
> We cannot have 21GB in the proc cache.
> Is BOL wrong? Is memusage in bytes?
>
>
> Regards
> Paul Cahill
>
>

Thanks Andrew

The article got me looking at syscacheobjects.
It seems that most of my dups in cache are due to raw SQL from a VB app the
rest are from two c++ apps using odbc and SQLExecDirect.
Niether are using bind variables, they are building up the where clauses,
exec storedproc @var = 'literal'  etc in their execute string.

Our main app used by 500 people shows that query plans are being shared
nicely.

I'm still not sure why
dbcc memorystatus gives
Procedure Cache                Value
------------------------------ -----------
TotalProcs                     7469
TotalPages                     41059
InUsePages                     25526

and yet sum(memusage) gives 2870291

Paul



"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:%23v2s2ma9FHA.2264@tk2msftngp13.phx.gbl...
> The best way to tell how much the procedure cache is using is to run DBCC
> MEMORYSTATUS.  See here for details:
> http://support.microsoft.com/?id=271624
>
> In the Procedure Cache section it will list the number of pages used.  To
> find out how much memory in MB's that is used you use this:
>
> (Pages * 8192) / 1024 / 1024
>
>
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "Paul Cahill" <anon@anon.com> wrote in message
> news:uS6AE1Z9FHA.1184@TK2MSFTNGP12.phx.gbl... 
>
>

Yes adhoc SQL is the biggest cause of procedure cache bloat and poor
performance.  SQL2005 has some new features that help to mitigate that but
the real solution is to parameterize the queries or better yet use stored
procedures.  As for the memusage I think that there is a lot of overlap in
that many user processes share the same page(s). There are also execution
plans in syscacheobjects that get reused and even dropped and recreated. It
is a constantly moving target when you have lots of activity and poor plan
reuse. I have never found a really useful case for memusage directly from
sysprocesses.  SyscacheObjects also has a column to tell you how many bytes
each plan takes up as well.

--
Andrew J. Kelly  SQL MVP


"Paul Cahill" <anon@anon.com> wrote in message
 news:%23DWy9%23b9FHA
.4084@TK2MSFTNGP10.phx.gbl...
> Thanks Andrew
>
> The article got me looking at syscacheobjects.
> It seems that most of my dups in cache are due to raw SQL from a VB app
> the rest are from two c++ apps using odbc and SQLExecDirect.
> Niether are using bind variables, they are building up the where clauses,
> exec storedproc @var = 'literal'  etc in their execute string.
>
> Our main app used by 500 people shows that query plans are being shared
> nicely.
>
> I'm still not sure why
> dbcc memorystatus gives
> Procedure Cache                Value
> ------------------------------ -----------
> TotalProcs                     7469
> TotalPages                     41059
> InUsePages                     25526
>
> and yet sum(memusage) gives 2870291
>
> Paul
>
>
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:%23v2s2ma9FHA.2264@tk2msftngp13.phx.gbl... 
>
>