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

Monday, September 15, 2008

Giving Something New a Try

I've decided to leave my job of almost 9 years, the .NET platform, and Windows all in one fell swoop, and give something new a try. At the beginning of October, I'll be back working for a small company called NextPoint as a Software Engineer doing Ruby development on OS X. Big change indeed.

I'm extremely excited to be working on something so different, and ecstatic about working with another fantastic small team of 4 smart folks. I'll be working for a boss that I've worked for in the past, and have a huge amount of respect for and trust in.

What hasn't changed is that I'll still be doing software development for the legal vertical, so I'll get to stay in a domain that I've gained a lot of knowledge in over the years. Hopefully I'll be able to provide some of that knowledge to this new company.

I want it to be clear that I'm not running away from the .NET community, or being driven away. I'm so grateful to this community and the support it's given me. I still plan on trying to stay involved and hopefully I'll be able to be able to provide a different perspective on .NET and C# in specific, to the folks in the .NET community.

Anyway, thanks to the folks that got me started here, and those that provided support over the years (indirectly and directly). It's much appreciated.

Saturday, September 06, 2008

Codeapalooza Slides and Code Samples

Hey All

Yes, we had some pretty nasty hardware issues at today's presentation.  If you were there, I really apologize for that.  Hopefully once we worked out the kinks, you were able to glean some of the great stuff here.

I've posted the slides and code samples.

Thursday, September 04, 2008

Codeapalooza this Saturday!!

Don't forget that Codeapalooza is this Saturday.  Lot's of awesome presentations are lined up, including my presentation on ADO.NET Data Services.

Don't miss it!