In order to peek into the procedure cache, you can use the following dbcc command:
dbcc procbuf
In order to see the output on the console, use:
dbcc traceon(3604)
go
dbcc procbuf
go
You’ll see that the output is pretty extensive. If what you are after is which trigger and procedures are using space in the procedure cache and how much space it uses, you only are interested in the lines like:
... Total # of bytes used : 1266320 ... pbname='sp_aux_getsize' pbprocnum=1 ...
You can thus execute it and grep for these two lines:
$SYBASE/OCS/bin/isql -Usa -Pxxxx << EOT | grep "pbname
Total # of bytes used"
dbcc traceon(3604)
go
dbcc procbuf
go
EOT
You of course need to replace xxxx by your actual password.
Then you will want to make it looks nicer:
- Merge the two lines: awk ‘!(NR%2){print p” “$0}{p=$0}’
- Display only the name and the size: awk ‘{ print $1″ “$9″ bytes”; }’ | sed “s/pbname=//g” | sed “s/’//g”
- Sort by size: sed “s/’//g” | sort -k2 -n
Putting it all together:
$SYBASE/OCS/bin/isql -Usa -Pxxxx << EOT | grep "pbname
Total # of bytes used" | awk '!(NR%2){print p" "$0}{p=$0}' | awk '{ print $1" "$9" bytes"; }' | sed "s/pbname=//g" | sed "s/'//g" | sort -k2 -n
dbcc traceon(3604)
go
dbcc procbuf
go
EOT
You will then see something like:
... sp_jdbc_tables 62880 bytes sp_getmessage 68668 bytes sp_aux_getsize 80596 bytes sp_mda 81433 bytes sp_mda 81433 bytes sp_drv_column_default 90144 bytes sp_dbcc_run_deletehistory 133993 bytes sp_lock 180467 bytes sp_helpsegment 181499 bytes sp_dbcc_run_summaryreport 207470 bytes sp_modifystats 315854 bytes sp_autoformat 339825 bytes sp_spaceused 353572 bytes sp_jdbc_columns 380403 bytes sp_do_poolconfig 491584 bytes sp_configure 823283 bytes
I could not understand the followig part
$SYBASE/OCS/bin/isql -Usa -Pxxxx << EOT | grep "pbname Total # of bytes used"
Coul dyou please explain me clearly
Only this one line makes no sense on its own. This line just means that whatever comes in the following lines will be executed until EOT is found and then filtered by grep.