使用Temp Table並不能改Connection數過多的問題, 因為Client端要取用Temp Table, 還是要建立Connection.

使用Stored Procedure或許是解決Client/Server架構效能問題的方法之一, 但不是絕對的.

因為, 基本上, 我們對DB Performance Tuning, 主要是針對:

1. Index建立
2. SQL Script Tuning/Optimization
3. I/O流量減少

對常取用的Table做Index, 把SQL Script做點Optimization的處理, 以及對I/O流量的控管或加大流量, 這裏的I/O指網路頻寬及Disk Channel.

和I/O有關的, 還有儘量'精確的'擷取足夠的資料量即可, 我常見VB6或Delphi的程式設計師用

<pre class="c" name="code">SELECT * FROM TABLE WHERE 1=1

的指令取出資料, 一個Loop跑完全部資料, 在Loop中再做處理...其實, 這樣的效能實在很爛, 正確做法是

<pre class="c" name="code">SELECT F1, F2, F3 FROM TABLE WHERE F1 BETWEEN 'A' AND 'C'

就是要在WHERE中把要處理的範圍直接限定出來. 如果F1欄位有做Index就更理想了.

還有就是Table的設計, 資料錄的總長度最好在一個Block內, 2048 Bytes長度內比較好.

為何沒提Lock, 尤其是Dead Lock, 因為SQL Script Optimization的議題就巳含在內了.

要減少Connection, 根本要領在程式設計時要按照...連線, 儘快把要做的事做完, 取消連線....的順序寫.

我常見程式設計師寫一段程式, 連取消連線這個指令都不寫, 就End了, 這是錯誤的, 因為Connection會一直存在, 只是靜止而已, 直到SQL Server自己發現靜止很久了, 自動清除.

因此, 建議版大改程式時, 先把資料庫結構, 尤其是Index的規劃先安排好, 再看SQL的相關程式指令, 是否都有遵照只查詢需要的資料這個原則設計.

如果, Coding的習慣良好, 其實Connection一點都不會是問題.

使用 Stored Procedure 取代前端應用程式直接存取資料表
Stored Procedure 它是事先編譯過，所以效能會比較好，同時也可以節省前端程式陳述式傳遞之頻寬耗損。
並可以參閱一下微軟官方網站資料http://www.microsoft.com/taiwan/technet/columns/profwin/strengthsql.mspx

針對 (2) (尤其是 (c)) 我會嘗試使用 snapshot isolation.

Query 在那邊組成對於效能有時有不可預期的影響, 在程序端組成可以使用程序端的 cache (如 nhibernet linkq/ms 或是 coherence/oralce). 這對於重賦性的 data + connection 過高有明顯的助益. 從 VB6 換到 c# 也不失為一種改善效能的方法.

DB 先天上效能並不是主要賣點, 對於一個已經"合理"效能最佳化的 query 重寫, 可能省下機器的錢還比不上所花的時間.

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的效能。

如果你們VB6的程式是採用3-Tier架構,要解決連結數過多的狀況,可以在IIS上設定連線共用。
參考資料:自己

看你用VB6 就知道貴公司是很有歷史的公司,主機應是用了5年以上的主機了,如是建議換主機比較好

資料庫效能不好, 除了各參數資源調校(CPU,MEMORY,DISK,NETWORK)外, 最主要的還是SQL調校, 要試問這SQL查詢或更新幾筆資料, 花費多少時間合不合理? 接下來是要做到精準下SQL, 所謂精準SQL是搜尋或異動最少data block, 就可完成這SQL, 達到最省io, memory, cpu的狀態, 當你練就到看到SQL心中有執行計劃, 你就會下精準SQL....

最快方式...是找個顧問幫你們公司調SQL, 你們dba從旁學習他調校工作的方法及模型...花點錢來省時間...不是你專長的事, 請公司花錢省時間而你就借事練識...對公司和你是雙鸁....

網址是我的SQL調校成果: