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資料檔案和日志檔案的磁盤，盡量不要使用預設的磁盤配置設定單元
防毒軟體
防毒軟體會對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