My data_engine queue is backing up but I have already increased my data_engine bulksize as well as made my data_engine multi threaded. What do I do now?

Document ID : KB000037377
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

I have followed the directions in the data_engine best practice guide:tec000004820. However my data_engine queue is still periodically backing up.
what else can I look at?

 

Answer:

At this point we will need to engage the SQL server DBA and the operating system Admin to look at performance on the back end.
You can check the data_engine logs for below samples messages:
Line 309: Jan 28 05:22:40:939 [46756] de: Commit - inserted 542 rows to RN_QOS_DATA_0012 in 59894 ms(ms/r:110)
Line 364: Jan 28 05:23:03:167 [46756] de: Commit - inserted 545 rows to RN_QOS_DATA_0003 in 22132 ms (ms/r:40)
Line 428: Jan 28 05:23:27:627 [46756] de: Commit - inserted 267 rows to RN_QOS_DATA_0002 in 24401 ms (ms/r:91)
Line 436: Jan 28 05:23:31:100 [46756] de: Commit - inserted 87 rows to RN_QOS_DATA_0007 in 3420 ms (ms/r:39)
Line 439: Jan 28 05:23:31:609 [46756] de: Commit - inserted 21 rows to RN_QOS_DATA_0149 in 456 ms (ms/r:21)
Line 440: Jan 28 05:23:32:134 [46756] de: Commit - inserted 21 rows to RN_QOS_DATA_0154 in 420 ms (ms/r:20)
Line 444: Jan 28 05:23:32:882 [46756] de: Commit - inserted 21 rows to RN_QOS_DATA_0158 in 663 ms (ms/r:31)
Line 745: Jan 28 05:24:44:473 [46756] de: Commit - inserted 774 rows to RN_QOS_DATA_0119 in 23017 ms (ms/r:29)
Line 919: Jan 28 05:25:45:820 [46756] de: Commit - inserted 1240 rows to RN_QOS_DATA_0017 in 54442 ms (ms/r:43)

if you HIGH number in the time to insert this would indicate a performance issue on the back end SQL server.A well performing SQL server should be able to insert thousands of rows per second.Additional topic to discus with System and DBA admins:1) CPU utilization. Best practice should be less than 6-% normally
2) Memory utilization: Best practice should be less than 90% normally
3) Disk queue: Should always be less than 1
4) also check to make sure your database is in simple recovery mode. ( this is strictly for performance)
5) check that the database is not set to grow in too small of chunks. Logs and data files size should be at least 500 MBs