| Author |
sysprocesses.memusage are the pages 8K?
|
|
| Paul Cahill 2005-11-30, 7:23 am |
|
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
| |
| Andrew J. Kelly 2005-11-30, 7:23 am |
| 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
>
>
| |
| Paul Cahill 2005-11-30, 9:23 am |
| 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...
>
>
| |
| Andrew J. Kelly 2005-11-30, 8:23 pm |
| 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...
>
>
|
|
|
|