[HANA] Parameter - service_thread_sampling_monitor_enabled

HANA Thread

• M_SERVICE_THREADS
  • Current thread
• M_SERVICE_THREADS_SAMPLES
  • History of thread
  • Retention = at least 2 hours
• HOST_SERVICE_THREAD_SAMPLES
  • Only available if embedded statistics server is used
  • Retention = 1 day ~ 42 days
  • Command
    • Deactivate data collection
      • UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET STATUS = 'Inactive' WHERE ID = 5034
    • Activate data collection
      • UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET STATUS = 'Active' WHERE ID = 5034
    • Frequency of M_SERVICE_THREAD_SAMPLES -> HOST_SERVICE_THREAD_SAMPLES
      • Default: 3600 (hourly)
      • UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET INTERVALLENGTH = <seconds> WHERE ID = 5034
    • Configure data retension with <days> days
      • Default 42 days 
      • UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET RETENTION_DAYS_CURRENT = <days> WHERE ID = 5034
    • Set the filter factor for storing historic thread samples to <filter_factor>
      • Default 50 (samples of every 50th M_SERVICE_THREAD_SAMPLES timestamp -> HOST~~
  • ex)
    • UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE 
      SET STATUS = 'Active', 
      INTERVALLENGTH = <seconds>
      RETENTION_DAYS_CURRENT = 42
      WHERE ID  = 5034;

 

 

 

Thead state

Thread state	Area	Details	SAP Note
Barrier Wait	Lock	Barrier wait	1999998
ConditionalVariable Wait	Lock	Mainly record and table lock waits	1999998
ExclusiveLock Enter	Lock	Exclusive read / write lock waits	1999998
Inactive	Idle	Idle thread
IO Wait	I/O	Disk I/O wait (e.g. during column load or hybrid LOB access)	1999930
Job Exec Waiting	Idle	Waiting for a JobWorker thread executing the actual work
Mutex Wait	Lock	Mutex lock waits	1999998
Network Connect	Network	Network I/O between SAP HANA nodes or services	2222200
Network Poll	Network	Network I/O between SAP HANA nodes or services	2222200
Network Read	Network	Network I/O read between SAP HANA nodes or services	2222200
Network Write	Network	Network I/O write between SAP HANA nodes or services	2222200
Resource Load Wait	I/O	Disk I/O wait (e.g. during undo file load, DataAccess::UndoFileAnchor::allocateFile)	1999930
Running	SQL	Request execution, typically CPU consumption	2000002
Semaphore Wait	Lock	Semaphore lock waits	1999998
SharedLock Enter	Lock	Shared read / write lock waits	1999998
Sleeping	Lock	Waiting for a lock	1999998
Speculative Lock Retry backoff	Lock	Transactional memory lock (back-off waiting on transaction retry)	1999998
Speculative Lock Wait for fallback	Lock	Transactional memory lock state (waiting on fallback lock)	1999998
Unknown	Unknown	State of 'Native' threads created outside of the standard SAP HANA context can't be determined and so they are displayed as 'Unknown' (SAP HANA >= 2.0)