Determining the Total Number of Threads

The monThread monitor table includes information about all threads in SAP ASE.

To determine the total number of threads in SAP ASE, issue:
select count(*) from monThread

However, many threads reported from this query are from syb_blocking_pool, which do not require much CPU and need not be considered for resource planning.

Note: When you select entries for monThread (for example, counting the number of threads in the default thread pool), the number of threads SAP ASE reports may be fewer than the configured value because some threads may be changing states while SAP ASE collects the data. For example, a specific thread in the ACTIVE queue may place itself in the IDLE queue while the data collection is in progress.

Subsequent runs of the same query will provide exact results, unless some threads are transitioning their states.

During resource planning, pay particular attention to the number of query processing, or engine, threads, that run user queries. Determine the number of query processing threads by issuing select count(*) from monEngine, select count(*) from sysengines, or by adding the thread counts for syb_default_pool to the thread counts for any user-created thread pools.

sp_sysmon displays the amount of CPU resources each thread consumes. See Performance and Tuning Series: Monitoring SAP Adaptive Server with sp_sysmon.