SQL 效能相關

摘自 ITHOME

1.建議先使用SQL PROFILE去錄製語法，然後再丟到database engine tuning advisor
去分析，並參考SQL所建議的方式(大都是叫你建INDEX)一個資料表建議最多不要超過8個 INDEX。
2.stored procedure可以使用
3.開始收集相關的監控資訊
4.使用執行計劃去分析語法，改寫語法，避免TABLE SCAN。
5.不要對資料欄位做運算，LIKE使用方法將會造成資料表掃描而減低效能,叢集索引掃描 CLUSTERED INDEX SCAN與資料表掃描(Table Scan)幾近相同,沒有什麼效率可言
儘量避免使用<>的運算子,該運算子會造成查詢速度的下降
非必要少用NOT BETWEEN,因會該算子會成系統執行效能的下降,NOT IN也是
6.T-SQL使用SARG的寫法
7.勿在where子句對欄位使用函數，對資料欄位使用函數當然是在對資欄位做運算,所以這些都不算是SARG, 使用函數後SQL SERVER需要將資料表內所有紀錄的相關欄位輸入到函數中,若有100萬筆紀錄就需要呼叫函數100萬次,這將是效能殺手，建立完全不作事的函數，僅大 量呼叫函數，本身就耗資源
8.小心使用OR運算子，在AND運算中,只要一個子句有合適的索引就可以大幅提升查詢的速度

在使用OR布林運算子時,多個條中若有一個條件沒有合適的索引,則其他再多的欄位有索引也沒有月,只有整個資料表或是叢集索引掃描一遍,以確定全部的資料是否有符合的記錄

不要認為只要負向運算子出現在查詢中,SQL SERVER就一定無法使用索引.在WHERE條件中使用非SARG並不一定導致資料表掃描.SQL SERVER可以在某些非SARG狀況中使用索引,以及查詢中雖然包含了部份非SARG但仍可以對此查詢的SARG部份使用索引,但最好還是使用SARG
9.可以利用索引的搜尋條件
=
>
>=
<
<=
Between
Like ‘xx%’
10.無法利用索引的搜尋條件
<>
!=
!>
!<
OR
NOT IN
NOT LIKE IN
NOT EXIST
LIKE ‘%xx’
10.SELECT * 儘量少用，會造成無謂提供給應用程式多餘的資料行
因為資料表的結構改變,造成應用程式抓取資料行次序錯誤
會造成資料庫引擎要先搜尋所有的資料行,再進行作業,浪費執行時間
會造成無法使用索引進行資料查詢,降低效能
會造成文件不清楚,解讀文件時無法從中得知明確資料行名稱。
11.適當的使用子查詢雖然可以加速整個程式撰寫的速度,但是過度使用子查詢的下場,就是會拖垮整個查詢的效能，首選的方法就是使用合併聯結查詢的陳述式（INNER JOIN），聯結與子查詢可以達到相同的執行結果，但效能卻不一定相同。
12.將經常要查詢或更新的資料表，指定存放於不同磁碟組的檔案群組，將非叢集索引，指定存放於不同磁碟組的檔案群組，將常用的現有資料與歷史資料分割儲存至不同的資料表，並指定存放於不同磁碟組的檔案群組，或考慮採用分割資料表與資料壓縮
13.Tempdb之用途
暫存資料表、子查詢、HASH JOIN、ORDER BY、GROUP BY、SELECT DISTINCT、快照式交易隔離等級、線上索引維護作業…
大量使用 Tempdb 時之設定，確保 Tempdb 有足夠的資料檔大小，將 Tempdb 的資料檔指定至不同的磁碟組，如果是 SQL Server 有多個 CPU 時，建議 Tempdb 的資料檔個數與 CPU 核心數相同。
14.必要的監控資訊CPU

Process(_Total)\% Processor Time :

CPU執行非閒置執行緒的時間百分比，常用來觀察CPU使用的情形。其計算方式是:先計算在某個取樣區間，處理器執行閒置處理程序所花費的時間，減去這個時間後，再換算成百分率。

CPU

Processor\%Privileged Time: CPU執行Windows 核心指令的時間百分比，例如:處理SQL Server I/O時，當Physical Disk計數器值很高時，Privileged Time數值相對提高，可以考慮換用較有效率的磁碟子系統，提高整體的處理能力。
CPU

Processor\%User Time: 相對於處理器用於執行使用者處理序(如SQL SERVER)的時間百分比
CPU

System\Processor Queue Length 等候處理器時間的執行緒數目。當執行緒所需的處理器循環超過可用數量時，將會形成處理器瓶頸。若許多處理序都在等待處理器時間，可能需要安裝較快或額外的 處理器。假如連續監控的過程中，此數值長時間達處理器數量2倍以上，則可能面臨處理器瓶頸，例如:一台資料庫伺服器有四顆CPU，則整體值不應超過8。
Memory

Memory\Available MBytes

當下仍閒置可立即使用的實體記憶體總數
Memory

Memory\Committed Bytes 已使用的虛擬記憶體數量，其值為使用中的實體記憶體加上虛擬記憶體的總量，單位是位元組。如果數量超過系統中實體記憶體的數量，則代表需要Hard Page Fault機制運作，也就是大量實體記憶體與硬碟之間的資料切換。
Memory

Memory\Pages/sec

代表hard Page Faults的數量，每秒產生多少分頁到硬碟的虛擬記憶體。其值是Pages Input/Sec 和Pages Output/sec 二個計數器的總和。它具有指標意義，若有越多分頁發生，表示主機是處於繁忙的狀態，所以不該長時間處於很大的值，平均值建議介於0-20之間。有值不一定 代表有問題，因為WINDOWS有很多的機制在自我調節時都會做分頁切換，如系統對檔案和網路的存取。如果某個程式已經十分忙錄，但是該程式還有大量的分 頁切換，就表示問題。
PhysicalDisk

PhysicalDisk(_Total)\% Disk Time

監看磁碟處理讀取/寫入活動的時間百分比。若%DISK TIME計數器值很高超過85%，請檢視Physical DISK : Current Disk Queue Length計數器來觀察有多少系統要求(System Request)正在等候磁碟存取。
PhysicalDisk

PhysicalDisk\Avg.Disk Queue Length

等候的I/Q要求數不應持續超過實體磁碟所包含讀寫頭數的1.5~2倍。大多數的磁碟都有一個讀寫頭，而通常磁碟陣列RAID裝置則有多個讀寫頭。硬體RAID裝置在系統監控中將顯示成一台實體磁碟；而透過軟體建立的RAID裝置則顯示成多重執行個體。
SQLServer

Buffer Manager\Buffer cache hit ratio

通常SQL Server在執行時，100%直接由記憶體取得資料分頁。通常這個值超過95%代表記憶體足夠。
可在記憶體的快取找到資料分頁，而不需要讀取磁碟的百分比。輸出值應該盡可能的接近100%，但一般來說，大於90%就是可接受的範圍了；90%或低於90%代表SQL Server作業已受限於記憶體的限制了。
SQL Server Access Methods\Full Scans / sec 每次完整掃描數次數，可能是資料表或完整索引掃描。若輸出明顯高於平常所觀測的基準線。若輸出值明顯高於平常所觀測的基準線，則可能需要留意。
SQLServer Databases\Transaction/sec 每秒針對資料庫啟動的交易數。若輸出值明顯高於平常所觀測的基準線，則資料庫活動可能會有問題。
SQLServer General Statistics\User Connections 計算目前已連接SQL Server的使用者數目。若輸出的值明顯高於基準線，則使用者的活動增加，將導致SQL Server的效能惡化。
SQLServer

Databases(_Total)\Percent Log Used

使用中的記錄檔空間百分比。
SQLServer

Log Growths 資料庫之交易記錄檔的擴大總次數。
SQLServer

Databases(_Total)\Data File(s) Size (KB)

資料庫內所有的資料檔總計大小，包含任何自動的成長。
SQLServer

SQL Errors(User Errors)\*



每秒使用者產生的錯誤量，可藉此觀察資料庫的可用性。它包含引起SQL Server離線、觸發SQL SERVER 關閉連線、使用者錯誤等。
SQLServer

Locks(_Total)\Lock Waits/sec

使用者無法取得鎖定而需要等候的數目。若值明顯高於日常觀測的基準線，則可能有並行存取的問題。
SQLServer

Locks(_Total)\Number of Deadlocks/sec

每秒發生deadlock的數目，若值大於0，則使用者可能遭遇到查詢未完成，以及應用程式可能執行失敗。
SQLServer

Memory Manager\Total Server Memory (KB)

已配置給SQL SERVER的記憶體數量。當值等於這台機器的所有實記憶體的數量，可能會遭遇到資源競爭Contention的問題，因為作業系統較難配置記憶體來執行一般性作業。
SQLServer

SQL Statistics\SQL Re-Compilations/Sec 每秒SQL陳述式重新編譯的次數。若值明顯高於基準線，預存程序的撰寫方式可能不適合執行計畫的快取。
SQL SERVER User Settable\Query 最多提供10個由SP_USER_COUNTERX(其中X由1~10)系統預存程序產生的客製化計數器執行個體，這些計數器可以用來追蹤客製化的工作。

15.效能不好，處理速度慢，當然會有很多的LOCK，連線數無法快速消化相然也就變多了，PF也會跟著增加，升級硬體只能治標不能治本，改善語法才是最重要的，但須要很長的時間去做監控。
開SQL PROFILE會浪費主機約6分之1的效能。