When I run sp_configure "procedure cache size" I got a table about
procedure cash (parameter name, default, ...). How can i get config
value of procedure cache size using sql query?

On May 29, 12:30*pm, Bum...@gmail.com wrote:
> When I run sp_configure "procedure cache size" I got a table about
> procedure cash (parameter name, default, ...). How can i get config
> value of procedure cache size using sql query?

you can grab the sql from sp_configure or directly query sysconfigures
and syscurconfigs tables in master database where name like
"%procedure cache%" , here is the sample..


select "Parameter Name" = convert(char(30), name),
"Default" = convert(char(11), space(11-char_length(
convert(varchar(11), defvalue)))+
convert(varchar(11), defvalue)),
Memory Used" = convert(char(11), space(11-char_length(
convert(varchar(11), b.comment)))+
convert(varchar(11), b.comment)),
"Config Value" =convert(char(11), space(11-char_length(
isnull(a.value2, convert(char(32), a.value)))) +
isnull(a.value2, convert(char(32), a.value))),
"Run Value" = convert(char(11), space(11-char_length(
isnull(b.value2, convert(char(32), b.value)))) +
isnull(b.value2, convert(char(32), b.value))),
"Unit" = convert(char(20), b.unit),
"Type" = convert(char(10), b.type)
from master.dbo.sysconfigures a,
master.dbo.syscurconfigs b
where
a.config *= b.config
and name like "%" + "procedure cache" + "%"
and parent != 19
and a.config != 19
order by name

-HTH
Manish Negandhi
[TeamSybase]

And how can i update config value of procedure cash size using sql
query?

On May 29, 5:07*pm, Bum...@gmail.com wrote:
> And how can i update config value of procedure cash size using sql
> query?

Updating system tables directly is not recommended. You can use
sp_configure to change value of procedure cache, because of which
system tables will automatically get updated

-HTH
Manish Negandhi
[TeamSybase]