Microsoft® SQL Server® 2012 Bible

657

Chapter 25: Asynchronous Messaging with Service Broker

25

TABLE 25 -1 Resources to Monitor Service Broker Activity

Resource Description

SQL Profi ler SQL Profi ler provides the same 13 trace events available since SQL Server

2005.

Extended

Events

Extended Events is the preferred method for troubleshooting and monitoring

Service Broker activity. For a complete list and description of the Service

Broker events available in SQL Server 2012, refer to http://technet

.microsoft.com/en-us/library/ms186347.aspx.

ssbdiagnose

Utility

The ssbdiagnose utility introduced in SQL Server 2008R2 is still supported

and is a great way to easily report issues in Service Broker conversations or

service confi guration issues. For more information on the ssbdiagnose utility,

refer to http://msdn.microsoft.com/en-us/library/bb934450.aspx.

Catalog Views SQL Server 2012 provides 12 catalog views for Service Broker. Table 25-2 lists

and describes each of these Catalog Views.

Dynamic

Management

Views (DMVs)

SQL Server 2012 provides four basic DMVs to monitor tasks, messages,

queues, and connections. Table 25-3 lists and describes each of these DMVs.

Table 25-2 describes the Service Broker Catalog Views available in SQL Server 2012.

TABLE 25 -2 Service Broker Catalog Views

Catalog View Description

sys.conversation_endpoints This catalog view contains a row per conversation

endpoint in the database.

sys.conversation_groups This catalog view contains a row for each conversa-

tion group.

sys.conversation_priorities Contains a row for each conversation priority created

in the current database.

sys.remote_service_bindings This catalog view contains a row per remote service

binding.

sys.routes This catalog views contains one row per route.

Service Broker uses routes to locate the network

address for a service.

sys.service_contracts This catalog view contains a row for each contract in

the database.

sys.service_contract_message_usages This catalog view contains a row per (contract, mes-

sage type) pair.

Continues

c25.indd 657c25.indd 657 7/31/2012 9:21:38 AM7/31/2012 9:21:38 AM

http://www.it-ebooks.info