1

I push some data from one database to another on the same instance using Service Broker. 20 cores are online, 6 Service Broker tasks are activated, every taks is working on indepentent piece of data. So I expect 6 tasks running at peak, but only few - up to 3, usually 2 - tasks are running. Not other known (for me) process is using this server.

How to prompt more cores to run?

UPDATE: The queue procedure:

CREATE PROCEDURE dbo.RequestProcedure
AS BEGIN
    SET NOCOUNT ON;

    DECLARE @OperationId int;
    DECLARE @ProcedureName varchar(60) = OBJECT_NAME(@@PROCID);
    DECLARE @Annotation varchar(240);
    EXECUTE LogOperation @ProcedureName, @Annotation, @OperationId OUTPUT;

    DECLARE @ConversationHandle uniqueidentifier;
    DECLARE @Message xml;
    DECLARE @MessageType sysname;

    BEGIN TRY
        WHILE (1 = 1) BEGIN
            WAITFOR (
                RECEIVE TOP(1) @ConversationHandle = [conversation_handle],
                    @Message = message_body,
                    @MessageType = message_type_name
                FROM RequestQueue
            ), TIMEOUT 1000;
            IF (@@ROWCOUNT = 0) BEGIN
                BREAK;
            END;

            IF @MessageType = 'RequestMessage' BEGIN
                DECLARE @Sql varchar(max) = REPLACE(REPLACE(REPLACE(REPLACE(CAST(@Message AS varchar(max)),
                    '<RequestMessage>', ''),
                    '&gt;', '>'),
                    '&lt;', '<'),
                    '</RequestMessage>', '');

                EXECUTE (@Sql);

                SEND ON CONVERSATION @ConversationHandle
                    MESSAGE TYPE ConfirmMessage ('<ConfirmMessage />');
            END ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
                END CONVERSATION @ConversationHandle;
            END ELSE IF @MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN
                END CONVERSATION @ConversationHandle;
            END;
        END;
    END TRY
    BEGIN CATCH
        DECLARE @ConversationId uniqueidentifier = (
            SELECT TOP(1) conversation_id FROM sys.conversation_endpoints
            WHERE [conversation_handle] = @ConversationHandle
        );
        EXECUTE LogError @OperationId, @ConversationId;
        THROW;
    END CATCH;
END;
GO

UPDATE: The activation code:

DECLARE @CoreCount tinyint = (SELECT TOP(1) cpu_count FROM sys.dm_os_sys_info);
DECLARE @MaxQueueReaders tinyint = 1;
IF @CoreCount > 4 SET @MaxQueueReaders = CEILING(@CoreCount / 4);


DECLARE @Sql varchar(200) = CONCAT('
    ALTER QUEUE RequestQueue
    WITH ACTIVATION
    (
        STATUS = ON,
        PROCEDURE_NAME = RequestProcedure,
        MAX_QUEUE_READERS = ', @MaxQueueReaders, ',
        EXECUTE AS OWNER
    );');
EXECUTE (@Sql);

Dumps of some views queries:

/* @@VERSION */

Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) 
    Aug 15 2017 10:23:29 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

/* sys.dn_os_sys_info */

cpu_ticks         ms_ticks    cpu_count   hyperthread_ratio physical_memory_kb   virtual_memory_kb  committed_kb  committed_target_kb  visible_target_kb  stack_size_in_bytes os_quantum  os_error_mode os_priority_class max_workers_count scheduler_count scheduler_total_count deadlock_monitor_serial_number sqlserver_start_time_ms_ticks affinity_type affinity_type_desc  process_kernel_time_ms process_user_time_ms time_source time_source_desc  virtual_machine_type virtual_machine_type_desc  sql_memory_model sql_memory_model_desc
----------------- ----------- ----------- ----------------- -------------------- ------------------ ------------- -------------------- ------------------ ------------------- ----------- ------------- ----------------- ----------------- --------------- --------------------- ------------------------------ ----------------------------- ------------- ------------------- ---------------------- -------------------- ----------- ----------------- -------------------- -------------------------- ---------------- ---------------------
7572717394892491  3161911182  24          6                 201326132            8589934464         188743688     188743688            188743688          2093056             4           5             32                832               20              35                    9105379                        2585885935                    2             AUTO                93179880               5905735270           1           MULTIMEDIA_TIMER  1                    HYPERVISOR                 2                LOCK_PAGES

/* sys.dm_os_schedulers */

parent_node_id scheduler_id cpu_id      status                is_online is_idle preemptive_switches_count context_switches_count idle_switches_count current_tasks_count runnable_tasks_count current_workers_count active_workers_count work_queue_count     pending_disk_io_count load_factor
-------------- ------------ ----------- --------------------- --------- ------- ------------------------- ---------------------- ------------------- ------------------- -------------------- --------------------- -------------------- -------------------- --------------------- -----------
0              0            0           VISIBLE ONLINE        1         1       78159                     111635318              32804168            9                   0                    16                    9                    0                    19                    17         
0              1048578      0           HIDDEN ONLINE         1         0       30565                     0                      832                 1                   0                    1                     1                    0                    0                     1          
64             1048576      0           VISIBLE ONLINE (DAC)  1         1       4                         9                      646                 2                   0                    3                     1                    0                    0                     1          
0              1048582      1           HIDDEN ONLINE         1         1       0                         0                      16727443            1                   0                    1                     1                    0                    0                     1          
0              1            1           VISIBLE ONLINE        1         0       73562                     102766095              30121544            10                  0                    18                    9                    0                    130                   16         
0              2            2           VISIBLE ONLINE        1         0       133637                    107242447              34076175            10                  2                    16                    10                   0                    138                   18         
0              1048583      2           HIDDEN ONLINE         1         1       0                         0                      5688905             1                   0                    1                     1                    0                    0                     1          
0              1048584      3           HIDDEN ONLINE         1         1       6                         0                      666                 1                   0                    1                     1                    0                    0                     1          
0              3            3           VISIBLE ONLINE        1         0       53081                     103061355              30913264            8                   1                    16                    8                    0                    0                     16         
0              4            4           VISIBLE ONLINE        1         0       67518                     98777243               32823981            9                   0                    16                    9                    0                    108                   16         
0              1048585      4           HIDDEN ONLINE         1         1       2                         0                      669                 1                   0                    1                     1                    0                    0                     1          
0              1048587      5           HIDDEN ONLINE         1         1       15                        0                      1944                0                   0                    1                     0                    0                    0                     0          
0              5            5           VISIBLE ONLINE        1         1       47651                     102668651              36766472            10                  0                    17                    9                    0                    1                     16         
1              6            6           VISIBLE ONLINE        1         0       436656                    106533477              33820249            9                   0                    17                    9                    0                    7                     16         
1              1048579      6           HIDDEN ONLINE         1         0       0                         0                      4155                1                   0                    1                     1                    0                    0                     1          
1              7            7           VISIBLE ONLINE        1         1       282840                    103176525              29563995            10                  0                    18                    9                    0                    112                   16         
1              8            8           VISIBLE ONLINE        1         1       924169                    105025972              31897312            8                   0                    17                    8                    0                    28                    15         
1              9            9           VISIBLE ONLINE        1         1       1800901                   127859374              30655022            10                  0                    18                    10                   0                    50                    19         
1              10           10          VISIBLE ONLINE        1         1       1107977                   101928554              31583884            9                   0                    17                    9                    0                    57                    16         
1              11           11          VISIBLE ONLINE        1         1       2441327                   105364449              29033203            11                  0                    19                    9                    0                    23                    17         
2              12           12          VISIBLE ONLINE        1         0       41643                     104976967              36227813            11                  0                    17                    11                   0                    134                   18         
2              1048580      12          HIDDEN ONLINE         1         0       0                         0                      732                 1                   0                    1                     1                    0                    0                     1          
2              13           13          VISIBLE ONLINE        1         1       47409                     98600427               31389443            11                  0                    17                    10                   0                    98                    18         
2              1048586      13          HIDDEN ONLINE         1         1       51498                     0                      143888              1                   0                    1                     1                    0                    0                     1          
2              14           14          VISIBLE ONLINE        1         1       97399                     102442827              33076404            10                  0                    19                    10                   0                    107                   17         
2              15           15          VISIBLE ONLINE        1         0       43435                     108437743              35976058            10                  2                    17                    10                   0                    2                     19         
2              16           16          VISIBLE ONLINE        1         1       111299                    99857906               33513239            10                  0                    17                    10                   0                    133                   18         
2              17           17          VISIBLE ONLINE        1         1       83488                     112766271              33923774            11                  0                    18                    10                   0                    98                    18         
3              18           18          VISIBLE ONLINE        1         0       284684                    112472624              33377557            11                  1                    19                    11                   0                    0                     21         
3              1048581      18          HIDDEN ONLINE         1         0       0                         0                      941                 1                   0                    1                     1                    0                    0                     1          
3              19           19          VISIBLE ONLINE        1         1       1531191                   119422944              47146769            12                  0                    20                    11                   0                    5                     19         
3              20           20          VISIBLE OFFLINE       0         1       0                         1                      641                 1                   0                    2                     1                    0                    0                     1          
3              21           21          VISIBLE OFFLINE       0         1       0                         1                      641                 1                   0                    2                     1                    0                    0                     1          
3              22           22          VISIBLE OFFLINE       0         1       0                         1                      641                 1                   0                    2                     1                    0                    0                     1          
3              23           23          VISIBLE OFFLINE       0         1       1                         3                      643                 2                   0                    3                     1                    0                    0                     1          

UPDATE: To avoid misunderstanding, at the beginning I posted the same question on Stack Overflow, assuming that other people read and help me here.

  • Do you have resource governor configured on that server? Is maxdop configured? Do you see any waits or blockings during the execution of that task? – Ronaldo Jan 20 at 12:14
  • I do not use resource governopr. MAXDOP = 0. Some wait types, of course: ASYNC_IO_COMPLETION = 1, BROKER_EVENTHANDLER = 1, BROKER_TASK_STOP = 2, BROKER_TO_FLUSH = 1, BROKER_TRANSMITTER = 2, CXPACKET = 54, DIRTY_PAGE_POLL = 1, FT_IFTSHC_MUTEX = 1, HADR_FILESTREAM_IOMGR_IOCOMPLETION = 1, IO_COMPLETION = 1, KSOURCE_WAKEUP = 1, LAZYWRITER_SLEEP = 2, LOGMGR_QUEUE = 1. No blocked sessions. – Gerard Jaryczewski Jan 20 at 12:33
  • There's still a lot of missing info. When you say "6 Service Broker tasks are activated" do you mean that you see 6 sessions running your activation procedure? When you say "3, usually 2 - tasks are running" what do you mean? Include your queue definition and activation procedure in your question body. – David Browne - Microsoft Jan 20 at 15:11
  • @DavidBrowne-Microsoft, I want to prompt SQL Server to migrate data in parallel tasks). When I say: "6 Service Broker tasks are activated", then I mean: "There are 6 rows in sys.dm_os_acivated_tasks", and this is expected result because of MAX_QUEUE_READERS = 6. Whey I say: "3, usually 2 - tasks are running", I mean: "There are only 2 or 3 rows in sys.dm_exec_requests with status = running". – Gerard Jaryczewski Jan 20 at 15:36
  • @DavidBrowne-Microsoft, I have added the activation code. – Gerard Jaryczewski Jan 20 at 15:39
1

There are only 2 or 3 rows in sys.dm_exec_requests with status = running

A running SQL Server request will cycle quickly (perhaps every 4ms) between running, runnable, and suspended. running means that the task is actively using a CPU core (aka a Scheduler). runnable means that it need to use a Scheduler, but it's waiting for one to be available, and suspended means it's waiting on the completion of something, so it doesn't need to use the CPU.

For instance if you run this program:

drop table if exists t
go

create table t(id int identity primary key , d char(200) not null default '')
go
set nocount on
select @@spid
go
while 1=1
begin
  insert into t default values 
end

And then monitor the task status and progress from another session

select status, wait_type, last_wait_type, cpu_time, total_elapsed_time
from sys.dm_exec_requests
where session_id = 63

select *
from sys.dm_exec_session_wait_stats
where session_id = 63
order by wait_time_ms desc

You'll see the elapsed type broken down by CPU and waits. EG

status                         wait_type                                                    last_wait_type                                               cpu_time    total_elapsed_time
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------- ------------------
runnable                       NULL                                                         WRITELOG                                                     64673       217900

(1 row affected)

session_id wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
---------- ------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
63         WRITELOG                                                     989950               171566               132                  25162
63         PREEMPTIVE_OS_WRITEFILEGATHER                                10                   2607                 363                  0
63         ASYNC_NETWORK_IO                                             3                    1388                 1287                 0
63         MEMORY_ALLOCATION_EXT                                        269443               648                  3                    0
63         PREEMPTIVE_OS_FLUSHFILEBUFFERS                               13                   100                  9                    0

So it may just be that only 2 or 3 of the activation proc sessions need the CPU at any given time. The others are waiting for database reads, or log file writes, or page latches, or whatever. It's not at all uncommon for a request to spend much more time in suspended than running.

There's another possible cause for a lack of concurrency, and that's Service Broker conversation locking. The in-order delivery guarantees of Service Broker are implemented using conversation locking.

When you RECEIVE from a conversation, you lock that conversation's conversation group (often just that one conversation). No other queue reader can RECEIVE from that conversation until you've committed your RECEIVE. So if your sending application uses a pool of N long-lived conversations, your queue readers' concurrency will be limited to the same number.

  • DavidBrowne-Microsoft, that's the point: I expect up to 6 cores running, and even heavily running, because I prepared 60.000 of messages on different tables, and every message is an insert operation (generally), which requires 5 seconds (average) to complete. So I say, in my opinion: "Dear Server, you can use up to 6 cores to finish earlier, hurry up, please", and Server says: "All right, thank you for your generosity, but I am not interested to finish earlier". It's confusing. – Gerard Jaryczewski Jan 20 at 16:00
  • Ad "When you RECEIVE from a conversation, you lock that conversation's conversation group (often just that one conversation)", I am not using conversation groups. – Gerard Jaryczewski Jan 20 at 16:02
  • Well what are the waits that you see? If the requests aren't scheduled on a CPU, they are waiting on something. And again, it's pretty usual that SQL Server can run 6 concurrent requests on 2-3 CPU cores, as CPU cores are much faster than disks and network connections. And sessions sometimes contend for shared resources like locks and latches. – David Browne - Microsoft Jan 20 at 16:14
  • All right, David, I understand know: CPU are faster than disks, and probably I am waiting for disk. And after some other investigations I have found, that almost all cores are running. Now I need to digg into wait stats... – Gerard Jaryczewski Jan 20 at 16:18
  • 1
    Those are instance-wide aggregates since the service has started. Since you're on an old version, you don't have session-level wait stats, but you can sample the system wait stats over a few seconds to see the current wait stat profile. eg docs.microsoft.com/en-us/archive/blogs/dbrowne/… – David Browne - Microsoft Jan 20 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.