CODEWITHSUNDEEP
sql-serverperformancescalabilityservice-broker
venkatesh
venkatesh

Reputation: 111

Service broker is taking time initially

I have used service broker to scale a stored procedure. It runs smoothly for 10,100,500 hits at same time.

But there is one small issue which I am facing. Below are the details.

The stored procedure which we are scaling out happens to execute for 10 secs.

When I run for 10 with 10 queues the average execution time is 10 secs. Means all are executing at same time.

But when I run for 100 times with 100 queues. I am not able to see similar results. Yes I donot expect all of them to run at same time. But at least the first execution should have completed by 10 secs and the others should execute when ever they are having resources. The finding here is 100 executions take 4 mins and the first completion happens after 2 mins (literally). And the rest completes in another 2 mins.

MY cpu cores are 6 and activations(Max_Queue_Readers) I tried with 6,12,48 and all have given the same result.

And one more thing to mention if I run this thing with wait for 10 secs with out the actual sp execution it runs very fast like 1000 requests in 40 secs.

I would like to know from the above said points the service broker initially is waiting one some thing to complete the first execution? Means why it is taking 2 mins to complete the first one any idea?

All these numbers are tracked by using temporary log tables, where we log times.

Upvotes: 1

Views: 536

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294277

The is a ramp up from 0 to Max_Queue_Readers. Service Broker does not activate instantly 6 queue readers, it will activate one, then another one in 5 seconds, then another one in 5 seconds and so on. The idea is for Service Broker to start as many activated procedures as necessary to keep up with the workload, but not more. Spikes are flattened out.

Read more at Understanding Queue Monitors.

Upvotes: 1

Related Questions