locked
QUEUE ACTIVATION - CPU 100% RRS feed

  • Question

  • Hi all, 
      When I set the Queue Activation Status = ON , the Procedure can be activation, but the Target Server CPU still used 100%. even if not exists message receive.
    It looks like just Queue monitor process running. when I killed that process the CPU rate normal.

      [single-core cpu 100%,dual-core cpu 50%]

    Thanks
    Thursday, November 12, 2009 9:03 AM

Answers

  • I see that you have an infinite loop looking for messages. Ideally, your procedure should exit (RETURN) if there is no more pending messages in the queue. Service Broker will call your procedure again when a message comes in the queue. The infinite loop looking for messages seems to be taking the CPU.

    Also, you dont need to do an EXISTS(). Do a RECEIVE() with a few seconds of timeout value. If the RECEIVE() returns nothing that means the queue empty and just exit from your stored procedure.

    Beyond Relational
    SyntaxHelp.com
    Friday, November 13, 2009 2:01 AM

All replies

  • Can you post the content of your activation stored procedure? It could be that your activation process continuously queries queue for possible messages and adding a delay there might help.
    Beyond Relational
    SyntaxHelp.com
    Thursday, November 12, 2009 10:21 AM
  • Sure,Thanks.

    I was try to remove the insert logic, but it I still not improved. And if not use the Queue to activation procedure, just run procedure to receive the message, it not have the problem.

    IF object_id('SP_RECEIVE') is null 
      DROP PROC [SP_RECEIVE]
    GO
    
    
    CREATE PROC [dbo].[SP_RECEIVE]
    AS
    
    SET XACT_ABORT ON
    	DECLARE @conversationHandle uniqueidentifier
    		, @msg_body nvarchar(MAX)
    		, @msg_type_name varchar(100) ;
    	IF EXISTS(SELECT TOP 1 1 FROM [InstTargetQueue] NOLOCK)
    	BEGIN
    
    	BEGIN Transaction;
    
    		RECEIVE top(1) 
    			@msg_type_name=message_type_name,  
    			@conversationHandle=conversation_handle, 
    			@msg_body=message_body         
    		FROM [InstTargetQueue]
    
    
    		IF @msg_type_name = '//Sample1/msg_Request'
    		BEGIN
    
    
    			DECLARE @XML XML,
    			@MsgType int,
    			@MsgBody varchar(max),
    			@MsgDt datetime
    
    			SET @XML = @msg_body
    			SELECT 
    			@MsgType = @XML.value('(msg/type)[1]','int'),
    			@MsgDt = @XML.value('(msg/msgdt)[1]','datetime')
    
    
    			SET @XML = @XML.query('(msg/body/row)[1]') 
    
    
    						INSERT INTO tblMessage 
    						(MsgType,MsgBody,MsgDt) VALUES(@MsgType,@XML,@MsgDt)
    
    
    
    			SEND ON CONVERSATION @conversationHandle 
    			MESSAGE TYPE [//Sample1/msg_Reply] (N'Reply:')
    
    			END CONVERSATION @conversationHandle
    		END
    
    
    	 
    
    	Commit
    
    	END
    	
    	GO



    Friday, November 13, 2009 1:11 AM
  • I see that you have an infinite loop looking for messages. Ideally, your procedure should exit (RETURN) if there is no more pending messages in the queue. Service Broker will call your procedure again when a message comes in the queue. The infinite loop looking for messages seems to be taking the CPU.

    Also, you dont need to do an EXISTS(). Do a RECEIVE() with a few seconds of timeout value. If the RECEIVE() returns nothing that means the queue empty and just exit from your stored procedure.

    Beyond Relational
    SyntaxHelp.com
    Friday, November 13, 2009 2:01 AM
  • yes, when I remove that exists(), it's ok.  

    use RECEIVE TOP(1) will infinite loop?
    If I need to add the WAITFOR ?

    like this:

    WAITFOR
    (
      RECEIVE...
    ),TIMEOUT 500
     
    Thanks you so much.
    Friday, November 13, 2009 2:11 AM
  • I was use for that broker to send the log, the data will very large, I was worry about the performance. So can you give me some suggest. 
    Friday, November 13, 2009 2:22 AM
  • Do RECEIVE() with a timeout.
    I would suggest you break the data into smaller pieces and send across using service broker.

    Beyond Relational
    SyntaxHelp.com
    Friday, November 13, 2009 2:30 AM
  • ok, Thanks a lot . 
    Friday, November 13, 2009 3:58 AM