Data engine queue is not getting released in one of our PROD shred environments due to which multiple customers are impacted.
QOS messages are backed up/queued. There are no messages being Sent.
The first symptom noticed was that the data_engine queue was severely backed up and not processing any data so the queued QOS messages kept increasing.
The second symptom noticed was that the data_engine.log was being written to by the probe VERY slowly.
The third issue was that the data_engine queue did not contain one of its default queue Subjects, so we added it-> QOS_DEFINITION
-- query to list blocked sessions (run this more than once to check)
Results showed multiples of the Microsoft Mashup Routine processes blocking sessions.
select p.query_plan, ex.session_id, ex.blocking_session_id, db_name(ex.database_id)as dbname, s.host_name, s.program_name, s.login_name, ex.status, ex.command, ex.last_wait_type, ex.cpu_time, ex.reads, ex.writes, ex.percent_complete from sys.dm_exec_requests ex INNER JOIN sys.dm_exec_sessions s ON ex.session_id = s.session_id cross apply sys.dm_exec_query_plan(ex.plan_handle) p |
Example shown below but we also saw some sessions blocked as well when we ran the query several times.
Steps followed:
View the data_engine queue in the Hub GUI Status window to make sure it starts processing and continues processing data.
Once the data_engine thread_count_insert was set to 24, and hub_bulk_size was set to 1750, the data_engine performance was very good and the hub GUI Status->Queued column remained at or near zero on every click of the refresh button.