SQL Server 連線基礎知識(轉)

SQL Server 連線基礎知識(轉)[@more@]引言
　　該堆疊的頂部是 API 或物件庫層。應用程式透過物件庫公開的 API 函式或介面連線到 Microsoft® SQL Server。用於訪問 SQL Server 的 API 示例包括 ODBC 和 DB-Library。用於訪問 SQL Server 的物件庫示例包括 OLE DB、ADO 和 ADO.NET。由於 ADO 最終使用 OLE DB 與伺服器通訊，因此 Windows 應用程式在與 SQL Server 通訊時實際上只使用兩個常用的物件庫，即 OLE DB 和 ADO.NET。由於透過 ADO 或 ADO.NET 進行連線通常比透過 ODBC 進行連線更普遍（但 SQL Server 的查詢分析器和企業管理器仍透過 ODBC 進行連線），因此本文將從 ADO/OLE DB 和 ADO.NET 的角度介紹 SQL Server 連線體系結構的客戶端。如今，大多數應用程式均透過物件庫（而非 ODBC 或類似 API）連線到 SQL Server。

ADO 和 OLE DB
　　OLE DB 客戶端（也稱作使用者）透過客戶端提供程式與伺服器以及其他後端程式進行通訊。此提供程式是一組 COM 元件（一個或多個），用於將應用程式請求轉換為網路程式間通訊 (IPC) 請求。在使用 SQL Server 的情況下，最常用的 OLE DB 提供程式是 SQLOLEDB，它是 Microsoft 為 SQL Server 提供的 OLE DB 提供程式。SQLOLEDB 隨附於 SQL Server 中，並作為 Microsoft 資料訪問元件 (MDAC) 庫的一部分安裝。

　　為了使用 ADO 與 SQL Server 進行通訊，應用程式首先使用 Connection 物件建立與伺服器的連線。ADO 的 Connection 物件接受一個連線字串，該字串指定要使用的 OLE DB 提供程式以及傳遞給它的引數。如果應用程式使用 SQLOLEDB 提供程式連線到 SQL Server，則該字串中將顯示“SQLOLEDB”。

　　ADO 應用程式還可以透過 ODBC 連線到 SQL Server。為此，應用程式將使用適用於 ODBC 的 OLE DB 提供程式，並指定在其連線字串中引用目標 SQL Server 的 ODBC 資料來源。這種情況下，應用程式與 OLE DB 進行通訊，同時 ODBC 的 OLE DB 提供程式呼叫相應的 ODBC API，以便與 SQL Server 進行會話。

ADO.NET
　　ADO.NET 應用程式通常使用 .NET Framework Data Provider for SQL Server 連線到 SQL Server。該本機提供程式使 ADO.NET 物件能夠與 SQL Server 直接進行通訊。通常，應用程式使用 SqlConnection 物件建立連線，然後使用 SqlCommand 物件向伺服器傳送命令，並接收伺服器返回的結果。SqlDataAdapter 和 SqlDataReader 類通常與 SqlCommand 一起使用，以便透過託管的程式碼應用程式與 SQL Server 進行互動。

　　透過 OleDbConnection 類，ADO.NET 應用程式還可以使用 SQLOLEDB OLE DB 提供程式與 SQL Server 進行互動。此外，它們可以透過 OdbcConnection 類使用 ODBC 訪問 SQL Server。因此，僅透過託管程式碼，您就有三種不同的方法從應用程式訪問 SQL Server。從故障排除的角度而言，瞭解這些方法是非常有用的，因為它可以幫助您將遇到的與連線相關的問題歸結到特定的資料訪問層或庫。

客戶端 Net-Library
　　該堆疊中的下一層是 Net-Library。Net-Library 在 API 或物件庫（應用程式使用它與 SQL Server 進行通訊）與網路協議（用於與網路交換資料）之間提供了一個通道。SQL Server 為所有主要的網路協議提供了 Net-Library。這些庫以透明方式將客戶端發出的請求傳送到 SQL Server，並將伺服器發出的響應返回給客戶端。可以使用 SQL Server 的客戶端網路實用程式配置適用於特定客戶端的 Net-Library。支援的客戶端協議包括 TCP/IP、命名管道、NWLink、多協議 (RPC) 和其他一些協議。

　　尤其值得一提的 Net-Library 是共享記憶體 Net-Library。顧名思義，該 Net-Library 使用 Windows 的共享記憶體功能在 SQL Server 客戶端與伺服器之間進行通訊。顯然，這意味著客戶端與伺服器必須位於同一臺物理計算機上。

　　由於它能夠繞過物理網路堆疊，因此共享記憶體 Net-Library 要比其他 Net-Library 快得多。對共享記憶體區域的訪問受到同步物件的保護，因此客戶端與伺服器之間的通訊速度主要受限於 Windows 對核心物件進行排程的能力，以及程式與共享記憶體區域之間進行資料複製的能力。

可以在連線時將某個時間段或（本地）指定為您的計算機名，來指示使用共享記憶體 Net-Library。也可以在連線時為計算機例項名加上字首 lpc:，來指示要使用共享記憶體 Net-Library。

　　注意，即使連線到同一臺計算機上的 SQL Server，共享記憶體 Net-Library 也未必就是最佳的連線選項。在某些情況下，客戶端與伺服器之間的直接連線可能限制它的擴充套件性。與應用程式整體體系結構中的其他元素一樣，應始終對給定技術解決方案進行全面的測試，然後才能判斷它是否有良好的擴充套件性以及是否比其他方法更快。

連線
　　客戶端進行連線時，SQL Server 的使用者模式計劃程式 (UMS) 元件將它指定給特定的計劃程式。啟動時，SQL Server 為系統上的每個 CPU 建立一個單獨的 UMS 計劃程式。當客戶端連線到伺服器時，這些客戶端將指定給具有最少連線數的計劃程式。連線後，客戶端將不會更換計劃程式 - 它將始終受到指定計劃程式的控制，直到連線斷開。

　　這對與伺服器建立多個連線的應用程式很重要。如果應用程式效能較差，或無法在它的多個連線上平均分配工作，則在該應用程式的某些連線之間可能造成不必要的 CPU 資源爭用，而其他連線實際上卻處於空閒狀態。

　　例如，應用程式與雙處理器計算機上執行的 SQL Server 建立了四個連線，連線 1 和 3 隸屬於處理器 0，連線 2 和 4 隸屬於處理器 1。如果應用程式的大部分工作透過連線 1 和 3 執行，則這兩個連線將爭用 CPU 0，而 CPU 1 實際上可能仍處於空閒狀態。這種情況下，應用程式只能斷開某些連線或重新連線某些連線，並希望連線 1 和 3 隸屬於不同的 CPU （連線時無法指定處理器隸屬關係），或在它的連線上重新分配工作負荷，以便每個連線的工作負荷更加均衡。當然，後一種情況要遠好於前一種情況。

　　連線記憶體

　　SQL Server 為客戶端請求的每個連線保留三個資料包緩衝區。每個緩衝區的大小取決於 sp_configure 儲存過程指定的預設網路資料包大小。如果預設網路資料包大小小於 8 KB，則這些資料包的記憶體將由 SQL Server 的緩衝池提供。否則，該記憶體將由 SQL Server 的 MemToLeave 區域分配。 　　

　　值得一提的是，.NET Framework Data Provider for SQL Server 的預設網路資料包大小為 8KB，因此，與託管程式碼客戶端連線關聯的緩衝區通常由 SQL Server 的 MemToLeave 區域提供。而典型的 ADO 應用程式卻不同，它們的預設資料包大小為 4 KB，因此緩衝區將由 SQL Server 緩衝池分配。

事件

　　連線後的客戶端請求通常分為兩種廣泛類別：語言事件和遠端過程呼叫。儘管還存在其他類別，但大多數由 SQL Server 客戶端傳送到伺服器的請求由以下兩種型別之一構成：語言事件是從客戶端傳送到伺服器的 一組 T-SQL。例如，如果呼叫 ADO Command 物件（其 CommandText 屬性設定為 T-SQL 查詢，CommandType 屬性設定為 adCmdText）的 Execute 方法，則查詢將作為語言事件提交給伺服器。同樣，如果將 CommandType 設定為 adCmdTable 並呼叫 Execute 方法，則 ADO 將生成一個內部查詢（它將選擇 CommandText 屬性標識的表中的所有列），並將它作為語言事件提交給伺服器。另一方面，如果將 CommandType 設定為 adStoredProc，則呼叫 Execute 將使 ADO 向伺服器提交一個遠端過程呼叫請求，以執行 CommandText 屬性中列出的儲存過程。　　

　　為何要關心將請求作為語言事件還是作為 RPC 提交給伺服器呢？通常，這是因為 RPC 的功能更為出色，特別是在重複呼叫具有不同篩選值的同一查詢時。儘管 SQL Server 可以自動將普通的語言事件請求引數化，但這種能力非常有限。它從不嘗試自動將某些型別的查詢引數化。這可能會導致基本相同的查詢產生不同的執行，從而只因為這些不同的執行提供不同的值，而導致在伺服器上白白浪費計劃編譯的成本。這通常不是您所希望的結果 - 您希望針對查詢的第一次執行編譯一個新的計劃，然後將該計劃重複用於具有不同引數的執行。　

而 RPC 則透過顯式引數化查詢（而不是依賴伺服器引數化查詢）來支援計劃重複使用。為過程的第一次執行生成一個計劃後，隨後的執行將自動重複使用該計劃，即使它們提供的引數值不同。與透過語言事件呼叫儲存過程相比，使用 RPC 呼叫儲存過程不僅節省了計劃編譯所需的執行時間和 CPU 資源，還增強了 SQL Server 記憶體資源的利用率，因為它避免了冗餘執行計劃所浪費的記憶體。　　

　　在執行動態 T-SQL 時，通常首選 sp_executesql 而不是 EXEC() 也出於同樣的原因。Sp_executesql 的工作方式是：使用指定的查詢建立一個儲存過程，然後使用提供的引數呼叫它。與 EXEC() 不同，sp_executesql 提供了一個允許您引數化動態 T-SQL 並支援計劃重複使用的機制。使用 sp_executesql 執行的動態查詢比使用 EXEC() 的查詢能夠在更大程度上避免不必要的編譯和資源消耗。 　

　　TDS

　　從客戶端傳送到 SQL Server 的 RPC、語言事件和其他型別的請求被格式化為稱作表格資料流 (TDS) 的 SQL Server 特定資料格式。TDS 是 SQL Server 客戶端和伺服器之間使用的“語言”。對於它的確切格式將不作介紹，但是，如果客戶端要與 SQL Server 進行通訊，就必須使用 TDS。　　

　　目前，SQL Server 支援三種版本的 TDS：TDS 8.0（適用於 SQL 2000 客戶端）、TDS 7.0（適用於 SQL Server 7.0 客戶端）和 TDS 4.2（適用於 SQL Server 4.2、6.0 和 6.5 客戶端）。完全支援所有 SQL Server 2000 功能的版本只有 TDS 8.0。其他版本保持向後相容。　　

　　伺服器端 Net-Library

　　在伺服器端，客戶端請求最初由 SQL Server 為偵聽特定網路協議而建立的偵聽器接收。這些偵聽器由伺服器上的網路庫以及伺服器端的 Net-Library（在它們與伺服器之間提供管道）構成。您可以使用 SQL Server 網路實用程式配置伺服器偵聽的協議。SQL Server 與客戶端支援同樣範圍的網路協議（處理群集的情況除外）。對於群集化的 SQL Server，只有 TCP/IP 和命名管道可用。　　

　　SQL Server 為偵聽客戶端請求所使用的每個網路協議設定一個執行緒，並使用 Windows 的 I/O 完成埠機制等待和有效處理請求。從網路接收到 TDS 資料包時，Net-Library 偵聽器將其重新彙編為它們的原始客戶端請求，並將這些請求傳遞到 SQL Server 的命令處理層，即開放式資料服務 (ODS)。

將結果返回到客戶端

　　伺服器在準備將特定客戶端請求的結果返回時，將使用最初接收請求時所用的網路堆疊。它透過伺服器端 Net-Library 將結果傳送到相應的網路協議，隨後這些結果將透過網路以 TDS 格式返回到客戶端。 　　

　　在客戶端上，客戶端 Net-Library 將從伺服器接收的 TDS 資料包從 IPC 層重新彙編，並將其繼續轉發到初始化該請求的 API 或物件庫。　　

　　小結

　　儘管涉及了所有元件，但 SQL Server 客戶端與伺服器之間的往返過程卻相當快 - 特別是在使用記憶體 Net-Library 時，亞秒響應時間非常普遍。構建和調整您自己的 SQL Server 客戶端應用程式時，以下幾個與資料相關的問題值得注意： 　　

　　• 如果應用程式與 SQL Server 執行在同一臺計算機上，則建議您使用共享記憶體 Net-Library（如果尚未使用它）。基於共享記憶體 Net-Library 的連線通常比其他型別的連線快很多。在注意上述內容的同時，還應：始終全面測試解決方案並將它與其他可行方案進行對比，這樣才能判斷它是否確實更好或更快。事實勝於雄辯。 　　

　　• 由於客戶端在第一次連線時將指定給特定的 UMS 計劃程式，並只有在斷開連線後，才會擺脫該計劃程式的控制，因此確保在應用程式與伺服器建立的連線上均衡分配工作負荷非常重要。工作負荷不均衡可導致不必要的 CPU 爭用並降低資源使用率。 　　

　　• 在伺服器上配置的預設網路資料包大小以及客戶端在連線時指定的網路資料包大小將直接影響它們在伺服器上所需的記憶體量和分配記憶體的池。對伺服器進行擴充套件性和速度配置時，應記住這一點。還應記住，預設情況下，ADO.NET 應用程式的網路資料包大小比 ADO 應用程式的更大。
• 通常，在向伺服器傳送請求時，應首選 RPC 而非語言事件。為此，應在使用的 ADO 或 ADO.NET 物件中設定相應的屬性。 　　

　　• 執行動態 T-SQL 時，應在可能的情況下使用 sp_executesql 代替 EXEC()。唯一例外的情況是，當使用 EXEC() 的功能將查詢片斷連線而成的動態查詢字串的大小超過單個本地變數的儲存大小時（這種情況非常少見）。 　　

　　• 當遇到客戶端問題，並且懷疑它可能和連線伺服器時所用的物件庫或 API 有關時，可以使用的一個故障排除技巧就是更改所用的客戶端機制，這樣可以將問題歸結為特定的元件。例如，假設您升級 MDAC 並開始在 SQL Server 錯誤日誌中看到 17805 錯誤，這表明客戶端 ADO 應用程式傳送的 TDS 資料包的格式不正確。您可能嘗試讓應用程式轉為使用 ODBC 的 OLE DB 提供程式，如果您可以較為容易地做到這一點，應看看該問題是否與 SQLOLEDB 提供程式有一定的關係。相反，如果基於 ADO 的應用程式一直透過 ODBC 進行連線，則可以切換到 SQLOLEDB，看看這是否能解決問題，或至少幫助您縮小問題的範圍。 　　

　　• 同樣，在對連線問題進行故障排除時，更改正在使用的 Net-Library 有時會有所幫助。如果使用 TCP/IP，命名管道也許值得一試。例如，如果 DHCP 伺服器出現問題，並且沒有有效的 IP 地址，則您將無法使用 TCP/IP 連線到 SQL Server。透過切換到命名管道，可以快速地將問題歸結為 TCP/IP 特定的因素上。另一方面，如果在切換 Net Library 後仍存在同樣的問題，則可以排除 Net-Library 方面的問題。問題的原因可能是伺服器已關閉，或在您與伺服器之間的某處網路基礎設施無法正常工作。最後，還可以容易地更改應用程式使用的 Net-Library，而不必更改應用程式本身，這樣就為您提供一個幫助縮小問題範圍的工具。儘管從長遠角度而言，使用某一特定 Net-Library 並不可行，但讓客戶端臨時使用它可以幫助您縮小連線相關問題的範圍。