How can Internal Activation use more resources?

Hi,

I'm using service broker queue with internal activation to run a stored procedure.
The DB server is windows 2003 R2, 4 cpu, with SQL server 2005 SP2.
When I'm runing the stored procedure directly from the sql management studio it takes about 75% of the cpu and running for about a minute, but when the stored procedure is activated by the queue internal activation (as a background process) it uses only 25% of the machine cpu (my guess it uses only 1 cpu insted of all 4 cpu) and running for much longer time (sometimes even more than one hour).
How can I change this behavior? I want it to run as fast as possible.

The queue decleration is:

CREATE QUEUE [TaskQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ProcessTasksProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF);

Thanks in advance,
Shai Brumer

If you have 4 CPU, why are you restricting the MAX_QUEUE_READERS to 1?

How did you measure this? Did you preload the queue with messages and run the procedure? Or are the messages continuosly incomming from some local or remote sender?

Note that activated procedures are looped for you by the activation mechanism, that is if the procedure exists and there are still message in the queue, it is invoked again. You will not see a separate Activation:End and Activationtart events for this, so it may look like is running for hours but in fact is contiuosly re-invoked because there are still message in the queue.

With only 1 activated proc, it is normal to consume only 25% of the 4 CPUs. The only way a procedure could use more than one CPUs (and push CPU utilization above 25%) would be to contain many queries that can be parallelized. Do you have anything in the procedure that could be heavilly paralelized and thus explain the 75% when launched from SSMS?

|||

My intention is to run the stored procedure asynchronously from another stored procedure, and for this I used service broker queues as async mechanism- when I want to run the procedure asynchronously I enter a message into the queue that is configured to active the procedure. The procedure is heavy and using all the cpu’s of the server (I can see parallelization in the execution plan and the server cpu is up to 75% when I’m running the procedure directly from the management studio).

I set up the MAX_QUEUE_READERS to 1 because I want a single procedure to run at any given time- but I want the procedure to take as much resource she need in order to complete the run as fast as it can.

Is this the normal behavior of internal activation? (Useing only 1 CPU).

Is there any way to change this?

|||Activation procedure should behave just as if the procedure was launched from SSMS. If you ahve plans that are heavily parallel, they should continue to be parallel and use 70% CPU. Can you compare the plans when run under activation vs. when run from SSMS?|||

How can I get the execution plan from the SSMS ?

|||

I ment:

How can I get the execution plan from the activation ?

|||Attach the Profiler and monitor the server and monitor the Performance/Showplan XML for Query Compile. The Profiler will show the plans just like SSMS does and each event/plan can be saved as an individual .sqlplan file (right click on the event and select 'Extract Data').|||

It seems the problem was the statistics of the table.

Thanks for the help.

Shai.