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?

book

Article ID: 37377

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

I have followed the directions in the data_engine best practice guide:


https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=33592


However my data_engine queue is still periodically backing up.

What else can I look at?

Environment

UIM 8.x or above

Resolution

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) 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 MB/s