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

本系列文章包含三部分：

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

2、 ? 使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)監(jiān)控 SQLServer 進(jìn)程和會(huì)話。

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

?

前言：

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

?

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

?

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

?

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

?

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

?

?????? 現(xiàn)在假設(shè)一個(gè)情況，在你的數(shù)據(jù)庫(kù)環(huán)境中，一個(gè) web 應(yīng)用程序?qū)?shù)據(jù)集的操作是一行一行的。為了讀取每一行，應(yīng)用程序會(huì)在數(shù)據(jù)庫(kù)中往返訪問(wèn)，導(dǎo)致經(jīng)常需要開(kāi)啟新的連接。為了處理這個(gè)問(wèn)題，需要經(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)建腳本來(lái)獲取信息。

?

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

?

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

?

步驟：

?

1、 ? 打開(kāi) SQLServer （這里使用 SQLServer Management Studio 后面簡(jiǎn)稱 SSMS ），然后新開(kāi)一個(gè)查詢窗口（ ctrl+m ）。

?

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

?

    --創(chuàng)建一個(gè)表來(lái)存儲(chǔ)統(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)建一個(gè)表 [dbo] . [tbl_ServerHealthStatistics] ，在創(chuàng)建之前，使用 OBJECT_ID() 函數(shù)來(lái)檢查是否存在該表，如果存在則不創(chuàng)建，這是一個(gè)良好的編程習(xí)慣，建議在創(chuàng)建表（無(wú)論是實(shí)體表還是臨時(shí)表）時(shí)使用?？梢源_保腳本可重復(fù)執(zhí)行。

?

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

?

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

?

?

擴(kuò)展信息：

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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

?

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