SQLSERVER數(shù)據(jù)庫性能的的基本

SQLSERVER 數(shù)據(jù)庫性能的基本 很久沒有寫文章了，在系統(tǒng)正式上線之前，DBA一般都要測試一下服務器的性能 比如你有很多的服務器，有些做web服務器，有些做緩存服務器，有些做文件服務器，有些做數(shù)據(jù)庫服務器 做數(shù)據(jù)庫服務器的那臺服務器性能要相對較好，磁盤，

SQLSERVER 數(shù)據(jù)庫性能的基本

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

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

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

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

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

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

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

正文

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

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

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

磁盤

為了SQLSERVER能有效運行，監(jiān)控和優(yōu)化SQLSERVER的磁盤子系統(tǒng)是一個重要的方面

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

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

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

少于 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：每秒從磁盤讀取數(shù)據(jù)的平均值

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

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

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

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

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

磁盤驅動器的位置：

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

事務日志大于2ms

Tempdb數(shù)據(jù)庫大于2ms

磁盤速度的優(yōu)先級

意思是說，Tempdb放在單獨的物理磁盤，事務日志文件放在單獨的物理磁盤，數(shù)據(jù)文件放在單獨的物理磁盤，操作系統(tǒng)放在單獨的物理磁盤，

數(shù)據(jù)庫備份文件放在單獨的物理磁盤

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

C盤放操作系統(tǒng)文件

D盤放數(shù)據(jù)文件和事務日志文件 和Tempdb數(shù)據(jù)文件和Tempdb日志文件

E盤放數(shù)據(jù)庫備份文件

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

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

殺毒軟件

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

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

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

殺毒軟件導致YourSQLDba備份失敗

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

內存

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

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

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

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

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

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

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

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

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

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

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

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

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

如果你設置最大的工作線程數(shù)為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版本組合自動配置的最大工作線程數(shù)
CPU數(shù) 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賬戶能使進程將他的數(shù)據(jù)逗留在物理內存里，防止操作系統(tǒng)把程序數(shù)據(jù)從物理內存換頁換出磁盤上的虛擬內存

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

TempDB 數(shù)據(jù)庫的優(yōu)化

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

TempDB數(shù)據(jù)庫的存儲計劃

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

（2）不要讓Tempdb的數(shù)據(jù)文件自動增長，這可以減少管理動態(tài)文件增長的CPU開銷

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

每個數(shù)據(jù)文件的大小要一樣

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

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

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

（6）預先定義好Tempdb數(shù)據(jù)庫的文件大小

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

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

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

CPU的優(yōu)化

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

定義多少個邏輯CPU能并行執(zhí)行查詢

很多微軟的產品，例如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)來監(jiān)控系統(tǒng)性能

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

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 ：用于統(tǒng)計網絡帶寬方

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

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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com