Best practices on setting up a queue?
-
Is there a best practices article out there on how to set up a queue? The queue we are looking to set up will contain web service calls and parameters as BLOBs to set up a replication like system between two servers. The base table will be constantly polled by a job to check for any new changes. Any help or advice would be greatly appreciated
-
I did some work on optimising a customised message passing database utilising queues which wasn't working. as far as I can remember the main issue was removing the old entries which was causing blocking. thinking back a few years ( ! ) how the queue is implemented, lifo, fifo, lilo etc. will/should affect your design. In most cases I figure you'd probably want tables as heaps.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thank you Colin.
I will give more specifics. I have not seen this for my own two eyes yet as the database is at another location with no remote-in access, but here is what the developers are telling me. They have had this process in place for a while. Now they added a new webservice, that appears to be inserting more actively into the queue (14,000 entries per day vs. the normal 300). They are seeing the inserts being blocked. Below is the table structure. They are processing one record at a time with this select statement:
select TOP 1 *
from message (nolock)
ORDER BY id
The select is from a job that fires every second. There is a clustered on the ID in the table, so it is not a heap. If we take that clustered index off, will it cause page splits, or will it just insert at the bottom of the table? Also, will it hinder our select performance?
I have read other articles that select into temp table from the queue and loop through the temp table result set in code so they are processing more than one record per select. That sounds logical, but then I wonder if I will have disk i/o problems reading into a temp table?
Thanks for your help
CREATE TABLE [Message] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsProcessing] [bit] NOT NULL CONSTRAINT [DF_Message_IsProcessing] DEFAULT (0),
[Message] [image] NULL ,
[DateTimeEnqueued] [datetime] NOT NULL CONSTRAINT [DF_Message_DateTimeEnqueued] DEFAULT (getdate()),
[DateTimeProcessingStarted] [datetime] NULL ,
[RetryCount] [int] NULL CONSTRAINT [DF_Message_RetryCount] DEFAULT (0),
[MetaData] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateTimeRetryResume] [datetime] NULL ,
CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply