ServiceBroker相关Sys系统表

1、显示服务：sys.services

• name    [服务名称]
• service_id    [服务的对象ID]
• principal_id    [拥有这个契约的principal ID]
• service_queue_id    [服务队列的对象ID]

2、显示服务契约：sys.service_contracts

• name [契约名称]
• service_contract_id [契约的对象ID]
• principal_id [拥有这个契约的principal ID]

3、显示契约中使用了哪些消息类型：sys.service_contract_message_usages

• service_contract_id [服务契约的对象ID]
• message_type_id [上面的契约所使用的消息类型的对象ID]
• is_sent_by_initiator [如果为1，消息类型是由源发送的]
• is_sent_by_target [如果为1，消息类型是由目标发送的]

4、显示消息类型：sys.service_message_types

• name[消息类型名称]
• message_type_id[消息类型的对象ID]
• principal_id[拥有这个契约的principal ID]
• validation[消息类型请求的验证类型。可以是以下值：

            N表示无（即没有验证）
             X表示XML（由Service Broker验证XML）
             E表示空（消息主体总是为空）]

• validation_desc [上面列的具体文本，值为NONE、XML或EMPTY]
• xml_collection_id [XML集合的对象ID]

5、显示服务队列定义：sys.service_queues

• max_readersService Broker [同时运行的队列读取器的最大数量]
• activation_procedure[已激活的存储过程的名称]
• execute_as_principal_id[已激活的存储过程将作为这个principal运行]
• is_activation_enabled[如果值为1，在这个队列上启用了激活]
• is_receive_enabled[如果值为1，服务可以在这个队列上发送RECEIVE命令]
• is_enqueue_enabled[如果值为1，服务可以在这个队列上发送SEND命令]
• is_retention_enabled[如果值为1，在这个队列上启用了保持]

6、显示哪个服务正在使用这个队列：sys.service_queue_usages

• service_id[服务的对象ID]
• service_queue_id[队列的对象ID]

7、显示针对其他Broker的路由：sys.routes

• name[路由的唯一名称]
• route_id[路由的对象ID]
• principal_id[拥有这个路由的principal ID]
• remote_service_name远程服务名称]
• broker_instance[远程Service Broker的唯一实例标识符，这个Service Broker拥有上面的远程服务]
• lifetime[这个路由过期的日期和时间（UTC时间）]
• address[远程Service Broker的网络地址]
• mirror_address[远程Service Broker镜像的网络地址。这个列是可选的，仅在Service Broker启用了数据库镜像之后使用]

8、显示远程服务绑定：sys.remote_service_bindings

• Name[远程服务绑定的唯一名称]
• remote_service_binding_id[远程服务绑定的对象ID]
• principal_id[拥有这个远程服务绑定的principal ID]
• remote_service_name[远程服务名称]
• service_contract_id[服务契约的对象ID]
• remote_principal_id[远程principal的对象ID。当与该服务通信时，使用与这个principal关联的证书]
• is_anonymous_on[如果值为1，这个远程服务的会话将是匿名的，即无法知道哪个用户开始了这个会话]

9、显示端点:sys.service_broker_endpoints

• is_message_forwarding_enabled[如果值为1，这个端点支持转发消息]
• message_forwarding_size[tempdb中用于转发消息的空间的大小，单位MB]
• connection_auth[使用这个端点的连接所需要的身份认证类型，可以是以下值：

1表示NTLM
2表示Kerberos
3表示Negotiate
4表示证书
5表示NTLM、证书
6表示Kerberos、证书
7表示Negotiate、证书
8表示证书、NTLM
9表示证书、Kerberos
10表示证书、Negotiate]

• connection_auth_desc[以上这些值的文本表示，可以是以下值：

NTLM
KERBEROS
NEGOTIATE
CERTIFICATE
NTLM, CERTIFICATE
KERBEROS, CERTIFICATE
NEGOTIATE, CERTIFICATE
CERTIFICATE, NTLM
CERTIFICATE, KERBEROS
CERTIFICATE, NEGOTIATE]

• certificate_id[用于身份认证的证书的对象ID]
• encryption_algorithm[加密算法：

0表示None
1表示RC4
2表示AES
3表示None、RC4
4表示None、AES
5表示RC4、AES
6表示AES、RC4
7表示None、RC4、AES
8表示None、AES、RC4]

• encryption_algorithm_desc[以上这些值的文本表示，可以是以下值：

NONE
RC4
AES
NONE, RC4
NONE, AES
RC4, AES
AES, RC4
NONE, RC4, AES
NONE, AES, RC4]

10、显示会话端点:sys.conversation_endpoints

• conversation_handle[用于通过编程方式访问这个会话的唯一标识符]
• conversation_id[会话的唯一标识符]
• is_initiator[如果值为1，这个端点就是会话的开始点。如果值为0，这个端点就是会话的目标点]
• service_contract_id[服务契约的对象ID]
• conversation_group_id[拥有这个会话的会话组的唯一标识符]
• service_id[服务的对象ID]
• lifetime[这个会话过期的日期和时间]
• State[这个会话的当前状态：

SO表示开始对外会话
SI表示开始对内会话
CO表示交谈
DI表示断开的对内会话
DO表示断开的对外会话
ER表示错误
CD表示关闭]

• state_desc[以上状态字段的文字描述，值如下所示：

STARTED_OUTBOUND
STARTED_INBOUND
CONVERSING
DISCONNECTED_INBOUND
DISCONNECTED_OUTBOUND
ERROR
CLOSED]

• far_service[远端服务名称]
• far_broker_instance[远端Service Broker的唯一标识符]
• principal_id[拥有本地会话使用的证书的principal对象ID]
• far_principal_id[拥有远端会话使用的证书的principal对象ID]
• outbound_session_key_identifier[用于对外加密的唯一标识符]
• inbound_session_key_identifier[用于对内加密的唯一标识符]
• security_timestamp[本地会话键创建的时间]
• dialog_timer[Service Broker发送DialogTimer消息的时间]
• send_sequence[这个号码表示发送序列中下一个号码]
• last_send_tran_id[最后一个发送操作的事务标识符]
• end_dialog_sequence[结束对话框消息的序列号码]
• receive_sequence[这个号码表示下一个等待接受的序列号码]
• receive_sequence_frag[这个号码表示下一个等待接受的消息片段序列号码]
• system_sequence[这个会话中看到的系统消息的最后一个序列号码]
• first_out_of_order_sequence[第1个次序颠倒的消息的序列号码]
• last_out_of_order_sequence[最后一个次序颠倒的消息的序列号码]
• last_out_of_order_frag[最后一个次序颠倒的消息片段的序列号码]
• is_system[如果值为1，这就是一个系统对话框]

11、显示会话组:sys.conversation_groups

• conversation_group_id [拥有这个会话的会话组的唯一标识符]
• service_id [服务的对象ID]
• is_system [如果值为1，这就是一个系统级会话组，并不是供用户使用的]

12、显示队列内容:select * from <user_queue_name>

• status [消息的状态。可以是以下值：

0表示已收到的消息
1表示准备好了
2表示还没有完成
3表示保留的发送消息]

• queuing_order[队列中的消息顺序号]
• conversation_group_id[拥有这个会话的会话组的唯一标识符]
• conversation_handle[用于通过编程方式访问这个会话的唯一标识符]
• message_sequence_number[会话流中这个消息的相对位置]
• service_name[这个消息发送到的服务的名称]
• service_id[服务的对象ID]
• service_contract_name[服务契约的名称]
• service_contract_id[服务契约的对象ID]
• message_type_name[消息的类型名称]
• message_type_id[消息类型的对象ID]
• validation[消息类型请求的验证类型。可以是以下值：

N表示无（即没有验证）
X表示XML（由Service Broker验证XML）
E表示空（消息主体总是为空）]

• message_body[这个消息的二进制有效内容。注意，这里的数据格式完全由应用程序决定]
• message_id[这个消息的唯一标识符]

13、显示Broker的连接：sys.dm_broker_connections

• connection_id [连接的唯一标识符]
• transport_stream_id [传输流的唯一标识符]
• state [连接的当前状态：

1表示新连接
2表示正在连接中
3表示已连接
4表示登录成功
5表示连接关闭]

• state_desc[上面状态的文字描述：NEW、CONNECTING、CONNECTED、LOGGED_IN和CLOSED]
• connect_time[连接打开的日期和时间]
• login_time[登录发生的日期和时间]
• authentication_method[Windows所使用的身份认证方式]
• principal_name[用于验证连接权限的登录名]
• remote_user_name[用于做Windows身份认证的远程用户名]
• last_activity_time[连接最后一次发送和接收数据的日期和时间]
• is_accept[如果值为1，这个连接是从远程获得的如果为0，这个连接是从本地建立的]
• login_state[这个数字表示登录过程的当前状态：

0表示Initial
1表示Negotiate
2表示SSPI
3表示Public Key Login
4表示Public Key Tentative
5表示Logged In
6表示Arbitration]

• login_state_desc[以上状态字段的文字描述，值如下所示：

Initial
Negotiate
SSPI
PublicKeyLogin
PublicKeyTentative
LoggedIn
Arbitration]

• peer_certificate_id[身份验证远程实例所用证书的本地对象ID]
• receives_posted[这个连接尚未完成的异步网络接收数目]
• is_receive_flow_controlled[如果值为1，表示由于网络流控制，网络接收已经延迟]
• sends_posted[这个连接尚未完成的异步网络发送数目]
• is_send_flow_controlled[如果值为1，表示由于网络流控制，网络发送已经延迟]
• total_bytes_sent[这个连接上总共的发送字节数]
• total_bytes_received[这个连接上总共的接收字节数]
• total_fragments_sent[这个连接上总共发送的消息片段数目]
• total_fragments_received[这个连接上总共接收的消息片段数目]
• total_sends[这个连接上总共网络发送请求数]
• total_receives[这个连接上总共网络接收请求数]
• encalg[所使用的加密算法：

1表示没有加密
2表示RC4
3表示AES]

13、显示活动的任务:sys.dm_broker_activated_tasks

• spid [已激活的存储过程的会话ID]
• database_id [数据库的对象ID]
• queue_id [服务队列的对象ID]
• procedure_name [已激活的存储过程的名称]
• execute_as [运行存储过程的用户的对象ID]

14、显示队列监视器:sys.dm_broker_queue_monitors

• database_id [数据库的对象ID]
• queue_id [服务队列的对象ID]
• state [队列监视器的当前状态，可以是以下值：

INACTIVE
NOTIFIED
RECEIVES_OCCURRING]

• last_empty_rowset_time[最后一次RECEIVE返回一个空行集的日期和时间]
• last_activated_time[最后一次存储过程被激活的日期和时间]
• tasks_waiting[当前正在等待消息的RECEIVE语句数量]

15、数据库列表：sys.databases

• service_broker_guid [这个数据库中的Service Broker的唯一标识符]
• is_broker_enabled [如果值为1，表示这个数据库上启用了Service Broker]

16、数据库所有端点列表：sys.endpoints

• Name [这是该端点的唯一名称。你可以决定端点的名字，但是你必须给出一种方法防止出现重名]
• endpoint_id [这是一个唯一的整型值，它也定义了这个端点。由于这是一个整型值，所以对于与其他表做JOIN十分有用，它可能是其他视图的外键]
• principal_id [拥有这个端点的SQL Server principle的标识符ID]
• Protocol [整型值，用于识别这个端点使用了什么协议。Service Broker端点总是使用TCP/IP作为协议，所以这里的值是2]
• protocol_desc [这个字段包含了对该端点协议设置的文字描述，Service Broker端点总是显示TCP]
• Type [这个字段描述了端点负载的类型。所有的Service Broker端点都是3]
• type_desc [Type字段的文字描述。对于Service Broker端点，这里是SERVICE_BROKER]
• State [从诊断的角度看，下面两列最重要，因为它们描述了Service Broker端点的当前状态。值如下所示：

0表示端点处于启动状态（Started）
1表示端点处于停止状态（Stopped）
2表示端点处于禁用状态（Disabled）]

• state_desc[状态字段的文字描述。值如下所示：STARTED、STOPPED或DISABLED]
• is_admin_endpoint[对于Service Broker端点，这个字段总是0]

17、数据库安全证书：sys.certificates
   当配置Service Broker把Kerberos作为安全对话框的加密方式时，你会把公钥和私钥保存在证书中，而这些证书会导入当前数据库。另外，为了防止配置错误，用于安全对话框的证书必须这样表示。sys.certificates视图包含一个is_active_for_begin_dialog列，是一个位类型的字段。如果位值为1，表示这个证书是由Service Broker使用的。如果值为0，Service Broker就无法使用这个证书。
   尝试使用一个没有被标记为激活的BEGIN DIALOG证书会导致一个错误，该错误会在sys. transmission_queue/transmission_status中显示。要在现有的证书上启用这个标记，可以使用ALTER CERTIFICATE命令，然后设置WITH ACTIVE FOR BEGIN DIALOG = ON。

18、对称密钥：sys.symmetric_keys
   Service Broker需要为适当的安全通信操作准备两个对称密钥。服务主密钥（Service Master Key）和数据库主密钥（Database Master Key）以行的形式保存在这个视图中，如果这两个密钥没有找到，你会收到一个来自Service Broker的错误。要创建这两个密钥其实很简单，可以使用CREATE SYMMETRIC KEY命令。
19、Service Broker任务：sys.sysprocesses
   许多服务器中的后台任务都为Service Broker处理服务。cmd列显示了任务的名称。有关Service Broker的任务以及它们的职责如下所示。

• BRKR ASYNC CONN。这个任务负责连接远端Service Broker。
• BRKR CMPLTN HDLR。这个任务负责处理IO完成（IO completion）。
• BRKR MSG XMITTER。这是消息发送器任务。所有通过网络发送的消息都要通过这个任务。
• BRKR MSG DSPTCHR。这个消息分配器负责在本地和远程发送消息。
• BRKR EVENT HNDLR。这个任务负责处理内部事件。
• BRKR INITIALIZER。SQL Server在被要求启动之后快速初始化，这样，系统的用户可以快速开始使用系统。但是前提是你有一个包含数百万激活对话框的数据库。启动的时间可能比较长。Service Broker初始化任务允许服务器继续启动，然后在后台异步初始化激活的Service Broker。在通常情况下，在后台看这个任务不会有任何警告。但如果系统已经运行了很长时间，或者并非大量对话框要初始化，这个任务不应该被绕过。
• BRKR TASK。这是一个内部激活的存储过程。
• DBCC SSB CHECK。这个任务会检查数据库的一致性。只有在DBCC CHECKDB运行时，你才会看到这个任务。

20、Service Broker作整体监控：Perfmon
   在Perfmon中找到的Service Broker信息确实要更加全局化，因此相对于服务级别的问题诊断，它更适合于对Service Broker作整体监控。与SQL Profiler一样，Perfmon中收集到的信息也可以由SQL Server的SQLDiag工具收集，但SQLDiag并不需要Perfmon。有3种Perfmon计数器分类： Broker激活、Broker统计和Broker/DBM传输。接下来的内容描述了这些计数器的用途。

1）Broker激活
这些计数器负责监视Service Broker中即将发生的激活数量。注意，这些计数器是对整个Service Broker做监控的，而不是对某个特定的服务监控

(负责监视Service Broker中即将发生的激活数量的计数器)

计数器名称

描述

Stored Procedures Invoked/sec

Service Broker中每秒调用的已激活的存储过程的数量

Task Limit Reached

Service Broker中已激活任务到达队列的上限时间

Task Limit Reached/sec

上面的计数器的每秒速率

Tasks Running

目前正在Service Broker中运行的已激活任务的数量

Tasks Started/sec

Service Broker中每秒调用的已激活的任务数量

2）Broker统计
这些计数器负责监视某个特定的Service Broker的所有操作。它们可以告诉你那个Service Broker的整体健康状况

(监视某个特定的Service Broker所有操作的计数器)

计数器名称

描述

Broker Transaction Rollbacks

这个Service Broker检测到的事务回滚数

Dialog timer event count

Service Broker中当前处于激活状态的对话框计时器的数量

Enqueued Local Messages Total

Service Broker发出的消息总数。这里的数量仅包括从本地发送的消息，而不包括从网络到达的消息

Enqueued Local Messages/sec

上面的计数器的每秒速率

Enqueued Messages Total

进入本地队列的所有消息的数量

Enqueued Messages/sec

上面的计数器的每秒速率

Enqueued Transport Msg Frag Tot

一个队列中消息片段的总数。注意，在传输过程中一个大消息会自动被分成许多小的片段

Enqueued Transport Msg Frags/sec

上面的计数器的每秒速率

Enqueued Transport Msgs Total

Service Broker发出的消息总数。这个数字仅包括通过网络到达的消息数

Enqueued Transport Msgs/sec

上面的计数器的每秒速率

Forwarded Messages Total

这个Service Broker转发的消息总数

Forwarded Messages/sec

上面的计数器的每秒速率

Forwarded Msg Byte Total

这个Service Broker转发的字节总数

Forwarded Msg Bytes/sec

上面的计数器的每秒速率

Forwarded Msg Discarded Total

这个Service Broker丢弃的转发消息总数

Forwarded Msg Discarded/sec

上面的计数器的每秒速率

Forwarded Pending Msg Bytes

所有的等待被发送的转发消息的字节总数

Forwarded Pending Msg Count

当前等待被发送的转发消息的数量

SQL RECEIVE Total

已经处理的T-SQL RECEIVE命令总数

SQL RECEIVEs/sec

上面的计数器的每秒速率

SQL SEND Total

已经处理的T-SQL SEND命令总数

SQL SENDs/sec

上面的计数器的每秒速率

3）Broker/DBM传输
这些计数器是由Service Broker和数据库镜像共享的传输层所使用的

（由Service Broker和数据库镜像共享的传输层所使用的计数器）

计数器名称

描述

Current Bytes for Recv I/O

这次传输读取的当前字节数

Current Bytes for Send I/O

这次传输通过网络发送的当前字节数

Current Msg Frags for Send I/O

当前通过网络发送的消息片段数量

Message Fragment Send Size Avg

通过网络发送的消息片段的平均大小，单位是字节

Message Fragment Send Total

通过网络发送的消息片段总数

Message Fragment Sends/sec

上面的计数器的每秒速率

Message Fragment Receive Total

从网络收到的消息片段总数

Message Fragment Receives/sec

上面的计数器的每秒速率

Message Fragment Recv Size Avg

从网络收到的消息片段的平均大小

Open Connection Count

当前打开的连接数

Pending Bytes for Recv I/O

Service Broker传输从网络收到的字节数，但是这些字节还没有放入队列或还未丢弃

Pending Bytes for Send I/O

等待通过网络发送的消息片段的字节数

Pending Msg Frags for Recv I/O

Service Broker传输从网络收到的消息片段数，但是这些字节还没有放入队列或还未丢弃

Pending Msg Frags for Send I/O

等待通过网络发送的消息片段的字节数

Receive I/O Bytes Total

从网络收到的字节总数

Receive I/O Bytes/sec

上面的计数器的每秒速率

Receive I/O Len Avg

每次通过网络接收的平均字节数

Receive I/Os/sec

每秒完成的传输接收IO操作数

Send I/O Bytes Total

通过网络发送的字节总数

Send I/O Bytes/sec

上面的计数器的每秒速率

Send I/O Len Avg

每次通过网络发送的平均字节数

Send I/Os/sec

每秒完成的传输发送IO操作数

21、检测和修复带有破损数据的数据库：DBCC CHECKDB
   DBCC CHECKDB命令允许你检测和修复带有破损数据的数据库。虽然这个命令不是Service Broker特有的工具，但它确实可以分析出许多种类的数据库破损。其中包括引用了丢失消息类型的契约、引用了丢失队列的服务。在普通的Service Broker操作中，不会发生这些错误。例如，你无法丢弃一个正在使用中的消息类型。但是，由于一些失败导致Service Broker的元数据被破坏，所以才有了这个命令。
   注意，虽然DBCC CHECKDB有选项可以修复遭到破坏的Service Broker元数据，但你要理解要把数据库变回到一致状态并不容易，通常会有很多限制。在上面的例子中，一个契约丢失了所需的消息类型。DBCC CHECKDB无法创建丢失的消息类型，因为它不知道任何有关这一类型的信息，它只能移除丢失了消息类型的契约。如果没有其他错误，在这个命令完成之后数据库将处于一致状态。当然，如果有一个服务引用了这个契约，单单移除这个契约是不够的，这个服务也要移除。这种迭代移除将一直继续，直到数据库保持一致状态为止。
   通常更好的解决方案是，从未受破坏的备份中恢复遭到破坏的数据库。但有时这不行，因此需要修复。