Check performance
We can check the performance of the ASE engine by using the sp_sysmon procedure. This procedure will monitor the engine for the given time spew out a long report. This allows us to see our performance and adjust the engine where needed.
Sometimes sp_sysmon gives a direct recommendation at the bottom which is always good to looking to.
Of course it helps to start sp_sysmon when the database is under high load.
|
Type
|
Command
|
|---|---|
| Data cache | sp_sysmon “00:05:00” , dcache; |
| Procedure cache | sp_sysmon “00:05:00” , pcache; |
| Memory | sp_sysmon “00:05:00” , memory; |
| disk I/O | sp_sysmon “00:05:00” , diskio; |
Following settings are those that in my experience require the most tuning:
- stack size
- number of open indexes
- default cache size
- open objects
- open partitions
- number of user connections
- number of locks
- user log cache size
- max memory
- global cache
- default data cache
- default data cache 4K
- default data cache 16K
- network listeners
- procedure cache size
Most of these need to be set with sp_configure from inside the master database. Which values you require is depended on your ASE engine specific needs.