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