DX Infrastructure Manager

Expand all | Collapse all

Queue in data_engine

  • 1.  Queue in data_engine

    Posted 04-15-2019 05:35 PM

    Hi everyone...

    I have many queue on data_engine. I think that the problem is bad performance of the db server. But I see this line on the logs file data_engine.

    Anyone know what is that????

     

    Apr 15 15:57:28:064 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:28:064 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:29:065 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:29:065 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:29:119 [19664] de: Commit - inserted   11 rows to RN_QOS_DATA_0176 in 1792 ms (ms/r:162)

    Apr 15 15:57:30:065 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:30:065 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:30:065 [9388] de: qos_data_thread - messages on hub: 12,117, messages in data_engine: 149,430 ( bulk buffers: 149,430, process queue: 0, limit: 100,000 )

    Apr 15 15:57:31:066 [9388] de: qos_check - Check Queue Loop.  Connected: 1 vbQueue: 1

    Apr 15 15:57:31:066 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:31:066 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:32:067 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:32:067 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:33:067 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:33:067 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:34:068 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:34:068 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:35:068 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:35:068 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:35:068 [9388] de: qos_data_thread - messages on hub: 12,117, messages in data_engine: 149,430 ( bulk buffers: 149,430, process queue: 0, limit: 100,000 )

    Apr 15 15:57:36:069 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:36:069 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:37:070 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:37:070 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:38:071 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:38:072 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:39:099 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:39:099 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:40:109 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:40:109 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:40:109 [9388] de: qos_data_thread - messages on hub: 12,117, messages in data_engine: 149,430 ( bulk buffers: 149,430, process queue: 0, limit: 100,000 )

    Apr 15 15:57:41:110 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:41:110 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:42:111 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:42:111 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:43:112 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:43:112 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:44:113 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:44:113 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:45:115 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:45:115 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:45:115 [9388] de: qos_data_thread - messages on hub: 12,117, messages in data_engine: 149,430 ( bulk buffers: 149,430, process queue: 0, limit: 100,000 )

    Apr 15 15:57:46:115 [9388] de: qos_check - Check Queue Loop.  Connected: 1 vbQueue: 1

    Apr 15 15:57:46:115 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:46:115 [9388] de: lockStatisticsFile UNLOCK

    Apr 15 15:57:47:116 [9388] de: lockStatisticsFile LOCK

    Apr 15 15:57:47:116 [9388] de: lockStatisticsFile UNLOCK



  • 2.  Re: Queue in data_engine

    Posted 04-15-2019 05:49 PM

    The place to start is with:

    data_engine best practices - CA Knowledge 



  • 3.  Re: Queue in data_engine

    Posted 04-15-2019 06:42 PM

    Yes David, I know the document and this was applied. But I see the message on the logs and I don't know if this is normal



  • 4.  Re: Queue in data_engine

    Posted 04-16-2019 09:18 AM

    I've not found anything to indicate it is a problem.



  • 5.  Re: Queue in data_engine

    Posted 04-16-2019 09:19 AM

    You see these messages at loglevel 5.

     

    Locking and unlocking entries in the log are normal when loglevel is set to 5. SQL Server by default uses locks for transaction isolation. The Lock command locks an object, either for shared or exclusive use, within the context of the currently active transaction. A lock on an object prevents transactions from committing until the lock is removed.

     

    See also:

     

    Transaction Locking and Row Versioning Guide
    https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017#Lock_Engine

     

    At loglevel 4 you only see database GLOBAL lock and unlock and much less frequent, in case you'd like to avoid these entries in the log entries.

     

    If you'd like to view the status of locks/sessions you can use:

     

    sp_lock

     

    or

     

    SELECT *
    FROM sys.dm_exec_requests
    WHERE DB_NAME(database_id) = 'CA_UIM'
    AND blocking_session_id <> 0


    Steve