dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthurIt seems to be pages.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegroups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegroups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>sql

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthur
It seems to be pages.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegro ups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>
|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur
|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegr oups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthurIt seems to be pages.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegroups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegroups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>

DBCC PROCCACHE Question.

What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair Company
If your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:

> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>
>
|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...[vbcol=seagreen]
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:

DBCC PROCCACHE Question.

What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair CompanyIf your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:
> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> > What exactly does this command show you. I need to know what the numbers
> > it
> > shows actually mean. I have been having SERIOUS performance issues with
> > my
> > SQL cluster and just by looking at the numbers from this command it looks
> > like my stored proc cache just grows and grows. I think...
> >
> > Are high numbers from this command a bad thing or just normal?
> >
> > Any help appreciated.
> >
> > TIA
> > Cary LaPora
> > Database Administrator
> > The Lancair Company
>
>|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
>> If that is the case look inside syscacheobjects to see if you have actual
>> plan reuse.
>> dbcc cachestats -- will show you hit ratios
>> dbcc memorystatus -- will show you how memory is divided inside SQL
>> Server
>> (for SQL Server controlled memory grants)
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Cary" <Cary@.discussions.microsoft.com> wrote in message
>> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>> > What exactly does this command show you. I need to know what the
>> > numbers
>> > it
>> > shows actually mean. I have been having SERIOUS performance issues
>> > with
>> > my
>> > SQL cluster and just by looking at the numbers from this command it
>> > looks
>> > like my stored proc cache just grows and grows. I think...
>> >
>> > Are high numbers from this command a bad thing or just normal?
>> >
>> > Any help appreciated.
>> >
>> > TIA
>> > Cary LaPora
>> > Database Administrator
>> > The Lancair Company
>>

DBCC PROCCACHE Question.

What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair CompanyIf your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what the
y
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:

> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>
>|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/defaul...kb;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...[vbcol=seagreen]
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
>sql

DBCC PROCCACHE question

How can I change procedure cache values.
Is it possible to change them only for one procedure.
If I have a huge procedure and small one I would like to allocate
values for both of them accordingly."inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0409150533.65df88b3@.posting.google.c om...
> How can I change procedure cache values.
> Is it possible to change them only for one procedure.
> If I have a huge procedure and small one I would like to allocate
> values for both of them accordingly.

You can't - MSSQL creates execution plans and manages the procedure cache
automatically. You can recompile a procedure, which forces MSSQL to create a
new plan (for example, after adding a new index), and you can empty the
entire cache, but that's about it. Check out "Execution Plan Caching and
Reuse" in Books Online for more details on how the cache works.

If you're having a specific problem, perhaps you could give some more
information, and someone may be able to suggest something.

Simon