小说网,君子以泽,好看的小说完本推荐

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷(xiāo)解決方案

運(yùn)用SQLSERVER2005CLR解決XMLShowplan實(shí)例

大家都知道SQL SERVER 2005 CLR（公共語(yǔ)言運(yùn)行庫(kù)），而在編程中SQL Server 開(kāi)發(fā)人員和數(shù)據(jù)庫(kù)管理員 (DBA) 有時(shí)會(huì)遇到這種情況，即用戶(hù)在工作高峰期向服務(wù)器提交長(zhǎng)時(shí)間運(yùn)行的查詢(xún)，因而降低了服務(wù)器的響應(yīng)速度。在這里給大家將介紹兩種方法可以防止這一情況的發(fā)生：
1.DBA 可使用 sp_configure 將 query governor cost limit 選項(xiàng)設(shè)置為特定閾值。（這是一個(gè)高級(jí)選項(xiàng)。）該閾值在整個(gè)服務(wù)器內(nèi)是有效的。
2.要影響連接的閾值，DBA 可以使用 SET QUERY_GOVERNOR_COST_LIMIT 語(yǔ)句。

在太平等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶(hù)提供網(wǎng)站設(shè)計(jì)制作、網(wǎng)站設(shè)計(jì) 網(wǎng)站設(shè)計(jì)制作定制網(wǎng)站開(kāi)發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),高端網(wǎng)站設(shè)計(jì),成都全網(wǎng)營(yíng)銷(xiāo),外貿(mào)網(wǎng)站建設(shè),太平網(wǎng)站建設(shè)費(fèi)用合理。

可以想像一下需要更細(xì)粒度控制的情況。例如，用戶(hù)可能有三個(gè)等效但語(yǔ)法結(jié)構(gòu)不同的查詢(xún)，并希望以執(zhí)行速度盡可能最快的形式自動(dòng)提交該查詢(xún)。此外，用戶(hù)還可能希望不執(zhí)行任何估計(jì)執(zhí)行成本超過(guò)特定閾值的查詢(xún)。以編程方式訪(fǎng)問(wèn)查詢(xún)成本，將允許用戶(hù)通過(guò)控制基于估計(jì)執(zhí)行成本的查詢(xún)提交過(guò)程來(lái)構(gòu)建服務(wù)器友好的應(yīng)用程序。本文中描述的技術(shù)允許使用 SQLCLR 用戶(hù)定義的過(guò)程、XPath、XQuery 以及 Visual C# 技術(shù)，以編程方式訪(fǎng)問(wèn)查詢(xún)的估計(jì)執(zhí)行成本。如本文所述，通過(guò)用戶(hù)定義的過(guò)程使用 SQLCLR 來(lái)訪(fǎng)問(wèn) SQL Server 2005 的基本技術(shù)也可用于其他應(yīng)用程序。

在 SQL Server 2005 中，可以使用 .NET Framework 中可用的任何編程語(yǔ)言（例如 Microsoft Visual Basic .NET 或 Visual C#）來(lái)定義用戶(hù)定義的類(lèi)型、函數(shù)、過(guò)程以及聚合。從概念上講，在定義了用戶(hù)定義的實(shí)體后，就可以在 SQL Server 中使用該實(shí)體，就像是由 SQL Server 本身提供的實(shí)體一樣。例如，定義用戶(hù)定義的 T 類(lèi)型之后，還可以定義帶有一列 T 類(lèi)型的關(guān)系型表。定義了用戶(hù)定義的 P 過(guò)程后，就可以使用 EXEC P 調(diào)用該過(guò)程，就像 Transact-SQL 過(guò)程一樣。

使用 SQL SERVER 2005 CLR 存儲(chǔ)過(guò)程和進(jìn)程內(nèi)數(shù)據(jù)訪(fǎng)問(wèn)提取查詢(xún)成本

實(shí)現(xiàn)該解決方案
1. .NET Framework 語(yǔ)言（本文中使用 Visual C#）定義存儲(chǔ)過(guò)程，該過(guò)程將從給定查詢(xún)的 XML Showplan 中獲得查詢(xún)成本。
2.正在運(yùn)行 SQL Server 的服務(wù)器注冊(cè)此過(guò)程。此操作需要兩個(gè)子步驟：
1） SQL Server 中注冊(cè)該程序集。
2）建一個(gè)引用外部 CLR 方法的存儲(chǔ)過(guò)程。

顯示用于創(chuàng)建用戶(hù)定義的 CLR 存儲(chǔ)過(guò)程的示意圖。

以下步驟循序漸進(jìn)地介紹該解決方案的過(guò)程。

1. A 包含一個(gè) Visual C# 程序 (ShowplanXPath.cs)，該程序從運(yùn)行 SQL Server 的服務(wù)器中提取 XML 格式的 Showplan，然后在獲得的 Showplan 上執(zhí)行 XPath 表達(dá)式，以提取估計(jì)查詢(xún)執(zhí)行成本。***步包括，使用 Visual C# 編譯器編譯該程序并生成一個(gè) DLL (ShowplanXPath.dll)?？墒褂靡韵旅钚衼?lái)進(jìn)行編譯。該命令生成一個(gè)名為 ShowplanXPath.dll 的 DLL：

 
 
 
 
  
  
  
  \csc.exe   
  
  
  
  /out:ShowplanXPath.dll  
  
  
  
  /target:library   
  
  
  
  /reference:\System.dll  
  
  
  
  /reference:\System.Data.dll  
  
  
  
  /reference:\sqlaccess.dll  
  
  
  
  ShowplanXPath.cs

其中，應(yīng)該將替換為指向 Microsoft .NET Framework 位置的正確路徑，例如

 
 
 
 
  
  
  
  C:\WINNT\Microsoft.NET\Framework\v2.0.40607

或?qū)⑵涮砑拥较到y(tǒng)環(huán)境變量 PATH 中。請(qǐng)注意，您需要根據(jù)計(jì)算機(jī)上安裝的 .NET Framework 的版本來(lái)修改“v2.0.40607”。將替換為指向 SQL Server 2005 安裝的二進(jìn)制文件的正確路徑，例如

 
 
 
 
  
  
  
  "C:\Program Files\MicrosoftSQL Server\MSSQL.1\MSSQL\Binn\"

如果該路徑包含空格，那么就像本示例那樣將該路徑用引號(hào)括起來(lái)。

2.下一步，使用客戶(hù)端（例如 SQL Server 2005 Management Studio）發(fā)布的以下 Transact-SQL 命令來(lái)讓 SQL Server 2005 知道該程序集 (ShowplanXPath.dll)：

 
 
 
 
  
  
  
  use AdventureWorks   
  
  
  
  go   
  
  
  
  CREATE ASSEMBLY ShowplanXPath   
  
  
  
  FROM '\ShowplanXPath.dll'   
  
  
  
  go

將替換為指向***步所編譯 DLL 的位置的路徑。

3.注冊(cè)的程序集 (ShowplanXPath.dll) 中創(chuàng)建引用外部 CLR 方法的用戶(hù)定義的存儲(chǔ)過(guò)程。

 
 
 
 
  
  
  
  CREATE PROCEDURE dbo.GetXMLShowplanCost   
  
  
  
  (   
  
  
  
  @tsqlStmt   NVARCHAR(MAX),   
  
  
  
  @queryCost   NVARCHAR(MAX) OUT   
  
  
  
  )   
  
  
  
  AS EXTERNAL NAME ShowplanXPath.xmlshowplanaccess.GetXMLShowplan   
  
  
  
  go

請(qǐng)注意，此外部名稱(chēng)的邏輯格式為：assembly_name.class_name.method_name。@tsqlStmt 參數(shù)將包含一個(gè)查詢(xún)，而且將使用 OUT 參數(shù) @queryCost 返回查詢(xún)成本。

4.端使用以下代碼調(diào)用 CLR 用戶(hù)定義的存儲(chǔ)過(guò)程：

 
 
 
 
  
  
  
  DECLARE @query nvarchar(max)-- the query   
  
  
  
  DECLARE @cost nvarchar(max)-- its estimated execution cost   
  
  
  
  -- set this to your query   
  
  
  
  set @query = N'select * from person.address'   
  
  
  
  -- execute the procedure   
  
  
  
  EXECdbo.GetXMLShowplanCost @query, @cost OUTPUT   
  
  
  
  select @cost-- print the cost   
  
  
  
  -- note that @cost is nvarchar, we use explicit comparison in case of an error    
  
  
  
  -- and implicit conversion for actual cost   
  
  
  
  if (@cost != '-1') and (@cost <= 0.5)-- if query is cheap to execute,   
  
  
  
  EXEC(@query)-- execute it; else don't execute   
  
  
  
  -- replace 0.5 with your own threshold   
  
  
  
  go

請(qǐng)注意，可以通過(guò) @query 變量提交一組查詢(xún)（一個(gè)批處理），然后返回該批處理的總成本。如果查詢(xún)或批處理中有錯(cuò)誤，則返回“-1”作為其成本?？梢孕薷母戒?A 中的異常處理代碼，以便在出現(xiàn)錯(cuò)誤時(shí)能更好地滿(mǎn)足您的需要。

5.輸出參數(shù) @cost 將該查詢(xún)的估計(jì)執(zhí)行成本返回到客戶(hù)端。如步驟 4 中的代碼示例所示。

6.端可根據(jù) @cost 的值，選擇是否將該查詢(xún)提交到 SQL Server 來(lái)加以執(zhí)行，如步驟 4 中的代碼所示。

顯示執(zhí)行存儲(chǔ)過(guò)程的主要步驟

1.一旦調(diào)用該過(guò)程，它就會(huì)接收到一個(gè)成本有待估計(jì)的查詢(xún)。
2.CLR 存儲(chǔ)過(guò)程將 SHOWPLAN_XML 模式設(shè)置為 ON。不執(zhí)行提交到該連接的任何語(yǔ)句；然而，將為這些語(yǔ)句生成 showplan。將該查詢(xún)本身發(fā)送到 SQL Server。
3.該服務(wù)器以 XML 格式逐段返回此 showplan，然后 Visual C# 程序?qū)⑦@些片段整理在一起。
4.該過(guò)程將 SHOWPLAN_XML 模式設(shè)置為 OFF。
5. CLR 存儲(chǔ)過(guò)程準(zhǔn)備并以 XML 格式在 showplan 上執(zhí)行一個(gè) XPath 表達(dá)式，以提取查詢(xún)成本。該批處理中每條語(yǔ)句中的每個(gè)查詢(xún)計(jì)劃的成本均被提取并總計(jì)。
6.估計(jì)查詢(xún)執(zhí)行成本返回到調(diào)用程序中。如果 SQL 代碼中出現(xiàn)錯(cuò)誤，則返回“-1”作為成本。

注 DLL 與 SQL Server 之間的通信稱(chēng)為進(jìn)程內(nèi)數(shù)據(jù)訪(fǎng)問(wèn)，這是因?yàn)橐褜⒃?DLL 鏈接到 SQL Server 進(jìn)程。由于已將 DLL 動(dòng)態(tài)鏈接到該 SQL Server 進(jìn)程，因此交換數(shù)據(jù)并不跨越 SQL Server 進(jìn)程邊界。當(dāng)執(zhí)行進(jìn)程內(nèi)數(shù)據(jù)訪(fǎng)問(wèn)時(shí)，只能將 XPath 查詢(xún)發(fā)送到 SQL Server；XQuery 查詢(xún)不能使用進(jìn)程內(nèi)數(shù)據(jù)訪(fǎng)問(wèn)。使用就是 SQL SERVER 2005 CLR 存儲(chǔ)過(guò)程和進(jìn)程內(nèi)數(shù)據(jù)訪(fǎng)問(wèn)提取查詢(xún)成本的一個(gè)案例。

當(dāng)前題目：運(yùn)用SQLSERVER2005CLR解決XMLShowplan實(shí)例
URL網(wǎng)址：http://fisionsoft.com.cn/article/dpoiogd.html

新聞中心

其他資訊