Performance Monitoring Tools for the Database Layer
Most of the enterprise-grade database engines that can be deployed with ThingWorx provide good monitoring and issue detection tools. Work with your DBA team to monitor and analyze your database along with the application-side monitoring.
The following are some of the recommended issue detection queries for PostgreSQL and Microsoft SQL databases. These databases are the most used enterprise databases for ThingWorx model data.
Postgres
Monitor the following metrics to identify resource bottlenecks on the PostgreSQL server:
• Free disk space
• CPU usage
• I/O usage
You must identify and optimize the long running queries. Such queries can block other queries and increase wait time until resources are free again. This can result in long running threads waiting for a database lock on ThingWorx. A database lock enables an individual statement to lock the current state of row or table. Too many locks affect the performance of the application.
You can use PSM to detect long running queries as well as frequently executed queries.
MSSQL
For Microsoft SQL databases, it is recommended that you collect the database statistics on nightly basis in production environments. Additionally, indexes should be reviewed for high fragmentation and rebuilt as necessary, especially if the database is not running on SSD. The MSSQL Extended Events logs should be reviewed regularly for any long wait or deadlock operations in the ThingWorx database. During periods of slow performance, the following query can identify transactions that are waiting for a resource:
SELECT t.*,r.*, SUBSTRING(t.text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), t.text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
If a statement appears suspended or is running for a longer duration, check the wait_type and wait_resource columns to identify the reason for the waiting time.
To see if the statement is waiting on any locks, identify the session ID and run:
select * from sys.dm_tran_locks where session_id = 158