November 3, 2014 at 8:31 pm
Hi SSC,
I have a proc which currently receives XML messages one at a time from another application. The problem is that the application is very spikey, in that it will sometimes fire off tens - if not hundreds of thousands of messages all at once. Hardly ideal, but it is what it is. I've reduced the procedure which collects the messages to a simple insert into a "raw" table, and the another process comes along and processes them in batches, but there's still enough blocking going on to cause timeouts to occur.
I've spent most of the day researching Service Broker and while it looks neat and like it would do what I need (at least to the same extend my current code does) I can't find any telling answers to whether or not there's any reduction in blocking when SQL inserts records into a queue vs a regular table. Maybe it's just wishful thinking that a SB queue would do anything an ordinary table wouldn't in this case, but I though I'd check with the experts here as to whether there were any differences which might be relevant.
November 6, 2014 at 4:51 pm
JeeTee (11/3/2014)
I have a proc [...] the application is very spikey, in that it will sometimes fire off tens - if not hundreds of thousands of messages all at once. [...] I've reduced the procedure [...] but there's still enough blocking going on to cause timeouts to occur.Service Broker [...] looks neat and like it would do what I need [but] I can't find any telling answers to whether or not there's any reduction in blocking when SQL inserts records into a queue vs a regular table.
What you're looking into is a messaging application, which Service Broker acts like as well, but you need to get it INTO the app. Any app will have some form of this problem if you get spike volume and short timeouts. Are your blocks happening during the batch processing, or during the insertions? Are you tail-inserting records (IE: Always at the end of the Clustered Index) and are there other indexes which aren't tail inserts which might need to move around? How large are the batches that are being processed, is it over 5000 rows at a time (thus getting SQL Server to try to escalate the lock)?
A Service Broker queue IS a table. It's just got some extra wrappers on it but it works just like any other table storage. The value of Service Broker is on the read/processing side, not the insertion side. It's usually a bit lighterweight than a lot of other tables which are heavily indexed, but you'll still hotspot for insertions and other normal blocking problems.
So, let's start at the beginning, and please correct anything I've misunderstood:
You have high volume XML statements coming to your database from an external app, and blocking is causing you storage concerns. These XML statements are coming in as single row insertions, instead of being bulk collected and shipped by the origination application. Due to this, inserts are blocking inserts and you're receiving timeouts/failures?
Can you show us the schema for the target table and all indexing attached to it?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply