TWed2k - 程式開發討論區 - [轉貼] SQL Server 基本概念

主題: [轉貼] SQL Server 基本概念 [打印本頁]

發表人: jocosn 時間: 2005-4-16 04:17 PM 主題: [轉貼] SQL Server 基本概念

SQL Server 連線基本概念

「除了目前最熱門的Oracle資料庫外，當今有很多大型公司甚至是辦公室都會利用微軟的SQL Server來作為資料庫的管理程式。筆者今期就和大家探討以下該資料庫伺服器在連線方面的概念。SQL Server 2000用戶端使用 API、物件程式庫和通訊協定的堆疊來進行連線。在堆疊的頂層是API或物件程式庫。應用程式是經由物件程式庫所公開的 API 功能或介面連線到MicrosoftRSQL Server。用於存取SQL Server的API範例包括ODBC和DB程式庫。用於存取SQL Server 的物件程式庫範例包括OLE DB、ADO和ADO.NET。

ADO最終還是使用OLE DB來和伺服器通訊，因此Windows應用程式常用於和SQL Server通訊的物件程式庫實際上只有兩種：OLE DB和ADO.NET。經由ADO或ADO.NET連線當然比在ODBC上更普遍(雖然SQL Server的Query Analyzer和 Enterprise Manager 仍是在ODBC上連線)。現今大多數的應用程式是使用物件程式庫連線到SQL Server，而非使用ODBC或其他類似的元件。

ADO和OLE DB

OLE DB用戶端(也稱為取用者)是經由用戶端提供者的方式與伺服器及其他後端通訊。這個提供者是一或多組COM元件，用於將應用程式的要求轉譯成網路處理序之間的通訊(IPC)要求。在SQL Server 的例子中，最常用的OLE DB提供者是SQLOLEDB，也就是微軟為SQL Server所提供的OLE DB提供者。SQLOLEDB隨附於SQL Server並安裝為Microsoft Data Access Components (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結合，以便與Managed程式碼應用程式的SQL Server進行互動。

透過利用OleDbConnection類別，ADO.NET應用程式也可以使用SQLOLEDB OLE DB提供者與SQL Server進行互動。而且它們可以經由OdbcConnection類別使用ODBC來存取 SQL Server。所以，僅單獨從Managed程式碼，大家就有三種不同的方法從應用程式來存取SQL Server。以疑難排解的觀點來看這是件好事，因為它可將大家所遭遇到有關連線的問題分離到特定資料存取層級或程式庫。

用戶端網路程式庫

在堆疊中的下一層是網路程式庫。網路程式庫在應用程式用於和SQL Server通訊的 API或物件程式庫，以及用於和網路交換資料的網路通訊協定之間提供著導管的作用。SQL Server提供網路程式庫所有主要的網路通訊協定。這些程式庫可以無障礙地處理從用戶端傳送要求到 SQL Server，以及將伺服器的回應傳回給用戶端。大家可以使用SQL Server的用戶端網路公用程式，來設定特定用戶端上提供哪些網路程式庫。支援的用戶端通訊協定包括TCP/IP、Named Pipes、NWLink、Multiprotocol (RPC)和其他。

在此特別值得一提的網路程式庫是共用記憶體網路程式庫。正如其名，這個網路程式庫使用Windows的共用記憶體功能在SQL Server用戶端和伺服器之間進行通訊。當然，這意味著用戶端和伺服器必須存放在相同的實體機器上。

因為能夠略過實體網路堆疊，共用記憶體網路程式庫比其他的網路程式庫快相當地多。同步物件會保護對共用記憶體區域的存取，所以在用戶端和伺服器之間通訊的速度主要受限於，Windows 對核心物件發出和解除信號的能力以及複製資料進出共用記憶體區域的處理能力。

在連線時，大家可以指定期間或將(local)用作為電腦名稱，以指出要使用共用記憶體網路程式庫。當連線時，大家也可以在機器\執行個體名稱加上前置詞lpc:，以指出大家要使用共用記憶體網路程式庫。大家需要明白，即使是連線到在相同機器上的SQL Server，共用記憶體網路程式庫並不必然是大家最佳的連線選項。在某些情況下，用戶端和伺服器之間的連線太過直接會限制其延展性。如同在應用程式整體架構中的其他元素，在假設某個技術解決方案比替代方法有較佳的擴充性或速度較快之前應先進行徹底的測試。

連線

當用戶端連線時，SQL Server的使用者模式排程器(UMS)元件會將之指定給一個特定的排程器。在啟動時，SQL Server 會為系統上的每個CPU建立不同的UMS排程器。當用戶端連線到伺服器時，會以最少的連線數量將之指定給排程器。一旦建好連線，用戶端就不會再變更排程器 — 它會保持在指定的排程器上直到中斷連線。對於建立多重連線到伺服器的應用程式而言，這有著重要的含意。如果應用程式設計不良或者沒有將工作平均分配在各個連線上，有可能會導致應用程式的部份連線間，在某些連線幾乎是處於閒置狀態的情況下，卻不必要地爭用CPU資源。

例如，應用程式建立四個連線到裝配有雙處理器且執行SQL Server的機器上，連線1 和3連接到處理器0上，而連線2和4連接到處理器1上。如果應用程式工作最多的部份是由連線1和3來執行的，則它們可能在CPU 1仍保持幾乎閒置的時候爭用CPU 0。在這種情況下，應用程式無能為力，只有中斷連線/重新連線並希望連線1和3能連接到不同的CPU上(在連線時無法指定CPU的傾向) 或者重新分配工作量到各個連線以使它們為平均。當然，後者遠比前者好。

連線記憶體

SQL Server為用戶端的每個連線保留三個封包的緩衝區。每一個緩衝區的大小是視 sp_configure預存程序所指定之預設網路封包的大小而定。如果預設網路封包大小小於8KB，則這些封包的記憶體是來自SQL Server的緩衝集區。如果它等於或大於 8KB，則會從SQL Server的MemToLeave區域來配置記憶體。

值得注意的是，.NET Framework Data Provider for SQL Server的預設網路封包大小是8KB，所以與Managed程式碼用戶端連線相關聯的緩衝區通常來自SQL Server的 MemToLeave區域。和傳統的ADO應用程式相對照，ADO應用程式的預設封包大小是4KB ，且緩衝區是配置於SQL Server緩衝集區。

事件

一旦建立連線，用戶端的要求通常歸為兩大類：語言事件和遠端程序呼叫。雖然確有其他的類型，但大部分SQL Server用戶端對伺服器的要求是由這兩種類型之一所構成的。語言事件是從用戶端傳送給伺服器的T-SQL批次。例如，如果大家呼叫ADO Command物件的Execute方法，該物件的CommandText屬性會設定為T-SQL查詢，且 CommandType屬性會設定為adCmdText，則會將查詢以語言事件提交給伺服器。同樣地，如果大家將CommandType設定為adCmdTable並呼叫Execute方法，ADO會產生一個內部的查詢，選取由CommandText屬性所識別之資料表中所有的欄，並以語言事件提交給伺服器。另一方面，如果大家將CommandType設定為adStoredProc，呼叫 Execute會導致ADO提交遠端程序呼叫給伺服器以執行列在CommandText屬性中的預存程序。

為什麼大家要在意是以語言事件或以RPC提交要求給伺服器呢？大家關心是因為一般而言，RPC的效能較佳，特別是使用不同的篩選數值重複呼叫相同的查詢時。雖然 SQL Server可以將純語言事件要求自動參數化，但是這樣做的能力仍然十分有限。它不會嘗試自動參數化某些類型的查詢。對於基本上相同的查詢這會導致不同的執行結果，而單純只因為不同的篩選值，而在伺服器上耗費計劃編輯的成本。通常這並不是大家所想要的 — 對於第一次執行查詢大家要編譯一個新的計劃，然後在後續發生對不同參數有重要作用的執行上重複使用該計劃。

另一方面，RPC並不依賴伺服器而是使用明確地參數化來鼓勵計劃重複使用。程序第一次執行會產生一個計劃，即使提供參數不同的值，後續的執行仍會自動重複使用它。使用RPC來呼叫預存程序相對於使用語言事件，不僅節省計劃編輯所需的執行時間和CPU資源，加上因為避免在重複的執行計劃上浪費記憶體，所以也可以較妥善地利用SQL Server的記憶體資源。

這也是當執行動態T-SQL時，EXEC()通常偏好sp_executesql的相同理由。 Sp_executesql的運作方式是使用特定的查詢來建立預存程序，然後再使用支援的參數來呼叫它。與EXEC()不同的是，sp_executesql提供機制允許大家參數化動態的 T-SQL並且鼓勵計劃重複使用。使用sp_executesql執行的動態查詢比使用EXEC()執行的動態查詢，有較大的機會可以避免不必要的編輯和資源成本。

TDS

從用戶端傳送到SQL Server的RPC、語言事件和其他類型的要求，都會格式化為SQL Server特定的資料格式，稱為「表格式資料串流(TDS)」。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。其他的則是保留用於回溯相容性。

伺服器端網路程式庫

在伺服器端，用戶端的要求最初會由SQL Server設定來接聽特定網路通訊協定的接聽程式所接收。這些接聽程式包括伺服器上的網路程式庫和提供它們和伺服器之間導管功能的伺服器端網路程式庫。大家可以使用伺服器網路公用程式來設定伺服器所要接聽的通訊協定。除了處理叢集之外，SQL Servers支援和客戶端所支援同樣範圍的網路通訊協定。對於叢集的SQL Server，只有TCP/IP和「具名管道」可用。

SQL Server在其所接聽的用戶端要求上為每個網路通訊協定設定一個執行緒，並使用 Windows的I/O完成通訊埠機制有效地等待並處理要求。當由網路上接收 TDS 封包後，網路程式庫接聽程式會將它們重新組譯成原來的用戶端要求，並傳遞到SQL Server的命令處理層，即「開放式資料服務 (ODS)」。

將結果傳回用戶端

當伺服器準備好要對特定用戶端要求傳回結果時，會使用最初收到要求時相同的網路堆疊。它會在伺服器端網路程式庫上將結果傳送到適當的網路通訊協定，接著依序在網路上以TDS格式送回給用戶端。在用戶端上，伺服器接收的TDS封包會從IPC層使用用戶端網路程式庫重新組譯，然後轉送到啟始該要求的API或物件程式庫。雖然牽涉到所有的部分，SQL Server用戶端和伺服器之間的往返可以是相當快速的 — 回應時間在秒之下是很常見的，尤其是使用共用記憶體網路程式庫時。在此有幾個資料點是當大家建立或微調自己的SQL Server用戶端應用程式時值得記住的：

如果用戶的應用程式和SQL Server是在同一部機器上執行的話，倘若大家尚未使用共用記憶體網路程式庫，請考慮使用。共用記憶體網路程式庫架構的連線通常比其他類型的連線快。不過，請記住稍早所說的：在假設某解決方案天生就又好又快之前，請徹底進行測試並與可行的替代方案進行比較。好壞一試便知。因為用戶端在第一次連線時就被指定到特定的UMS排程器直到中斷連線為止，所以確認應用程式的工作量平均分配在建立到伺服器的各個連線上是很重要的。不平均的工作量可能導致不必要的CPU爭用和不盡理想的資源使用。大家在伺服器上所設定以及用戶端所指定的預設網路封包大小，在連線時會直接影響到它們在伺服器上需要多少記憶體以及配置的集區。

一般而言，在傳送請求到伺服器時大家應該優先選擇RPC而非語言事件。在大家使用的ADO或ADO.NET物件設定適當的屬性以加速做到這一點。當執行動態T-SQL時，儘可能使用sp_executesql而非EXEC()。唯一不可能的時候是當使用EXEC()的能力來將查詢的片段串連成超過可以儲存在單一本機變數的動態查詢字串時(很罕見的狀況)。

當大家遭遇到用戶端的問題，並且懷疑是和用來連接到伺服器的物件程式庫或API有關時，其中一個方法是變更正在使用的用戶端機制以將問題隔離到特定的元件。例如大家在升級 MDAC，然後開始在SQL Server錯誤記錄檔看到編號17805的錯誤，指出接收到從用戶端ADO應用程式傳出的不正確TDS封包。如果不麻煩的話，大家可以嘗試將應用程式切換成使用ODBC的OLE DB提供者，看看問題是否和SQLOLEDB提供者有關。反之，如果ADO架構應用程式是在 ODBC上連線，大家可以切換到SQLOLEDB看是否可以解決問題或至少可以縮小範圍。

總結

順著這些相同的思考軸線，就可以理解有時候在疑難排解與連線相關的問題時，為什麼要變更所使用的網路程式庫了。如果大家使用的是 TCP/IP，或許「具名管道」值得一試。例如，大家遭遇到 DHCP 伺服器的問題而且沒有有效的 IP 位址，大家就無法使用 TCP/IP 連線到 SQL Server。經由切換到「具名管道」，大家可以快速將問題隔離到 TCP/IP 的特定部分。另一方面，如果大家切換網路程式庫而仍然有同樣的問題，大家或許可以排除是網路程式庫的問題。或許是伺服器關閉或是在大家和伺服器之間網路基本架構的某一部分運作不正常。即使什麼都沒有的話，能夠簡單的變更應用程式使用的網路程式庫而不用變更應用程式本身，就已經提供大家有助於釐清問題的工具了。對大家而言即使無法長期的使用特定的網路程式庫，暫時切換用戶端使用它也有助於縮小相關連線問題的範圍。

[jocosn 在 2005-4-19 11:12 AM 作了最後編輯]

歡迎光臨 TWed2k (http://twed2k.org/)