SQLSERVER 數據庫性能的基本

很久沒有寫文章了，在系統正式上線之前，DBA一般都要測試一下服務器的性能

比如你有很多的服務器，有些做web服務器，有些做緩存服務器，有些做文件服務器，有些做數據庫服務器

做數據庫服務器的那臺服務器性能要相對較好，磁盤，內存，CPU等等，

那么在選用其中某一臺服務器作為數據庫服務器之前需要測試每一臺服務器的性能

并且需要設置一些硬件的參數，例如設置磁盤控制器的參數，參考文章：Writeback和Writethrough區別

那么具體怎麼測試呢？怎麼得出測試指標呢？

大家可以參考這篇文章：SQL Server Database Engine Performance Tuning Basics

正文

隨著市場份額的SQL Server的發展隨著時間的推移,有越來越多的對SQL服務器性能調優的需求。

有不同的團隊和個人采用各種各樣的方法提高SQLSERVER服務器的性能,

而且我認為這些記錄SQLSERVER troubleshooting 的基本步驟和提高各種程序性能的文檔對SQLSERVER社區是有意義的

磁盤

為了SQLSERVER能有效運行，監控和優化SQLSERVER的磁盤子系統是一個重要的方面

我們需要非常明確磁盤的性能需求

Avg. Disk Sec/Read 這個計數器是指每秒從磁盤讀取數據的平均值

下面的列表顯示這個計數器值的范圍，并指出這個計數器所處范圍的意思

少于 10 ms - 非常好
在 10 - 20 ms 之間- 還可以
在 20 - 50 ms 之間- 慢，需要關注
大于 50 ms –嚴重的 I/O 瓶頸

磁盤性能測試工具

（1）CrystalDiskMark

（2）HDTUNE 硬盤檢測修復工具 

（3）ATTO Disk Benchmark 

辨別I/O瓶頸

PhysicalDisk Object：Avg. Disk Queue：所選物理磁盤在取樣期間被排隊的磁盤讀寫請求平均值

如果你的磁盤隊列長度經常超出SQLSERVER磁盤使用峰值的2倍，那意味著可能有I/O瓶頸了

Avg. Disk Sec/Read：每秒從磁盤讀取數據的平均值 

Avg. Disk Sec/Write：寫入數據到磁盤的平均時間，Avg. Disk Sec/Read參考指標

Physical Disk:%Disk Time磁盤時間是所選磁盤驅動器繁忙處理讀寫請求時所花時間的百分比，一個指標就是如果這個值大于50%，那么就存在I/O瓶頸

Avg. Disk Reads/Sec：在磁盤上的讀操作的比率。確保這個數字小于磁盤吞吐量的85%。當這個值超過85%磁盤訪問時間會以指數式增長

Avg. Disk Writes/Sec c：在磁盤上的寫操作的比率。確保這個數字小于磁盤吞吐量的85%。當這個值超過85%磁盤訪問時間會以指數式增長

對于更多的信息，可以參考“如何創建性能計數器集”：http://technet.microsoft.com/en-us/library/cc722148.aspx

磁盤驅動器的位置：

為了不同的目的，你需要使用不同的驅動器來存放下面的東西
獨立的磁盤延時需求：
數據庫大于15ms

事務日志大于2ms

Tempdb數據庫大于2ms

磁盤速度的優先級

意思是說，Tempdb放在單獨的物理磁盤，事務日志文件放在單獨的物理磁盤，數據文件放在單獨的物理磁盤，操作系統放在單獨的物理磁盤，

數據庫備份文件放在單獨的物理磁盤

一般我們的做法：不可能有那么多單獨的物理磁盤，一般就是做了磁盤陣列的存儲

C盤放操作系統文件

D盤放數據文件和事務日志文件 和Tempdb數據文件和Tempdb日志文件

E盤放數據庫備份文件

當格式化磁盤的時候，對于要存放SQLSERVER數據文件和日志文件的磁盤，盡量不要使用默認的磁盤分配單元

使用64k 簇大小 Allocation Unit 來格式化磁盤，至于為什麼大家可以看一下這篇文章：如何用Procmon.exe來監視SQLSERVER的logwrite大小

殺毒軟件

殺毒軟件會對SQLSERVER的一些功能產生問題，使用殺毒軟件的排除功能將數據庫的文件排除在掃描的范圍外是很重要的（放入殺軟的掃描例外里）

下面的文件類型是需要排除在外的

*.mdf, *.ndf, *.ldf, *.bak

相關文章：殺毒軟件導致YourSQLDba備份失敗

文章中說到因為殺毒軟件掃描備份文件夾并鎖住了備份文件夾，導致SQLSERVER備份數據庫失敗

內存

總是給分配最大的內存給SQLSERVER實例在服務器屬性那里設置

注意:最大內存設置只對SQLSERVER的buffer cache部分有效，不包括SQLSERVER的一些需要內存的功能，例如復制

（SQLSERVER2012的最大內存設置已經可以限制buffer cache部分和非buffer cache部分的內存）

為了指明Non-Buffer Pool 的內存占用，使用下面的說明

SQL Server’s buffer pool外的內存需求（這個需求不是說你設置了SQLSERVER最大內存之后，所剩下的內存的需求，不管你有沒有設置SQLSERVER的最大內存

下面幾項都是服務器固定需要消耗的內存，而無論你的服務器內存是4G,8G還是16G，下面幾項都會固定占用服務器的內存）

（1）操作系統需要占用2GB內存，如果是64位操作系統，操作系統占用內存不大于3GB

（2）SQLSERVER工作線程的倍數，你可以在SQLSERVER服務器屬性里設置最大工作線程，

每個線程會使用0.5MB內存（X86服務器）

每個線程會使用2MB內存（X64服務器）

每個線程會使用4MB內存（Itanium服務器）

注意：0.5MB內存存放的是線程自身的數據結構和相關信息，不包括數據

為什么各種服務器所分配的線程內存不一樣，這個是操作系統分配的，SQLSERVER并沒有做特別的設置！

如果你設置最大的工作線程數為10個，服務器是X86，剛好服務器用盡了10個線程，那么占用的內存是10*0.5MB=5MB內存

（3）1GB的 multi-page 內存占用，鏈接服務器和其他SQLSERVER外圍的程序占用

（4）運行在服務器上的程序可能占用1~3GB內存，例如備份程序

例子

 例如，一個8核服務器，16GB內存，運行著SQLSERVER2012 X64，上面運行著第三方的備份程序，你可以參照下面的清單

 （1）3GB 給 Windows (2GB for 32 Bit Windows)

 （2）1GB 給 SQLSERVER 工作線程 (576 × 2MB 大概)

各種CPU和SQLSERVER版本組合自動配置的最大工作線程數
CPU數       32位計算機      64位計算機
<=4             256               512
8                 288               576
16               352                704
32               480                960

（3）1GB for MPAs, etc. （multi-page apply）

（4）1~2 GB 給 備份程序.

您能夠找到更多信息關于“最大工作線程選項”http://technet.microsoft.com/en-us/library/ms187024(v=sql.105).aspx

(For SQL Server 2008).

開啟Lock Pages in Memory 選項

Windows組策略決定哪個Windows賬戶能使進程將他的數據逗留在物理內存里，防止操作系統把程序數據從物理內存換頁換出磁盤上的虛擬內存

這能夠給您帶來性能上的提升，特別遇到內存壓力的時候

TempDB 數據庫的優化

默認，Tempdb數據庫只有一個數據文件和事務日志文件。然而，為了性能的優化，跟著下面給出的建議最佳實踐

TempDB數據庫的存儲計劃

（1）設置Tempdb數據庫的恢復模式為簡單（默認就是簡單的），簡單模式能夠自動回收日志空間使日志空間的需求保持最小

（2）不要讓Tempdb的數據文件自動增長，這可以減少管理動態文件增長的CPU開銷

 對于Tempdb數據庫，可以分開多個數據文件（總的Tempdb數據庫數據文件的數量=CPU邏輯處理器的數量，比如8核服務器可以分8個數據文件）

每個數據文件的大小要一樣

（3）嘗試將這些數據文件存放在不同的磁盤驅動器上以利用并行I/O

（4）TempDB 數據文件和 日志文件應該存放在較快速度的磁盤上（如果可能推薦放在做了RAID 1的磁盤上）

（5）使用RAID-10 或者 SSD 磁盤

（6）預先定義好Tempdb數據庫的文件大小

（7）設置Tempdb總的大小為當前數據庫實例中最大的那個數據庫的25% 

（8）設置Tempdb數據文件自動增長的固定大小小于200MB

（9）你應該設置Tempdb數據庫的數據文件數量跟邏輯CPU的數量一致，最多不超過8個數據文件

CPU的優化

設置最大并行度（Max Degree of Parallelism）

定義多少個邏輯CPU能并行執行查詢

很多微軟的產品，例如SharePoint 和 Dynamics CRM都把這個設置設置為1，這個是推薦的設置

對于 SharePoint  的LOB 應用程序，當你看到有很多CXPACKETS 的等待類型在你的SQLSERVER服務器里，

你應該考慮一下將這個設置（Max Degree of Parallelism）設置為1

索引填充因子

如果你的SQLSERVER服務器有非常高的事務量TPS （transaction per second）

你的索引有比較高碎片級別，考慮一下將填充因子設置為“80%”

并且使用下面的SQL語句檢測一下索引碎片

SELECT  DB_NAME(ps.database_id) AS 'Database Name' ,
        OBJECT_NAME(ps.OBJECT_ID) AS 'Database Object' ,
        ps.index_id ,
        b.name ,
        ps.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
        INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
                                       AND ps.index_id = b.index_id
WHERE   ps.database_id = DB_ID('ReportServerTempDB')
ORDER BY ps.avg_fragmentation_in_percent DESC
GO
 

使用Performance Monitor (Perfmon.exe)來監控系統性能

為了捕獲SQLSERVER特定的性能指標，你需要使用下面的計數器

Processor: % Processor Time  ：平均應該低于75% （最好低于50%）   

System: Processor Queue Length：平均每個邏輯CPU應該低于2，例如在一個2邏輯CPU的機器上，他應該保持在4

Memory—Pages/sec：平均應該低于20（最好低于15%）

Memory—Available Bytes ：可用內存應該保持在50MB以上

Physical Disk—% Disk Time：
Physical Disk—Avg. Disk Queue Length ：每個磁盤平均應該低于2，例如：一個RAID5磁盤，這個指標應該平均低于10

Physical Disk—Avg. Disk Reads/sec ：取決于CPU和磁盤的大小，應該低于相對應磁盤的吞吐量的85%

Network Interface—Bytes Total/sec ：用于統計網絡帶寬方

SQL Server: Buffer Manager—Page Life Expectancy：用于統計內存，應該保持在300秒
SQL Server: 一般統計用戶的連接數 來估計大概使用的內存
SQL Server: Databases— Transactions/sec ：每秒的事務數
SQL Server: Databases—Data File(s) Size KB：用于統計數據庫數據文件的大小，衡量磁盤子系統的性能
SQL Server: Databases—Percent Log ：衡量磁盤子系統的性能

如有不對的地方，歡迎大家拍磚o(∩_∩)o