承接上文，本文讲述如何使用系统存储过程来监控系统。

    SQLServer同样也提供了一系列系统存储过程用于监控SQLServer，获取当前进程、会话、请求以及锁定的详细信息。本文将演示系统存储过程来实现这些监控。

情景：

    有时候你会发现应用程序突然变得很慢，经常需要等待数据库响应，此时你需要快速查看是否请求被阻塞或者挂起。

准备工作：

在本文中，将使用以下存储过程来获取当前进程的信息：

• Sp_who
• Sp_who2

步骤：

1、  打开SSMS连到SQLServer实例并打开新查询窗口。

2、  在新查询窗口中输入以下脚本：

USE tempdb
GO

--创建测试表
IF OBJECT_ID('tempdb.dbo.#tbl_SPWho') IS NOT NULL 
    BEGIN
        DROP TABLE tempdb.dbo.#tbl_SPWho
    END

CREATE TABLE tempdb.dbo.#tbl_SPWho
    (
      spid SMALLINT ,
      ecid SMALLINT ,
      [status] NVARCHAR(30) ,
      loginame NVARCHAR(128) ,
      hostName NVARCHAR(128) ,
      blk CHAR(5) ,
      dbname NVARCHAR(128) ,
      cmd NVARCHAR(16) ,
      request_id INT
    )

--从系统存储过程中获取数据并插入临时表中
INSERT  INTO tempdb.dbo.#tbl_SPWho
        EXEC sp_who
GO

--创建存放sp_who2信息的临时表
IF OBJECT_ID('tempdb.dbo.#tbl_SPWho2') IS NOT NULL 
    BEGIN
        DROP TABLE tempdb.dbo.#tbl_SPWho2
    END

CREATE TABLE tempdb.dbo.#tbl_SPWho2
    (
      spid SMALLINT ,
      [status] NVARCHAR(30) ,
      [login] NVARCHAR(128) ,
      HostName NVARCHAR(128) ,
      BlkBy CHAR(5) ,
      DBName NVARCHAR(128) ,
      Command NVARCHAR(16) ,
      CPUTime INT ,
      DiskIO INT ,
      LastBatch NVARCHAR(50) ,
      ProgramName NVARCHAR(100) ,
      SPID2 SMALLINT ,
      REQUESTID INT
    )

--从系统存储过程中获取数据并插入临时表中
INSERT  INTO tempdb.dbo.#tbl_SPWho2
        EXEC sp_who2
GO

--查看特定数据库的数据
SELECT  spid AS SessionID ,
        ecid AS ExecutionContextID ,
        [status] AS ProcessStatus ,
        loginame AS LoginName ,
        hostname AS HostName ,
        blk AS BlockedBy ,
        dbname AS DatabaseName ,
        cmd AS CmomandType ,
        request_id AS RequestID
FROM    dbo.#tbl_SPWho
WHERE   dbname = 'AdventureWorks'
GO

--仅查看阻塞的数据
SELECT  spid AS SessionID ,
        ecid AS ExecutionContextID ,
        [status] AS ProcessStatus ,
        loginame AS LoginName ,
        hostname AS HostName ,
        blk AS BlockedBy ,
        dbname AS DatabaseName ,
        cmd AS CmomandType ,
        request_id AS RequestID
FROM    dbo.#tbl_SPWho
WHERE   blk > 0
GO

--查看挂起的数据
SELECT  spid AS SessionID ,
        ecid AS ExecutionContextID ,
        [status] AS ProcessStatus ,
        loginame AS LoginName ,
        hostname AS HostName ,
        blk AS BlockedBy ,
        dbname AS DatabaseName ,
        cmd AS CmomandType ,
        request_id AS RequestID
FROM    dbo.#tbl_SPWho
WHERE   [STATUS] = 'suspended'
GO

分析：

       本例中，创建了两个临时表，用于存放sp_who 和sp_who2存储过程返回的数据结果，然后通过INSERT…EXECUTE命令把结