Service Broker & CAP_CPU_PERCENT

-- Service Broker für eine Datenbank aktivieren
ALTER DATABASE [MeineDB] SET ENABLE_BROKER;

-- Nachrichtentyp erstellen
CREATE MESSAGE TYPE [//MyApp/RequestMessage] VALIDATION = WELL_FORMED_XML;

-- Kontrakt erstellen
CREATE CONTRACT [//MyApp/SampleContract] (
  [//MyApp/RequestMessage] SENT BY INITIATOR
);

-- Warteschlange und Dienst erstellen
CREATE QUEUE [dbo].[TargetQueue];
CREATE SERVICE [//MyApp/TargetService]
  ON QUEUE [dbo].[TargetQueue]
  ([//MyApp/SampleContract]);

-- Ressourcenpool mit Hard Cap von 30 % CPU erstellen
CREATE RESOURCE POOL PoolLimit
WITH (
  MIN_CPU_PERCENT = 0,
  MAX_CPU_PERCENT = 100,
  CAP_CPU_PERCENT = 30
);
GO

-- Resource Governor neu konfigurieren
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- RESOURCE_GOVERNOR_IDLE in den Wartestatistiken anzeigen
SELECT 
  wait_type,
  waiting_tasks_count,
  wait_time_ms,
  max_wait_time_ms,
  signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_GOVERNOR_IDLE'
ORDER BY wait_time_ms DESC;

-- Ringbuffer-Einträge für das Phänomen finden
SELECT * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER'
  AND record LIKE '%SCHEDULER_IDLE_ENQUEUE%';

-- 1. Ressourcenpool erstellen (weicher CPU-Limit, kein Hard Cap)
CREATE RESOURCE POOL PoolBroker
WITH (
  MIN_CPU_PERCENT = 0,
  MAX_CPU_PERCENT = 30, -- weiches Limit, nur bei Konflikten
  CAP_CPU_PERCENT = 100 -- de facto deaktiviert
);
GO

-- 2. Workload-Gruppe für Service Broker
CREATE WORKLOAD GROUP GroupBroker
USING PoolBroker;
GO

-- 3. Klassifizierungsfunktion erstellen (Beispiel: alle Sitzungen mit 'BROKER' in der Anwendung)
CREATE OR ALTER FUNCTION dbo.ClassifierBroker()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @group_name SYSNAME = N'default';
  IF APP_NAME() LIKE N'%ServiceBroker%'
    SET @group_name = N'GroupBroker';
  RETURN @group_name;
END;
GO

-- 4. Resource Governor mit neuer Klassifizierung konfigurieren
ALTER RESOURCE GOVERNOR
  WITH (CLASSIFIER_FUNCTION = dbo.ClassifierBroker);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Aktive Service Broker-Tasks anzeigen
SELECT 
  session_id,
  task_address,
  state_desc
FROM sys.dm_broker_activated_tasks;

-- Resource Pool-Zuordnung für aktive Sitzungen prüfen
SELECT 
  s.session_id,
  s.program_name,
  rp.name AS resource_pool
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups wg
  ON s.group_id = wg.group_id
JOIN sys.dm_resource_governor_resource_pools rp
  ON wg.pool_id = rp.pool_id
WHERE s.program_name LIKE '%Service Broker%';