MSMQ ARCHITECTURE WITH DEDICATED PROCESSORS PER DATABASE

Question

I have a web application in ASP.NET MVC , C# and I have a specific use case that takes long time to process and users have to wait until the process is complete. I want to use MSMQ and relay the heavy work to dedicated MSMQ consumer/servicer. Our application has multiple clients and each client has their own SQL database. So let's say 100 clients make 100 separate SQL databases. The real challenge I have is to make the process faster using MSMQ but task of 1 client should not effect the performance of others. So I have 2 solutions:

Option-1: Unique MSMQ Private Queue per database so in my case it will be 100 queues and growing. 1 dedicated ASP.NET console application that listens to a dedicated MSMQ so in my case it will be 100 processors or console applications.

Option-2: 1 big MSMQ private queue for all databases A: 1 dedicated MSMQ consumer per database so 100 processors B: 1 MSMQ consumer that listens to the big MSMQ

I want to stick with Option-1 but I would want to know is this a feasible and enterprise type solution?

Answer 1

You actually have two questions

---

1. First, how do you allocate a resources affinity to a processor to SQL Server.

   Select the database in Sql Management Studio, right click and follow this..

   

---

2. Clean your Database regularly

   DBCC FREEPROCCACHE;

   DBCC DROPCLEANBUFFERS;

---

3. MSMQ, turn on [journaling][2], but also consider another queuing process RabbitMQ etc, or write a simple one to enquque the jobs sample from here

   public class MultiThreadQueue
   {
   BlockingCollection<string> _jobs = new BlockingCollection<string>();
   
   public MultiThreadQueue(int numThreads)
   {
       for (int i = 0; i < numThreads; i++)
       {
           var thread = new Thread(OnHandlerStart)
               { IsBackground = true };//Mark 'false' if you want to prevent program exit until jobs finish
           thread.Start();
       }
   }
   
   public void Enqueue(string job)
   {
       if (!_jobs.IsAddingCompleted)
       {
           _jobs.Add(job);
       }
   }
   
   public void Stop()
   {
       //This will cause '_jobs.GetConsumingEnumerable' to stop blocking and exit when it's empty
       _jobs.CompleteAdding();
   }
   
   private void OnHandlerStart()
   {
       foreach (var job in _jobs.GetConsumingEnumerable(CancellationToken.None))
       {
           Console.WriteLine(job);
           Thread.Sleep(10);
       }
    }
   }
   

Hope this helps :)

---

The question has been reworded, he meant sometheng else when he said Processors.

---

Update added a consumer pattern with onPeek :

You really need to post some code!

Consider using the OnPeekCompleted method. If there is an error you can leave the message on the queue

If you have some kind of header which identifies the message you can switch to a different dedicated/thread.

    private static void OnPeekCompleted(Object sourceQueue, PeekCompletedEventArgs asyncResult)
    {
        // Set up and connect to the queue.
        MessageQueue mq = (MessageQueue)sourceQueue;

        // gets a new transaction going
        using (var txn = new MessageQueueTransaction())
        {
            try
            {
                // retrieve message and process
                txn.Begin();
                // End the asynchronous peek operation.
                var message = mq.Receive(txn);

            #if DEBUG
                // Display message information on the screen.
                if (message != null)
                {
                    Console.WriteLine("{0}: {1}", message.Label, (string)message.Body);
                }
            #endif
                // message will be removed on txn.Commit.
                txn.Commit();
            }
            catch (Exception ex)
            {
                // If there is an error you can leave the message on the queue, don't remove message from queue
                Console.WriteLine(ex.ToString());
                txn.Abort();
            }
        }

        // Restart the asynchronous peek operation.
        mq.BeginPeek();
    }

You can also use a service broker