Thursday, September 25, 2008

Debugging SQL Server Service Broker

Thought I would post a few of my tricks for debugging SQL Server Service Broker issues.  Most of these tricks are really targeted towards the cache invalidation patterns that I've been writing and presenting about.

Non-Functional Service Broker or your Error Logs are Growing Out of Control

  1. Check if service broker is enabled:
    SELECT is_broker_enabled FROM sys.databases WHERE name = 'MYDB'
    If it isn't enabled then you'll have to run:
    ALTER DATABASE MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE MYDB SET NEW_BROKER
    ALTER DATABASE MYDB SET MULTI_USER
  2. Look in the SQL Server Error Logs
    1. If you see "The master key has to exist and the service master key encryption is required.", you'll need to run:
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
    2. If you see "Cannot execute as the database principal because the principal "[PRINCIPAL]" does not exist...", you'll need to run:
      sp_changedbowner 'sa'

DB Server Has Huge TempDB Growth and CPU Saturation

  1. Execute a query to determine if you are leaking Service Broker conversations.  This will show all open conversations.  If you've got a lot of conversations in here, you're db is leaking conversations and you're definitely going to have performance problems:
    USE MYDB
    SELECT * FROM sys.conversation_endpoints
  2. Add the Dialog Timer Event Count performance counter.  This counter:
    Dialog Timer Event Count Performance Counter

Some other useful queries are:

  • Transmission Queue View - This view will tell you of all of the messages that are attempting to be sent to a target service.  If the target service is having issues, it's possible for this view to have many rows, causing database growth:
    USE MYDB
    SELECT * FROM sys.transmission_queue
  • Sysprocesses view - This view can provide some information on the Service Broker processes that are running.  The following are service broker processes that you may see. 
    USE MYDB
    SELECT * FROM sys.sysprocesses
    Take a look at the CPU time and IO info that the system views can give you to see if any of these are the culprits of your performance issues.
    • BRKR ASYCN CONN
    • BRKR CMPTLN HDLR
    • BRKR MSG XMITTER
    • BRKR MSG DSPTCHR
    • BRKR EVENT HNDLR
    • BRKR INITIALIZER
    • BRKR TASK