procedure cache is reaching 100%

[Former Member]

One of Sybase sever running on older version having this abnormal issue.procedure cache size usage is growing gradually upto 100% suddenly in every 2-3 days and server got hung finally. application updation is managed by batch job in every end of day.

We didn't find anything abnormal and this was happened sometime in non-business hours when there is no activity running on server also

Any advice on this type of problem

1> sp_monitorconfig "all"

2>

Name Num_free Num_active Pct_act Max_Used Num_Reuse

------------------------- ----------- ----------- ------- ----------- -----------

additional network memory 472860 1563904 76.78 1563904 0

audit queue size 100 0 0.00 0 0

disk i/o structures 0 10382 100.00 10485 0

heap memory per user 4096 0 0.00 0 0

max cis remote connection 3200 0 0.00 0 0

max memory 351520 8037088 95.81 8037088 0

max number network listen 4 1 20.00 1 0

max online engines 0 8 100.00 8 0

memory per worker process 796 228 22.27 228 0

number of alarms 50 10 16.67 14 0

number of aux scan descri 400 0 0.00 50 0

number of devices 16 14 46.67 14 0

number of dtx participant 500 0 0.00 0 0

number of java sockets 817 0 0.00 0 0

number of large i/o buffe 6 0 0.00 1 0

number of locks 298624 1376 0.46 29781 0

number of mailboxes 128 128 50.00 128 0

number of messages 64 0 0.00 0 0

number of open databases 9 16 64.00 16 0

number of open indexes 7807 4193 34.94 4228 0

number of open objects 32826 8134 19.86 13600 0

number of remote connecti 20 0 0.00 3 0

number of remote logins 20 0 0.00 3 0

number of remote sites 9 1 10.00 2 0

number of sort buffers 32767 0 0.00 99542 0

number of user connection 590 210 26.25 257 0

number of worker processe 128 0 0.00 0 0

partition groups 1024 0 0.00 0 0

permission cache entries 25 7 21.88 10 870651

procedure cache size 2335419 2664581 53.29 2676618 0

size of global fixed heap 300 0 0.00 0 0

size of process object he 3000 0 0.00 0 0

size of shared class heap 3072 0 0.00 0 0

size of unilib cache 200816 720 0.36 720 0

txn to pss ratio 12800 0 0.00 0 0

(return status = 0)

Here is cache details as below

1> sp_helpcache

2>

Cache Name Config Size Run Size Overhead

------------------------ ------------- ---------- ----------

default data cache 4096.00 Mb 4096.00 Mb 432.16 Mb

Memory Available For Memory Configured

Named Caches To Named Caches

-------------------- ----------------

4096.01 Mb 4096.00 Mb

------------------ Cache Binding Information: ------------------

Cache Name Entity Name Type Index Name Status

---------- ----------- ---- ---------- ------

(return status = 0)

1> sp_cacheconfig

2>

Cache Name Status Type Config Value Run Value

------------------------------ --------- -------- ------------ ------------

default data cache Active Default 4096.00 Mb 4096.00 Mb

------------ ------------

Total 4096.00 Mb 4096.00 Mb

==========================================================================

Cache: default data cache, Status: Active, Type: Default

Config Size: 4096.00 Mb, Run Size: 4096.00 Mb

Config Replacement: strict LRU, Run Replacement: strict LRU

Config Partition: 8, Run Partition: 8

IO Size Wash Size Config Size Run Size APF Percent

-------- --------- ------------ ------------ -----------

2 Kb 491520 Kb 0.00 Mb 3072.00 Mb 10

16 Kb 209664 Kb 1024.00 Mb 1024.00 Mb 10

1> sp_configure "procedure"

2>

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

allow procedure grouping 1 0 1 1 switch dynamic

procedure cache size 3271 10898890 5000000 5000000 memory pages(2k) dynamic

(1 row affected)

(return status = 1)

Thanks

April

[Former Member]

Thank you Mark,

I did restart the dataserver 4 days back and found the procedure cache size was normal and after four days it was increased by 80% and keep growing.

The des output are around 7000. But it is difficult to find out the culprit id because most of the LWP are created in tempdb , see the details below

ObjectID OwnerUID DBID PlanID MemUsageKB CompileDate ObjectName ObjectType OwnerName DBName

----------- ----------- ----------- ----------- ----------- -------------------------- ------------------------------ -------------------------------- ------------------------------ ------------------------------

545181894 2 2 37538764 12 Sep 24 2014 11:19AM *ss0545181894_1007887378ss* stored procedure NULL tempdb

384517355 2 2 37542454 8 Sep 24 2014 11:19AM *ss0384517355_1226778695ss* stored procedure NULL tempdb

49128072 2 2 37532588 8 Sep 24 2014 11:18AM *ss0049128072_0273031494ss* stored procedure NULL tempdb

1209464189 2 2 37530866 8 Sep 24 2014 11:18AM *ss1209464189_1680486015ss* stored procedure NULL tempdb

127748364 2 2 37533924 8 Sep 24 2014 11:18AM *ss0127748364_1276476232ss* stored procedure NULL tempdb

634842130 2 2 37529460 8 Sep 24 2014 11:18AM *ss0634842130_1702437932ss* stored procedure NULL tempdb

1916994762 2 2 37536804 8 Sep 24 2014 11:19AM *ss1916994762_0336931654ss* stored procedure NULL tempdb

1662321788 2 2 37529328 12 Sep 24 2014 11:18AM *ss1662321788_1128077887ss* stored procedure NULL tempdb

994563483 2 2 37537490 14 Sep 24 2014 11:19AM *ss0994563483_1023412305ss* stored procedure NULL tempdb

1926758823 2 2 37539726 8 Sep 24 2014 11:19AM *ss1926758823_0473373727ss* stored procedure NULL tempdb

Is any other way out to track down this problematic application?

The application team are planning to upgrade to 15.7 version. Will it be helpful?

Thanks

April

[Former Member]

April,

What is the version from select @@version, what is the number of engine in the configuration file could you post the configuration.

Niclas

[Former Member]

Hi,

Here is the engine details

Group: Processors

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

max online Q engines 0 0 0 0 number static

max online engines 1 8273 8 8 number static

number of Q engines at startup 0 0 0 0 number static

number of engines at startup 1 0 8 8 number static

procedure cache size usage and disk i/o structures are growing gradually upto 100% suddenly in every 2-3 days and server got hung finally.

@@version Adaptive Server Enterprise/12.5.4/EBF 13381/P/Sun_svr4/OS 5.8/ase1254/2006/64-bit/

here is the other configuration details

Group: Configuration Options

Group: Backup/Recovery

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

allow remote access 1 0 1 1 switch dynamic

max concurrently recovered db 0 0 0 0 number dynamic

number of checkpoint tasks 1 0 1 1 number dynamic

print recovery information 0 0 0 0 switch dynamic

recovery interval in minutes 5 0 5 5 minutes dynamic

tape retention in days 0 0 0 0 days dynamic

(1 row affected)

Group: Cache Manager

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

extended cache size 0 0 0 0 memory pages(2k) dynamic

global async prefetch limit 10 0 10 10 percent dynamic

global cache partition number 1 0 8 8 number static

memory alignment boundary 2048 0 2048 2048 bytes static

number of index trips 0 0 0 0 number dynamic

number of oam trips 0 0 0 0 number dynamic

total data cache size 0 4636834 0 4636834 kilobytes read-only

Group: Component Integration Services

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

cis bulk insert array size 50 0 50 50 rows dynamic

cis bulk insert batch size 0 0 0 0 rows dynamic

cis connect timeout 0 0 0 0 seconds dynamic

cis cursor rows 50 0 50 50 number dynamic

cis packet size 512 0 512 512 bytes dynamic

cis rpc handling 0 0 0 0 switch dynamic

enable cis 1 0 1 1 switch static

enable file access 0 0 0 0 switch dynamic

enable full-text search 0 0 0 0 switch dynamic

enable snmp 0 0 0 0 switch dynamic

max cis remote connections 0 2728 0 0 number dynamic

Group: DTM Administration

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

dtm detach timeout period 0 0 0 0 minutes dynamic

dtm lock timeout period 300 0 300 300 seconds dynamic

enable DTM 0 0 0 0 switch static

enable xact coordination 1 0 1 1 switch static

number of dtx participants 500 170 500 500 number dynamic

strict dtm enforcement 0 0 0 0 switch static

txn to pss ratio 16 14888 16 16 number dynamic

xact coordination interval 60 0 60 60 seconds dynamic

Group: Diagnostics

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

average cap size 200 0 200 200 bytes static

caps per ccb 50 0 50 50 number static

dump on conditions 0 0 0 0 switch dynamic

maximum dump conditions 10 0 10 10 number static

number of ccbs 0 0 0 0 number static

number of dump threads 1 0 1 1 number dynamic

Group: Disk I/O

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

allow sql server async i/o 1 0 1 1 switch static

disable disk mirroring 1 0 1 1 switch static

disk i/o structures 256 250 1024 1024 number dynamic

number of devices 10 #26 30 30 number dynamic

number of large i/o buffers 6 3138 6 6 number dynamic

page utilization percent 95 0 95 95 percent dynamic

Group: Error Log

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

event log computer name LocalSystem 0 LocalSystem LocalSystem name dynamic

event logging 1 0 1 1 switch dynamic

log audit logon failure 0 0 0 0 switch dynamic

log audit logon success 0 0 0 0 switch dynamic

Group: Extended Stored Procedure

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

esp execution priority 8 0 8 8 id dynamic

esp execution stacksize 139264 0 139264 139264 bytes static

esp unload dll 0 0 0 0 switch dynamic

start mail session 0 0 0 0 switch dynamic

xp_cmdshell context 1 0 1 1 switch dynamic

Group: General Information

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

configuration file 0 0 0 /sybase/syb name dynamic

Group: Java Services

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

enable enterprise java beans 0 0 0 0 switch dynamic

enable java 0 0 0 0 switch static

number of java sockets 0 0 0 0 number dynamic

size of global fixed heap 300 0 300 300 memory pages(2k) dynamic

size of process object heap 3000 0 3000 3000 memory pages(2k) dynamic

size of shared class heap 3072 0 3072 3072 memory pages(2k) dynamic

Group: Languages

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

default character set id 1 0 1 1 id static

default language id 0 0 0 0 id dynamic

default sortorder id 50 0 50 50 id static

disable character set conversi 0 0 0 0 switch static

enable unicode conversions 0 0 0 0 switch dynamic

Group: Lock Manager

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

deadlock checking period 500 0 800 800 milliseconds dynamic

deadlock retries 5 0 5 5 number dynamic

lock address spinlock ratio 100 0 100 100 ratio static

lock hashtable size 2048 76 2048 2048 bytes static

lock scheme allpages 0 allpages allpages name dynamic

lock spinlock ratio 85 0 85 85 ratio static

lock table spinlock ratio 20 0 20 20 ratio static

lock wait period 2147483647 0 2147483647 2147483647 seconds dynamic

number of locks 5000 70678 300000 300000 number dynamic

page lock promotion HWM 200 0 200 200 number dynamic

page lock promotion LWM 200 0 200 200 number dynamic

page lock promotion PCT 100 0 100 100 percent dynamic

print deadlock information 0 0 1 1 switch dynamic

read committed with lock 0 0 0 0 switch dynamic

row lock promotion HWM 200 0 200 200 number dynamic

row lock promotion LWM 200 0 200 200 number dynamic

row lock promotion PCT 100 0 100 100 percent dynamic

Group: Memory Use

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

additional network memory 0 0 0 0 bytes dynamic

allocate max shared memory 0 0 0 0 switch dynamic

allow resource limits 0 #4 0 0 switch static

audit queue size 100 2 100 100 number dynamic

average cap size 200 0 200 200 bytes static

caps per ccb 50 0 50 50 number static

compression memory size 0 152 0 0 memory pages(2k) dynamic

deadlock pipe max messages 0 0 0 0 number dynamic

default network packet size 512 #1435 512 512 bytes static

disk i/o structures 256 250 1024 1024 number dynamic

dynamic allocation on demand 1 0 1 1 switch dynamic

enable rep agent threads 0 0 0 0 switch dynamic

errorlog pipe max messages 0 0 0 0 number dynamic

event buffers per engine 100 #82 100 100 number static

executable codesize + overhead 0 59711 0 59711 kilobytes read-only

heap memory per user 4096 0 4096 4096 bytes dynamic

lock hashtable size 2048 76 2048 2048 bytes static

lock spinlock ratio 85 0 85 85 ratio static

max SQL text monitored 0 3248 4096 4096 bytes static

max memory 48128 16777216 8388608 8388608 memory pages(2k) dynamic

max number network listeners 5 1074 5 5 number dynamic

max online Q engines 0 0 0 0 number static

max online engines 1 8273 8 8 number static

memory per worker process 1024 246 1024 1024 bytes dynamic

messaging memory 400 0 400 400 memory pages(2k) dynamic

number of alarms 40 8 60 60 number dynamic

number of aux scan descriptors 200 #570 400 400 number dynamic

number of ccbs 0 0 0 0 number static

number of devices 10 #26 30 30 number dynamic

number of dtx participants 500 170 500 500 number dynamic

number of java sockets 0 0 0 0 number dynamic

number of large i/o buffers 6 3138 6 6 number dynamic

number of locks 5000 70678 300000 300000 number dynamic

number of mailboxes 30 14 256 256 number dynamic

number of messages 64 3 64 64 number dynamic

number of open databases 12 3368 25 25 number dynamic

number of open indexes 500 16130 12000 12000 number dynamic

number of open objects 500 63760 40960 40960 number dynamic

number of remote connections 20 35 20 20 number static

number of remote logins 20 23 20 20 number static

number of remote sites 10 2153 10 10 number static

number of user connections 25 171951 800 800 number dynamic

number of worker processes 0 27685 128 128 number dynamic

open index hash spinlock ratio 100 0 100 100 ratio dynamic

open index spinlock ratio 100 0 100 100 ratio dynamic

partition groups 1024 940 1024 1024 number dynamic

partition spinlock ratio 10 0 10 10 ratio dynamic

permission cache entries 15 #5858 32 32 number dynamic

plan text pipe max messages 0 0 0 0 number dynamic

procedure cache size 3271 10898890 5000000 5000000 memory pages(2k) dynamic

process wait events 0 0 1 1 switch dynamic

remote server pre-read packets 3 #33 3 3 number static

size of global fixed heap 300 0 300 300 memory pages(2k) dynamic

size of process object heap 3000 0 3000 3000 memory pages(2k) dynamic

size of shared class heap 3072 0 3072 3072 memory pages(2k) dynamic

size of unilib cache 0 230 0 0 bytes dynamic

sql text pipe max messages 0 0 0 0 number dynamic

stack guard size 4096 #3940 4096 4096 bytes static

stack size 94208 #90620 94208 94208 bytes static

statement cache size 0 111604 51200 51200 memory pages(2k) dynamic

statement pipe max messages 0 0 0 0 number dynamic

total data cache size 0 4636834 0 4636834 kilobytes read-only

total logical memory 48128 16074176 8037088 8040482 memory pages(2k) read-only

total physical memory 0 16074176 0 8037088 memory pages(2k) read-only

txn to pss ratio 16 14888 16 16 number dynamic

user log cache size 2048 0 4096 4096 bytes static

user log cache spinlock ratio 20 0 20 20 ratio dynamic

wait event timing 0 0 1 1 switch dynamic

Group: Meta-Data Caches

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

number of open databases 12 3368 25 25 number dynamic

number of open indexes 500 16130 12000 12000 number dynamic

number of open objects 500 63760 40960 40960 number dynamic

open index hash spinlock ratio 100 0 100 100 ratio dynamic

open index spinlock ratio 100 0 100 100 ratio dynamic

open object spinlock ratio 100 0 100 100 ratio dynamic

partition groups 1024 940 1024 1024 number dynamic

partition spinlock ratio 10 0 10 10 ratio dynamic

Group: Monitoring

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

SQL batch capture 0 0 1 1 switch dynamic

deadlock pipe active 0 0 0 0 switch dynamic

deadlock pipe max messages 0 0 0 0 number dynamic

enable monitoring 0 0 0 0 switch dynamic

errorlog pipe active 0 0 0 0 switch dynamic

errorlog pipe max messages 0 0 0 0 number dynamic

max SQL text monitored 0 3248 4096 4096 bytes static

object lockwait timing 0 0 1 1 switch dynamic

per object statistics active 0 0 1 1 switch dynamic

performance monitoring option 0 0 0 0 switch dynamic

plan text pipe active 0 0 0 0 switch dynamic

plan text pipe max messages 0 0 0 0 number dynamic

process wait events 0 0 1 1 switch dynamic

sql text pipe active 0 0 0 0 switch dynamic

sql text pipe max messages 0 0 0 0 number dynamic

statement pipe active 0 0 0 0 switch dynamic

statement pipe max messages 0 0 0 0 number dynamic

statement statistics active 0 0 1 1 switch dynamic

wait event timing 0 0 1 1 switch dynamic

Group: Network Communication

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

additional network memory 0 0 0 0 bytes dynamic

allow remote access 1 0 1 1 switch dynamic

allow sendmsg 0 0 0 0 switch dynamic

default network packet size 512 #1435 512 512 bytes static

max network packet size 512 0 1024 1024 bytes static

max number network listeners 5 1074 5 5 number dynamic

number of remote connections 20 35 20 20 number static

number of remote logins 20 23 20 20 number static

number of remote sites 10 2153 10 10 number static

remote server pre-read packets 3 #33 3 3 number static

send doneinproc tokens 1 0 1 1 switch dynamic

syb_sendmsg port number 0 0 0 0 id static

tcp no delay 1 0 1 1 switch static

Group: O/S Resources

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

max async i/os per engine 2147483647 0 2147483647 2147483647 number static

max async i/os per server 2147483647 0 2147483647 2147483647 number static

max native threads per engine 50 0 50 50 number dynamic

o/s file descriptors 0 0 0 8192 number read-only

tcp no delay 1 0 1 1 switch static

Group: Parallel Query

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

max parallel degree 1 0 1 1 number dynamic

max scan parallel degree 1 0 1 1 number dynamic

memory per worker process 1024 246 1024 1024 bytes dynamic

number of worker processes 0 27685 128 128 number dynamic

Group: Physical Resources

Group: Physical Memory

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

additional network memory 0 0 0 0 bytes dynamic

allocate max shared memory 0 0 0 0 switch dynamic

compression memory size 0 152 0 0 memory pages(2k) dynamic

dynamic allocation on demand 1 0 1 1 switch dynamic

heap memory per user 4096 0 4096 4096 bytes dynamic

lock shared memory 0 0 0 0 switch static

max memory 48128 16777216 8388608 8388608 memory pages(2k) dynamic

messaging memory 400 0 400 400 memory pages(2k) dynamic

shared memory starting address 0 0 0 0 not applicable static

total logical memory 48128 16074176 8037088 8040482 memory pages(2k) read-only

total physical memory 0 16074176 0 8037088 memory pages(2k) read-only

Group: Processors

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

max online Q engines 0 0 0 0 number static

max online engines 1 8273 8 8 number static

number of Q engines at startup 0 0 0 0 number static

number of engines at startup 1 0 8 8 number static

Group: Rep Agent Thread Administration

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

enable rep agent threads 0 0 0 0 switch dynamic

Group: SQL Server Administration

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

SQL Perfmon Integration 1 0 1 1 switch static

abstract plan cache 0 0 0 0 switch dynamic

abstract plan dump 0 0 0 0 switch dynamic

abstract plan load 0 0 0 0 switch dynamic

abstract plan replace 0 0 0 0 switch dynamic

allow backward scans 1 0 1 1 switch dynamic

allow nested triggers 1 0 1 1 switch static

allow resource limits 0 #4 0 0 switch static

allow updates to system tables 0 0 1 1 switch dynamic

cpu accounting flush interval 200 0 200 200 clock ticks dynamic

cpu grace time 500 0 500 500 clock ticks dynamic

deadlock retries 5 0 5 5 number dynamic

default database size 2 0 2 2 megabytes dynamic

default exp_row_size percent 5 0 5 5 percent dynamic

default fill factor percent 0 0 0 0 percent dynamic

enable DTM 0 0 0 0 switch static

enable HA 0 0 0 0 switch static

enable housekeeper GC 1 0 1 1 switch dynamic

enable job scheduler 0 0 0 0 switch dynamic

enable real time messaging 0 0 0 0 switch dynamic

enable sort-merge join and JTC 0 0 0 0 switch dynamic

enable webservices 0 0 0 0 switch dynamic

enable xml 0 0 0 0 switch dynamic

event buffers per engine 100 #82 100 100 number static

histogram tuning factor 1 0 1 1 number dynamic

housekeeper free write percent 1 0 8 8 percent dynamic

i/o accounting flush interval 1000 0 1000 1000 clock ticks dynamic

i/o batch size 100 0 100 100 number dynamic

i/o polling process count 10 0 10 10 number dynamic

identity burning set factor 5000 0 5000 5000 number static

identity grab size 1 0 1 1 number dynamic

job scheduler interval 1 0 1 1 seconds dynamic

job scheduler tasks 32 0 32 32 number dynamic

license information 25 0 25 25 number dynamic

maximum job output 32768 0 32768 32768 bytes dynamic

number of alarms 40 8 60 60 number dynamic

number of aux scan descriptors 200 #570 400 400 number dynamic

number of histogram steps 20 0 20 20 number dynamic

number of large i/o buffers 6 3138 6 6 number dynamic

number of mailboxes 30 14 256 256 number dynamic

number of messages 64 3 64 64 number dynamic

number of open databases 12 3368 25 25 number dynamic

number of open objects 500 63760 40960 40960 number dynamic

number of pre-allocated extent 2 0 16 16 number dynamic

number of sort buffers 500 0 32767 32767 number dynamic

page lock promotion HWM 200 0 200 200 number dynamic

page lock promotion LWM 200 0 200 200 number dynamic

page lock promotion PCT 100 0 100 100 percent dynamic

percent database for history 20 0 20 20 percent dynamic

percent database for output 30 0 30 30 percent dynamic

percent history free 30 0 30 30 percent dynamic

percent output free 50 0 50 50 percent dynamic

print deadlock information 0 0 1 1 switch dynamic

procedure cache size 3271 10898890 5000000 5000000 memory pages(2k) dynamic

row lock promotion HWM 200 0 200 200 number dynamic

row lock promotion LWM 200 0 200 200 number dynamic

row lock promotion PCT 100 0 100 100 percent dynamic

rtm thread idle wait period 600 0 600 600 seconds dynamic

runnable process search count 2000 0 2000 2000 number dynamic

sampling percent 0 0 0 0 percent dynamic

size of auto identity column 10 0 10 10 bytes dynamic

sql server clock tick length 100000 0 100000 100000 microseconds static

statement cache size 0 111604 51200 51200 memory pages(2k) dynamic

text prefetch size 16 0 16 16 logical pages dynamic

time slice 100 0 100 100 milliseconds dynamic

upgrade version 12500 0 12500 12500 id dynamic

Group: Security Related

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

allow procedure grouping 1 0 1 1 switch dynamic

audit queue size 100 2 100 100 number dynamic

auditing 0 0 0 0 switch dynamic

check password for digit 0 0 0 0 switch dynamic

curread change w/ open cursors 1 0 1 1 switch dynamic

current audit table 1 0 1 1 id dynamic

enable encrypted columns 0 2 0 0 switch static

enable ldap user auth 0 0 0 0 not applicable dynamic

enable pam user auth 0 0 0 0 number dynamic

enable row level access 0 0 0 0 switch dynamic

enable ssl 0 0 0 0 switch static

maximum failed logins 0 0 0 0 number dynamic

minimum password length 6 0 6 6 bytes dynamic

msg confidentiality reqd 0 0 0 0 switch dynamic

msg integrity reqd 0 0 0 0 switch dynamic

secure default login guest 0 guest guest not applicable dynamic

select on syscomments.text 1 0 1 1 switch dynamic

suspend audit when device full 1 0 1 1 switch dynamic

systemwide password expiration 0 0 0 0 days dynamic

unified login required 0 0 0 0 switch dynamic

use security services 0 0 0 0 switch static

Group: Unicode

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

default unicode sortorder binary 0 binary binary name static

enable surrogate processing 1 0 1 1 switch dynamic

enable unicode conversions 0 0 0 0 switch dynamic

enable unicode normalization 1 0 1 1 switch dynamic

size of unilib cache 0 230 0 0 bytes dynamic

Group: User Environment

Parameter Name Default Memory Used Config Value Run Value Unit Type

------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------

default network packet size 512 #1435 512 512 bytes static

number of user connections 25 171951 800 800 number dynamic

permission cache entries 15 #5858 32 32 number dynamic

stack guard size 4096 #3940 4096 4096 bytes static

stack size 94208 #90620 94208 94208 bytes static

user log cache size 2048 0 4096 4096 bytes static

user log cache spinlock ratio 20 0 20 20 ratio dynamic

(return status = 0)

Thanks

April