忘了說明：本系列文章出自《Microsoft SQL Server 2012 Performance Tuning Cookbook》，將會陸續(xù)推出譯文，但是由于工作需要，沒有按順序貼出來。

本系列文章包含三部分：

1、 ? 使用系統(tǒng)統(tǒng)計(jì)函數(shù)（ system statistical functions ）來監(jiān)控系統(tǒng)健康程度。

2、 ? 使用系統(tǒng)存儲過程來監(jiān)控 SQLServer 進(jìn)程和會話。

3、 ? 使用 DBCC SQLPERF 命令來監(jiān)控日志空間使用情況。

?

前言：

???????? SQLServer 提供了一些系統(tǒng)函數(shù)、系統(tǒng)存儲過程和 DBCC 命令來分析 SQLServer 性能相關(guān)的問題，不過絕大部分這些工具所獲得的信息都可以通過 DMVs 和 DMFs 來獲得。很多人依舊使用本文的工具的原因是因?yàn)樗麄冮L期使用這些工具來監(jiān)控 SQLServer ，已經(jīng)成為了一個習(xí)慣，所以為了向后兼容，微軟依舊保留這些工具，但是建議新入門的人盡可能從 DMO （ DMVs 和 DMFs 的統(tǒng)稱）中獲取信息。

?

????? 性能監(jiān)控有很多工具， SQL Profiler 、擴(kuò)展事件、 DMO 及本系列文章提供的工具等，來獲取信息，對于簡單的性能問題，使用某一種即可，但是對于復(fù)雜的性能問題，往往需要多個工具協(xié)同使用。

?

下面先介紹使用系統(tǒng)統(tǒng)計(jì)函數(shù)來監(jiān)控 SQLServer 的健康程度。

?

使用系統(tǒng)統(tǒng)計(jì)函數(shù)來監(jiān)控 SQLServer

?

??????? SQLServer 提供了一些列非常有用的系統(tǒng)統(tǒng)計(jì)函數(shù)來監(jiān)控當(dāng)前 SQLServer 的狀態(tài)。這些函數(shù)用于檢查和監(jiān)控服務(wù)器的健康狀態(tài)非常有效。

?

?????? 現(xiàn)在假設(shè)一個情況，在你的數(shù)據(jù)庫環(huán)境中，一個 web 應(yīng)用程序?qū)?shù)據(jù)集的操作是一行一行的。為了讀取每一行，應(yīng)用程序會在數(shù)據(jù)庫中往返訪問，導(dǎo)致經(jīng)常需要開啟新的連接。為了處理這個問題，需要經(jīng)常監(jiān)控 SQLServer 的連接數(shù)，下面將演示如何操作。

?

準(zhǔn)備工作：

?

SQLServer 提供了下面這些有用的系統(tǒng)函數(shù)：

?

    @@CONNECTIONS



@@TIMETICKS



@@CPU_BUSY



@@IDLE



@@IO_BUSY



@@PACK_RECEIVED



@@PACK_SENT



@@PACKET_ERRORS



@@TOTAL_READ



@@TOTAL_WRITE



@@TOTAL_ERRORS




  

?

本例子中將使用這些函數(shù)，并創(chuàng)建腳本來獲取信息。

?

環(huán)境準(zhǔn)備：

?

使用 SQLServer2008 企業(yè)版（本機(jī)只有企業(yè)版）和示例數(shù)據(jù)庫 AdventureWorks 。

?

步驟：

?

1、 ? 打開 SQLServer （這里使用 SQLServer Management Studio 后面簡稱 SSMS ），然后新開一個查詢窗口（ ctrl+m ）。

?

2、 ? 在窗口上輸入一下腳本：

?

    --創(chuàng)建一個表來存儲統(tǒng)計(jì)信息

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、運(yùn)行下面腳本，顯示收集的服務(wù)器信息：

?

    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


  

?

分析：

??????? 上面例子中，先創(chuàng)建一個表 [dbo] . [tbl_ServerHealthStatistics] ，在創(chuàng)建之前，使用 OBJECT_ID() 函數(shù)來檢查是否存在該表，如果存在則不創(chuàng)建，這是一個良好的編程習(xí)慣，建議在創(chuàng)建表（無論是實(shí)體表還是臨時表）時使用。可以確保腳本可重復(fù)執(zhí)行。

?

??????? 步驟 2 的腳本中，通過 INSERT..SELECT 語句來收集數(shù)據(jù)并插入到表中。

?

??????? 步驟 3 中，由于需要對比兩行之間的數(shù)據(jù)，所以使用 CTE （ 2005 之前可以使用臨時表）來暫時存放數(shù)據(jù)然后與目前數(shù)據(jù)做對比。

?

?

擴(kuò)展信息：

?

下面是這些系統(tǒng)統(tǒng)計(jì)函數(shù)的簡介，這些函數(shù)均返回從 SQLServer 啟動以來的匯總值。

?

??????? @@Connections：這個函數(shù)返回SQLServer自啟動以來，嘗試連接到SQLServer的連接數(shù)，是一個數(shù)值型結(jié)果。不管這些連接是否成功，均會記錄在里面。

?

????@@MAX_CONNECTIONS：返回允許同時連接的最大連接數(shù)，這個數(shù)與使用sp_configure 來配置的Max Connections值相同。也和SQLServer的版本和應(yīng)用程序、硬件的限制有關(guān)。

?

????@@TIMETICKS：返回一個微妙級別的計(jì)數(shù)點(diǎn)。這個值依賴于操作系統(tǒng)的時間系統(tǒng)。通常為31.50毫秒。

?

?????? @@CPU_BUSY ：返回自SQLServer服務(wù)啟動以來的工作時間，結(jié)果為所有CPU事件的累計(jì)，所以可能會超出實(shí)際時間，乘以@@TIMETICKS即可換成為妙。注意：如果@@CPU_BUSY 或 @@IO_BUSY 中返回的時間超過累積的 CPU 時間約 49 天，則您將收到算術(shù)溢出警告。在這種情況下，@@CPU_BUSY、@@IO_BUSY 和 @@IDLE 變量值并不精確。

?

??? @@IDLE：表示SQLServer空閑時的CPU時間。在多處理器情況下，返回值為所有CPU的匯總。

?

??? @@IO_BUSY：返回SQLServer自啟動以來執(zhí)行輸入輸出操作的CPU總數(shù)。

?

??? @@PACK_RECEIVED：返回SQLServer接收到的網(wǎng)絡(luò)包總數(shù)。

?

??? @@PACK_SENT：返回SQLServer發(fā)送的網(wǎng)絡(luò)報總數(shù)。

?

??? @@PACKET_ERRORS：返回SQLServer所遇到過的網(wǎng)絡(luò)包錯誤的總數(shù)。

?

??? @@TOTAL_READ：返回SQLServer所執(zhí)行過的所有物理讀操作總數(shù)。

?

??? @@TOTAL_WRITE：返回所有物理寫操作的總數(shù)。

?

??? @@TOTAL_ERRORS：返回SQLServer遇到過的所有讀寫操作的錯誤總數(shù)。

?

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

?

??????? 本例中的腳本收集某個時間點(diǎn)的 SQLServer 信息，可以借助 SQLServer Agent ，來定期、自動收集，以便用于后續(xù)分析之用。

?

?????? 另外，sp_monitor系統(tǒng)存儲過程可以返回本例中的信息，但是返回的結(jié)果集比較多，可能需要做二次處理來獲取。

?

第三章——使用系統(tǒng)函數(shù)、存儲過程和DBCC SQLPERF命令來監(jiān)控SQLServer（1）