locked
Performance Impact RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I'm would like to understand from all the folks using SQL Service Broker  if there is any sort of degraded performance on their server due to enabling of this internal message broker mechanism?

    The server in production is already seeing performance issues, and we want to introduce a business use case to capture data change on certain tables asyncronously. 

    However, an argument has come up that enabling SSSB and having SQL now perform additional mechanism of all that it goes into creation of queues, services and all other SQL broker objects, followed up by the execution may worsen the performance when pressure tested.

    Would like to hear your experience if any.

    Thankyou!

    Tuesday, April 28, 2020 1:39 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm not sure why Joe starts to talk about CDC when you asked about Service Broker. Service Broker will not have the same impact schema changes as CDC can have.

    Service Broker as such is not going to kill the server, but as with all features, it depends on how you (ab)use it.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Surbhi08 Wednesday, April 29, 2020 8:50 AM
    Tuesday, April 28, 2020 9:15 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have worked on performance problems for a number of environments in which CDC was enabled. None of the problems were traced to CDC or SSSB.

    However, there are a few tables in which the cluster key was not set correctly. Change this required dropping the primary key on an identity column, setting the correct cluster key, then creating the primary key as nonclustered. This was a big hassle that required resetting CDC, losing all history?

    I would suggest fixing your performance issues first, in particular, ensure that all primary key and cluster keys are set correctly

    jchang

    Tuesday, April 28, 2020 2:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm not sure why Joe starts to talk about CDC when you asked about Service Broker. Service Broker will not have the same impact schema changes as CDC can have.

    Service Broker as such is not going to kill the server, but as with all features, it depends on how you (ab)use it.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Surbhi08 Wednesday, April 29, 2020 8:50 AM
    Tuesday, April 28, 2020 9:15 PM