Service Broker and CAP_CPU_PERCENT: Throttling Message Processing

What Is Service Broker?

SQL Server Service Broker is an asynchronous message-queuing system built into the database. Applications send messages; stored procedures receive and process them, typically from background tasks.

Common use cases:

Event notifications (trigger-based alerts)
Asynchronous task processing (off-load heavy work from web requests)
Distributed transactions between databases or instances
Decoupled application communication

Unlike external message brokers (RabbitMQ, Kafka), Service Broker is inside the database. This means:

✓ No network hop—messages are in-database queues
✓ ACID-compliant—messages are transactional
✗ But if the queue gets backed up, it consumes CPU and memory inside SQL Server
✗ And there's no built-in circuit breaker by default

How Service Broker Queues Work

A Service Broker queue is a system table with a special activation stored procedure:

-- Create a queue with an activation procedure
CREATE QUEUE MyQueue
  WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = dbo.ProcessMessages,
    MAX_QUEUE_READERS = 10,
    EXECUTE AS OWNER
  );

-- The activation procedure processes one batch of messages
ALTER PROCEDURE dbo.ProcessMessages
AS
BEGIN
  -- Receive up to 1000 messages
  DECLARE @msg TABLE (conversation_handle UNIQUEIDENTIFIER);

  RECEIVE TOP (1000)
    conversation_handle
  INTO @msg
  FROM MyQueue;

  -- Process each message (this could be expensive!)
  WHILE (SELECT COUNT(*) FROM @msg) > 0
  BEGIN
    -- Custom business logic here
    -- Could be I/O-heavy, CPU-heavy, whatever
    WAITFOR DELAY '00:00:01';  -- Simulate work
  END
END;

Notice MAX_QUEUE_READERS = 10. This means up to 10 concurrent instances of ProcessMessages can run simultaneously. If each one processes 1000 messages, and each message takes significant work, you can quickly exhaust CPU.

The Problem: Runaway Queue Processing

Scenario: A Queue Backs Up

Your application starts sending messages faster than the queue can process them (perhaps the activation procedure is slow, or the message volume suddenly spikes). The queue fills up.

Service Broker's default behavior: spawn more activation readers to catch up (up to MAX_QUEUE_READERS). Now you have 10 concurrent processes each consuming CPU, each trying to drain 1000-message batches.

Reality check: A single poorly-written activation procedure processing 10,000+ messages in parallel can pin CPU at 100%, starving other queries. User reports: "The database is slow. I don't see any long-running queries. What's happening?"

Answer: Service Broker is eating your CPU silently, processing queued messages in the background.

Enter: Resource Governor and CAP_CPU_PERCENT

SQL Server's Resource Governor is a CPU (and memory) throttling system. CAP_CPU_PERCENT is the hard ceiling for a workload group's CPU usage.

The Resource Governor hierarchy:

Resource Pool: Allocates CPU and memory to groups of sessions
Workload Group: Defines how to classify sessions (by login, application, etc.)
CAP_CPU_PERCENT: The maximum % of physical CPU cores this workload group can use

Example: Limit Service Broker to 20% CPU

-- Create a resource pool limited to 20% CPU
CREATE RESOURCE POOL ServiceBrokerPool
  WITH (
    MAX_CPU_PERCENT = 20
  );

-- Create a workload group (default behavior)
CREATE WORKLOAD GROUP ServiceBrokerGroup
  WITH (
    CAP_CPU_PERCENT = 20
  )
  USING ServiceBrokerPool;

-- Activate Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;

⚠ Catch: By default, sessions aren't assigned to any workload group—they use the default pool. You must classify sessions to apply the throttle.

Connecting Service Broker to Resource Governor

The Challenge

Service Broker activation procedures run in the context of an internal NT AUTHORITY\NETWORK SERVICE-like session. To apply CAP_CPU_PERCENT, you need to classify these sessions into your throttled workload group.

Solution: Classifier Function

A classifier function is a T-SQL function that returns which workload group a session should use:

-- Create a classifier function
CREATE FUNCTION dbo.ClassifyConnections()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
  -- If the session is running Service Broker (queue reader),
  -- assign it to ServiceBrokerGroup
  IF PROGRAM_NAME() LIKE '%Service Broker%' OR
     APP_NAME() LIKE '%ActivationProc%'
    RETURN 'ServiceBrokerGroup'

  -- All other sessions go to default group
  RETURN 'default'
END;

-- Register the classifier function
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifyConnections);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Now, every Service Broker activation session is capped at 20% CPU. If a queue backs up and activation readers start spinning, they're automatically throttled—preventing CPU starvation of OLTP queries.

Practical Configuration

Step-by-Step Setup

-- 1. Create resource pool (40% CPU max)
CREATE RESOURCE POOL BrokerPool
  WITH (MAX_CPU_PERCENT = 40);

-- 2. Create workload group (cap at 30% of pool = 12% physical CPU)
CREATE WORKLOAD GROUP BrokerGroup
  WITH (CAP_CPU_PERCENT = 30)
  USING BrokerPool;

-- 3. Create classifier function
CREATE FUNCTION dbo.ClassifyConnections()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
  -- Detect Service Broker sessions
  DECLARE @session_id INT = @@SPID;
  IF (SELECT program_name FROM sys.dm_exec_sessions
      WHERE session_id = @session_id) LIKE '%Broker%'
    RETURN 'BrokerGroup'
  RETURN 'default'
END;

-- 4. Register classifier and reconfigure
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifyConnections);
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- 5. Verify configuration
SELECT * FROM sys.resource_governor_workload_groups;
SELECT * FROM sys.resource_governor_resource_pools;

Monitoring the Impact

-- Check workload group CPU usage (real-time)
SELECT
  d.group_id,
  g.name,
  d.cpu_usage_ms,
  d.cpu_delayed_ms,
  d.cpu_violation_count
FROM sys.dm_resource_governor_workload_groups_history_current d
INNER JOIN sys.resource_governor_workload_groups g
  ON d.group_id = g.group_id
ORDER BY d.cpu_usage_ms DESC;

-- If cpu_violation_count > 0, the group hit the CAP_CPU_PERCENT limit
-- and was throttled

Tuning CAP_CPU_PERCENT

Finding the Right Value

Too high (50%+): Service Broker can still starve OLTP queries.
Too low (5%–10%): Queue messages are processed very slowly, backing up further.
Sweet spot: 20–30%, depending on your server and queue volume.

Process:

Set CAP_CPU_PERCENT = 25 initially
Monitor queue depth (number of messages waiting) over 1 week
Check cpu_violation_count in sys.dm_resource_governor_workload_groups_history_current
If queue depth grows unbounded, increase to 30–35%
If OLTP performance still suffers, decrease to 15–20%

Pro tip: Couple CAP_CPU_PERCENT with queue monitoring. If queue depth > 10,000 messages consistently, either increase CAP_CPU_PERCENT or optimize the activation procedure (add indexes, reduce I/O, batch more efficiently).

Common Mistakes

Mistake 1: Classifier Function Not Detecting Service Broker

The classifier function checks PROGRAM_NAME() or APP_NAME(), but Service Broker sessions might report differently across SQL Server versions. Always test:

-- From within an activation procedure:
SELECT
  @@SPID AS session_id,
  PROGRAM_NAME() AS program_name,
  APP_NAME() AS app_name,
  client_interface_name,
  host_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Mistake 2: CAP_CPU_PERCENT = 100

This defeats the purpose. 100% means no throttling. If you set it to 100, remove Resource Governor and save the complexity.

Mistake 3: Ignoring Queue Depth

Resource Governor throttles CPU, but if your activation procedure is genuinely slow, CAP_CPU_PERCENT alone won't help. You must optimize the procedure itself: reduce I/O, batch efficiently, use proper indexing.

-- Monitor queue depth
SELECT
  name,
  COUNT(*) AS message_count
FROM sys.service_queues sq
INNER JOIN sys.service_queue_internal_table sqit
  ON sq.object_id = sqit.object_id
GROUP BY name;

When NOT to Use Resource Governor

If Service Broker is not used on your server—complexity without benefit.
If your activation procedures are already well-optimized and queue depth is minimal.
If you have multiple critical workloads competing for CPU (Resource Governor works better with 2–3 groups, not 10+).

The Bottom Line

Service Broker is powerful but invisible. Without governance, a backed-up queue can silently consume 100% of your server's CPU, and you won't see a long-running query because the work is spread across many background threads.

CAP_CPU_PERCENT is your insurance policy. It keeps Service Broker from starving your OLTP workload while still allowing messages to be processed. Combined with queue monitoring and activation procedure optimization, it's the difference between a managed system and one that randomly experiences "slowness" at 3 AM.