July 10, 2015 at 11:57 am
Hi Folks,
I'm trying to flesh out a good queue table design with our dev team.
So here is a general overview of the scenario. First an application will hit a WebAPI and grab any updates to Content and store those ID's in SQL (queue table). Next is the fun part, different multi threaded apps will process ID's from the queue. One app will make updates to the data in a different SQL DB while the other will update an index (likely Elastic).
Obviously, we don't want multiple threads working on the same items. One strategy could be to use UPDLOCK & READPAST query hints. However, I'm not sure about the reliability or performance of this solution. I just started looking into setting up a service broker but that would be completely unfamiliar territory for me. Also I can see how a broker might work well within the instance but how would that work with the application making updates to Elastic?
I'd love to hear from anybody with ideas or experience with setting up a design for a queue.
Thanks,
July 11, 2015 at 11:55 pm
Hi,
I had success working with READPAST in a queue table. The important things are to REALLY be careful with the types of locks held and, make sure there is no lock escalation, and make the queries really efficient. Once we did that, performance was good.
Thomas Kejser has another very interesting implementation for a queue table, but I have not tested it: [/url]
I will also say that generally speaking, although it's not natural for database people, it's less recommended to work with queues inside the database. There are external systems like RabbitMQ that do it much better.
July 12, 2015 at 6:53 am
yb751 (7/10/2015)
Hi Folks,I'm trying to flesh out a good queue table design with our dev team.
So here is a general overview of the scenario. First an application will hit a WebAPI and grab any updates to Content and store those ID's in SQL (queue table). Next is the fun part, different multi threaded apps will process ID's from the queue. One app will make updates to the data in a different SQL DB while the other will update an index (likely Elastic).
Obviously, we don't want multiple threads working on the same items. One strategy could be to use UPDLOCK & READPAST query hints. However, I'm not sure about the reliability or performance of this solution. I just started looking into setting up a service broker but that would be completely unfamiliar territory for me. Also I can see how a broker might work well within the instance but how would that work with the application making updates to Elastic?
I'd love to hear from anybody with ideas or experience with setting up a design for a queue.
Thanks,
UPDLOCK/READPAST work great for high-concurrency queuing when you are guaranteed ROWLOCKing as well. This can be achieved with an appropriate narrow clustered index. However, if you choose the ubiquitous integer identity(1,1) that EVERYONE puts on their SQL Server tables (even when they shouldn't), you can create a hotspot at 2 places in the table - where new rows go in and old rows are popped off the queue.
As someone mentioned already, Thomas Kejser has numerous solutions for spreading out the hotspot if you run into it. I like simple and efficient myself, so I have done a leading tinyint column to the clustered index and my insert/pop applications have that coded into them. This gives me just 1 extra byte for the size and 256 spots along the range of the index to grab values, giving 512 hotspots for insert/deletes. I would only pursue this if you found that you needed it in your scalability testing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2015 at 12:18 pm
Thanks for the input guys. I checked out Kejser's blog and he has some really interesting things to say on the subject. This may be the simplest to implement but I'm going to try using the Service Broker first. If all else fails I'll end up learning something new about SQL. At first what confused me about the SSB was the two service/queue system. Which instinctively led me to think that it wasn't quite meant for what I needed. However, I ran across this excellent article that finally gave me the "I get it" moment.
http://www.davewentzel.com/content/service-broker-demystified-can-i-model-monologs-yes-you-can
Cheers,
July 13, 2015 at 2:42 pm
If all else fails ...
Be VERY VERY careful with what that statement REALLY means. You are doing data processing, and if all else fails you could process that data incorrectly with no warning or error. And THAT is the worst thing that can possibly happen. People die, companies go out of business, etc. Note I am not putting a smiley here either, because I know of circumstances where both have happened.
Another thing that could happen is unacceptably slow processing, especially under load. Your HA/DR picture changes dramatically when SSB is in play too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 14, 2015 at 8:22 am
TheSQLGuru (7/13/2015)
If all else fails ...
Be VERY VERY careful with what that statement REALLY means. You are doing data processing, and if all else fails you could process that data incorrectly with no warning or error. And THAT is the worst thing that can possibly happen. People die, companies go out of business, etc. Note I am not putting a smiley here either, because I know of circumstances where both have happened.
Another thing that could happen is unacceptably slow processing, especially under load. Your HA/DR picture changes dramatically when SSB is in play too.
Perhaps I didn't express the context clearly. I'm currently looking at strategies and setting them up in a dev environment. I wouldn't setup anything I wasn't comfortable with in production. My 'if all else fails' meant if I wasn't happy with my testing I would look at another solution. It's never wasted time when you learned something new.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply