第三章——使用系统函数、存储过程和DBCC SQLPERF命令来监控SQLServer（1）[转]

忘了说明：本系列文章出自《Microsoft SQL Server 2012 Performance Tuning Cookbook》，将会陆续推出译文，但是由于工作需要，没有按顺序贴出来。

本系列文章包含三部分：

1、  使用系统统计函数（system statistical functions）来监控系统健康程度。

2、 使用系统存储过程来监控SQLServer进程和会话。

3、  使用DBCC SQLPERF命令来监控日志空间使用情况。

 

前言：

         SQLServer提供了一些系统函数、系统存储过程和DBCC命令来分析SQLServer性能相关的问题，不过绝大部分这些工具所获得的信息都可以通过DMVs和DMFs来获得。很多人依旧使用本文的工具的原因是因为他们长期使用这些工具来监控SQLServer，已经成为了一个习惯，所以为了向后兼容，微软依旧保留这些工具，但是建议新入门的人尽可能从DMO（DMVs和DMFs的统称）中获取信息。

 

      性能监控有很多工具，SQL Profiler、扩展事件、DMO及本系列文章提供的工具等，来获取信息，对于简单的性能问题，使用某一种即可，但是对于复杂的性能问题，往往需要多个工具协同使用。

 

下面先介绍使用系统统计函数来监控SQLServer的健康程度。

 

使用系统统计函数来监控SQLServer

 

        SQLServer提供了一些列非常有用的系统统计函数来监控当前SQLServer的状态。这些函数用于检查和监控服务器的健康状态非常有效。

 

       现在假设一个情况，在你的数据库环境中，一个web应用程序对数据集的操作是一行一行的。为了读取每一行，应用程序会在数据库中往返访问，导致经常需要开启新的连接。为了处理这个问题，需要经常监控SQLServer的连接数，下面将演示如何操作。

 

准备工作：

 

SQLServer提供了下面这些有用的系统函数：

 

@@CONNECTIONS

@@TIMETICKS

@@CPU_BUSY

@@IDLE

@@IO_BUSY

@@PACK_RECEIVED

@@PACK_SENT

@@PACKET_ERRORS

@@TOTAL_READ

@@TOTAL_WRITE

@@TOTAL_ERRORS

 

本例子中将使用这些函数，并创建脚本来获取信息。

 

环境准备：

 

使用SQLServer2008企业版（本机只有企业版）和示例数据库AdventureWorks。

 

步骤：

 

1、  打开SQLServer（这里使用SQLServer Management Studio后面简称SSMS），然后新开一个查询窗口（ctrl+m）。

 

2、  在窗口上输入一下脚本：

 

--创建一个表来存储统计信息
IF OBJECT_ID('[dbo].[tbl_ServerHealthStatistics]') IS NULL
    BEGIN
        CREATE TABLE [dbo].[tbl_ServerHealthStatistics]
            (
              ID INT IDENTITY(1, 1) ,
              StatDateTime DATETIME DEFAULT GETDATE() ,
              TotalConnections INT ,
              TimeTicks INT ,
              TotalCPUBusyTime INT ,
              TotalCPUIdleTime INT ,
              TotalIOBusyTime INT ,
              TotalReceivedPackets INT ,
              TotalSentPackets INT ,
              TotalErrorsInNetworkPackets INT ,
              TotalPhysicalReadOperations INT ,
              TotalWriteOperations INT ,
              TotalReadWriteErrors INT
            )
    END
GO

--收集信息到表中
INSERT  INTO [dbo].[tbl_ServerHealthStatistics]
        ( TotalConnections ,
          TimeTicks ,
          TotalCPUBusyTime ,
          TotalCPUIdleTime ,
          TotalIOBusyTime ,
          TotalReceivedPackets ,
          TotalSentPackets ,
          TotalErrorsInNetworkPackets ,
          TotalPhysicalReadOperations ,
          TotalWriteOperations ,
          TotalReadWriteErrors
        )
        SELECT  @@CONNECTIONS TotalConnections ,
                @@TIMETICKS TimeTicks ,
                @@CPU_BUSY TotalCPUBusyTime ,
                @@IDLE TotalCPUIdleTime ,
                @@IO_BUSY TotalIOBusyTime ,
                @@PACK_RECEIVED TotalReceivedPackets ,
                @@PACK_SENT TotalSentPackets ,
                @@PACKET_ERRORS TotalErrorsInNetworkPackets ,
                @@TOTAL_READ TotalPhysicalReadOperations ,
                @@TOTAL_WRITE TotalWriteOperations ,
                @@TOTAL_ERRORS TotalReadWriteErrors

 

3、运行下面脚本，显示收集的服务器信息：

 

WITH    cteStatistics
          AS ( SELECT   *
               FROM     [dbo].[tbl_ServerHealthStatistics]
             )
    SELECT  Cur.TotalConnections AS CurrentConnections ,
            Cur.StatDateTime AS CurrentStatDateTime ,
            Prev.TotalConnections AS PreviousConnections ,
            Prev.StatDateTime AS Previous_StatDateTime ,
            Cur.TotalConnections - Prev.TotalConnections AS ConnectionsIncreamentedBy ,
            DATEDIFF(millisecond, Prev.StatDateTime, Cur.StatDateTime) AS ConnectionsIncreamentedIn
    FROM    cteStatistics AS Cur
            LEFT JOIN cteStatistics AS Prev ON Cur.ID = Prev.ID + 1

 

分析：

        上面例子中，先创建一个表[dbo].[tbl_ServerHealthStatistics]，在创建之前，使用OBJECT_ID()函数来检查是否存在该表，如果存在则不创建，这是一个良好的编程习惯，建议在创建表（无论是实体表还是临时表）时使用。可以确保脚本可重复执行。

 

        步骤2的脚本中，通过INSERT..SELECT语句来收集数据并插入到表中。

 

        步骤3中，由于需要对比两行之间的数据，所以使用CTE（2005之前可以使用临时表）来暂时存放数据然后与目前数据做对比。

 

 

扩展信息：

 

下面是这些系统统计函数的简介，这些函数均返回从SQLServer启动以来的汇总值。

 

       @@Connections：这个函数返回SQLServer自启动以来，尝试连接到SQLServer的连接数，是一个数值型结果。不管这些连接是否成功，均会记录在里面。

 

    @@MAX_CONNECTIONS：返回允许同时连接的最大连接数，这个数与使用sp_configure 来配置的Max Connections值相同。也和SQLServer的版本和应用程序、硬件的限制有关。

 

    @@TIMETICKS：返回一个微妙级别的计数点。这个值依赖于操作系统的时间系统。通常为31.50毫秒。

 

       @@CPU_BUSY ：返回自SQLServer服务启动以来的工作时间，结果为所有CPU事件的累计，所以可能会超出实际时间，乘以@@TIMETICKS即可换成为妙。注意：如果@@CPU_BUSY 或 @@IO_BUSY 中返回的时间超过累积的 CPU 时间约 49 天，则您将收到算术溢出警告。在这种情况下，@@CPU_BUSY、@@IO_BUSY 和 @@IDLE 变量值并不精确。

 

    @@IDLE：表示SQLServer空闲时的CPU时间。在多处理器情况下，返回值为所有CPU的汇总。

 

    @@IO_BUSY：返回SQLServer自启动以来执行输入输出操作的CPU总数。

 

    @@PACK_RECEIVED：返回SQLServer接收到的网络包总数。

 

    @@PACK_SENT：返回SQLServer发送的网络报总数。

 

    @@PACKET_ERRORS：返回SQLServer所遇到过的网络包错误的总数。

 

    @@TOTAL_READ：返回SQLServer所执行过的所有物理读操作总数。

 

    @@TOTAL_WRITE：返回所有物理写操作的总数。

 

    @@TOTAL_ERRORS：返回SQLServer遇到过的所有读写操作的错误总数。

 

注意：@@CPU_BUSY、@@IDLE和@@IO_BUSY返回的值是基于ticks而不是毫秒或者微妙。如果想知道微妙值，可以乘以@@timeticks。

 

        本例中的脚本收集某个时间点的SQLServer信息，可以借助SQLServer Agent，来定期、自动收集，以便用于后续分析之用。

 

       另外，sp_monitor系统存储过程可以返回本例中的信息，但是返回的结果集比较多，可能需要做二次处理来获取。