August 22, 2014 at 12:38 am
Hi all ,
A client of mine complaint about his Service Broker Message queue..
last week the number of queue is around 2000 something and today become 3000 something ...they want to reduce the number of queue and this is the stored proc :
USE DB1
GO
--Object: StoredProcedure [dbo].[queue]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[queue]
AS
BEGIN
DECLARE @TimeoutMS Int
SET @TimeoutMS = 500
DECLARE @CH UniqueIdentifier --current conversation handle
DECLARE @ConversationGroupHandle UniqueIdentifier
DECLARE @XmlPacket Varchar(max) --get the xml packet into this variable
DECLARE @MessageTypeName sysname
WAITFOR
(
GET CONVERSATION GROUP @ConversationGroupHandle
FROM [dbo].[status_target_queue]
),
TIMEOUT @TimeoutMS;
WHILE (@ConversationGroupHandle IS NOT NULL)
BEGIN
BEGIN TRY
WAITFOR
(
RECEIVE TOP(1) @ch = Conversation_Handle,
@XmlPacket = CAST(message_body as Varchar(max)),
@MessageTypeName = message_type_name
FROM [dbo].[status_target_queue]
),
TIMEOUT @TimeoutMS--wait up to a message before removing this stored procedure from memory
-- process the xml using the ProcessStatusXml stored procedure
IF (@MessageTypeName = 'status_text_message')
BEGIN
EXEC ProcessStatusXml @XmlPacket
--if this is a legacy xml packet, end the conversation
DECLARE @xmlValue xml
SET @XmlValue = CAST(@xmlPacket as xml)
DECLARE @LegacyProc Varchar(50)
SELECT @LegacyProc = @XmlValue.value('(/StatusMessage/Legacy)[1]', 'nvarchar(30)')
IF (LEN(@LegacyProc) > 0)--this is legacy, end conversation
END CONVERSATION @ch
END
IF (@MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @ch
END
END TRY
BEGIN CATCH
INSERT INTO StatusProcessLog
(xmldata,
tracedata,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage )
VALUES
(@XmlPacket,
'Error was thown by the message',
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE())
END CATCH
-- Get the next available conversation group:
BEGIN TRY
WAITFOR (
GET CONVERSATION GROUP @ConversationGroupHandle
FROM [dbo].[status_target_queue]
), TIMEOUT @TimeoutMS;
END TRY
BEGIN CATCH
-- If there are no more conversations to retrieve,
-- GET CONVERSATION GROUP will throw and execption
-- and we should stop iterating:
BREAK;
END CATCH;
END
END
Is it due to there is only 1 message per transaction when the stored proc is called ?
("RECEIVE TOP(1) @ch = Conversation_Handle,
@XmlPacket = CAST(message_body as Varchar(max)),
@MessageTypeName = message_type_name
FROM [dbo].[status_target_queue] ")
and according to resource monitor the servicebroker log is pretty busy.
Image PID File Read (B/min) Write (B/min) IO Priority Response Time (ms)
sqlservr.exe 292 D:\ServiceBrokerMessages_log.ldf 0 15782466 Normal 0
Any response is highly appreciate!
Cheers
August 24, 2014 at 7:13 pm
WhiteLotus (8/22/2014)
Hi all ,A client of mine complaint about his Service Broker Message queue..
last week the number of queue is around 2000 something and today become 3000 something ...they want to reduce the number of queue and this is the stored proc :
USE DB1
GO
--Object: StoredProcedure [dbo].[queue]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[queue]
AS
BEGIN
DECLARE @TimeoutMS Int
SET @TimeoutMS = 500
DECLARE @CH UniqueIdentifier --current conversation handle
DECLARE @ConversationGroupHandle UniqueIdentifier
DECLARE @XmlPacket Varchar(max) --get the xml packet into this variable
DECLARE @MessageTypeName sysname
WAITFOR
(
GET CONVERSATION GROUP @ConversationGroupHandle
FROM [dbo].[status_target_queue]
),
TIMEOUT @TimeoutMS;
WHILE (@ConversationGroupHandle IS NOT NULL)
BEGIN
BEGIN TRY
WAITFOR
(
RECEIVE TOP(1) @ch = Conversation_Handle,
@XmlPacket = CAST(message_body as Varchar(max)),
@MessageTypeName = message_type_name
FROM [dbo].[status_target_queue]
),
TIMEOUT @TimeoutMS--wait up to a message before removing this stored procedure from memory
-- process the xml using the ProcessStatusXml stored procedure
IF (@MessageTypeName = 'status_text_message')
BEGIN
EXEC ProcessStatusXml @XmlPacket
--if this is a legacy xml packet, end the conversation
DECLARE @xmlValue xml
SET @XmlValue = CAST(@xmlPacket as xml)
DECLARE @LegacyProc Varchar(50)
SELECT @LegacyProc = @XmlValue.value('(/StatusMessage/Legacy)[1]', 'nvarchar(30)')
IF (LEN(@LegacyProc) > 0)--this is legacy, end conversation
END CONVERSATION @ch
END
IF (@MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @ch
END
END TRY
BEGIN CATCH
INSERT INTO StatusProcessLog
(xmldata,
tracedata,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage )
VALUES
(@XmlPacket,
'Error was thown by the message',
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE())
END CATCH
-- Get the next available conversation group:
BEGIN TRY
WAITFOR (
GET CONVERSATION GROUP @ConversationGroupHandle
FROM [dbo].[status_target_queue]
), TIMEOUT @TimeoutMS;
END TRY
BEGIN CATCH
-- If there are no more conversations to retrieve,
-- GET CONVERSATION GROUP will throw and execption
-- and we should stop iterating:
BREAK;
END CATCH;
END
END
Is it due to there is only 1 message per transaction when the stored proc is called ?
("RECEIVE TOP(1) @ch = Conversation_Handle,
@XmlPacket = CAST(message_body as Varchar(max)),
@MessageTypeName = message_type_name
FROM [dbo].[status_target_queue] ")
and according to resource monitor the servicebroker log is pretty busy.
Image PID File Read (B/min) Write (B/min) IO Priority Response Time (ms)
sqlservr.exe 292 D:\ServiceBrokerMessages_log.ldf 0 15782466 Normal 0
Any response is highly appreciate!
Cheers
Hi All,
Please help on this issue ...
Thanks !!
August 25, 2014 at 7:02 pm
actually, we use receive top 1 in the interface sp.
i think what you need to do is:
1. increase the max_queue_readers parameter in the queue definition. the default is 1. you can change it to 2 or 3.
2.improve the execution speed of the interface. you need to check whether you can optimize it.
August 25, 2014 at 7:44 pm
Wison (8/25/2014)
actually, we use receive top 1 in the interface sp.i think what you need to do is:
1. increase the max_queue_readers parameter in the queue definition. the default is 1. you can change it to 2 or 3.
2.improve the execution speed of the interface. you need to check whether you can optimize it.
Thank you for your response ! appreciate it !
hmm i can change the max_queue_readers into 2 or 3 as suggested but I am not sure about how to improve the speed ??
I am looking forward to hearing your further advice
Thanks !!
August 25, 2014 at 8:30 pm
firstly, you should check how long will your sp runs.
then find whether can optimize it.
For example, it ran for 10 minutes one time. if you can optimize it(reduce the running duration from 10 minutes to 2 minutes),then the process of decreasing the messages of the queue will speed up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply