title: SQL Server Service Broker: how to determine overhead or max performance?
body: <p>We are trying to determine whether we are using Service Broker appropriately and getting the max performance out of it.  We have been tweaking our SB conversations and handling, and have gone from 3000/minute to 8000/minute, but CPU has stayed constant at 100%.  Additionally, on some days the SB queue stays empty, but on similar-traffic days the queue can back up by 500k.  </p>
  
  <p>The machine is a quad-quad (16 cores), no HT, 32gb RAM and 26gb assigned to SQL Server, with AWE enabled.</p>
  
  <p>SQL Server 2008 SP1 (no CUs), Enterprise Edition.
  Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7600: ) </p>
  
  <p>The messages are inserted into a service broker queue, which pulls groups of messages and runs them through a CLR, which parses the XML (not a simple parse, alas) and inserts into a table.  The CLR is considerably faster than our T-SQL code.</p>
  
  <p>We have an average of 35 runnable tasks per scheduler</p>
  
  <p>We run nightly stats/index maintenance.</p>
  
  <p>We have set server MAXDOP = 1 to try and help performance.  </p>
  
  <p>We've upped our number of tempdb files to 64 to avoid SGAM contention, which combined with TF1118 seems to have stopped TEMPDB contention.  </p>
  
  <p>Looking at sys.dm_os_waiting_tasks, we typically have ~60 tasks waiting on THREADPOOL, with only a handful on other types.</p>
  
  <p>Our signal waits are 70% (resource waits = 30%).  </p>
  
  <p>We've verified that the TokenAndUserPermCache stays under 20mb.</p>
  
  <p>Looking at sys.dm_os_latch_stats, we see 40-200k BUFFER latches in 1 minute, which are mostly on sysdesend and a user table we use to deal with Dialogs.</p>
  
  <p>We also see high SOS_SCHEDULER_WAIT, which also indicates CPU pressure.  But is that because of the CLR being freakishly busy, or because of Service Broker overhead?  I'll happily provide code - let me know what I need to post here.</p>
  
  <p>Thanks in advance.</p>