Home Forums Programming Service Broker recommendations for MAX_QUEUE_READER option on queue RE: recommendations for MAX_QUEUE_READER option on queue

  • michael vessey (5/15/2012)


    i think i hit the jackpot

    http://msdn.microsoft.com/en-us/library/dd576261(v=sql.100).aspx

    finally after 2 days worth of digging 😀

    Be forewarned Micheal, that article (and most MS Service Broker performance articles) are focused on how to increase performance of Service Broker by reducing the overhead and impact of Service Broker itself. As such the kind of tasks that are being serviced by the Activation Procedures are extremely low latency tasks, so that they can focus on the load from SB.

    What they do not usually look at very much (and what I feel your principal performance concern is going to be), is what the performance impact/overhead of concurrency and locking stalls in the service tasks themselves is going to be. That is never easy to assess, and I do not know any hard and fast rules other than "always test".

    A theoretical upper limit is easy to set: it's the number of CPU threads plus the number of disk spindles that will be used by your service tasks. In theory, you can never have more tasks physically "in service"(*) than that anyway. Anymore than that and the excess would have to be physically "in queue" for resources, instead of in your Service Broker's queue.

    The real question then becomes, between One(1) and that number, when does the CPU task switching overhead and/or the effect of disk-head slewing from random access, and/or the hidden subtle logical effects of locking on the tasks, outweigh the benefits of maximizing the physical resource utilizations?

    And there's no generally solvable formula for that. (well there is for the CPU part, but not the other two).

    (*-- "in service" and "in queue" are the the Performance & CP technical terms for using a physical resource and waiting to use a physical resource, respectively)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]