如何從性能的角度更好地使用 CLR 函式（在每個數據庫中重複或具有通用功能）？
我問了一個關於
XML使用XSD schemainside驗證的問題SQL Server 2012（見連結）。我了解（正如我所懷疑的那樣）我需要使用CLR Function. 該函式將獲取XSD schema text並且XML text將進行驗證。我將有 1 個配置數據庫和許多安裝數據庫。從這個角度來看，我想知道在哪裡創建該功能 - 在配置數據庫中還是在每個安裝數據庫中？
從支持的角度來看，最好只有一個 CLR 函式。
這似乎是這個問題的重複：
為其他數據庫中的內部儲存過程設置一個中央 CLR 儲存過程/函式儲存庫以供使用？
但是，我認為這兩個答案中的任何一個都不夠充分，因為它們沒有提到這個問題的一些更重要的方面。
通常，對於 SQLCLR 對象來說，哪個位置更好，這裡沒有明顯的選擇，因為使用 SQLCLR 程式碼所做的事情可能會受到限制。有一些用途需要將程序集放在每個單獨的數據庫中，還有一種用途需要將程序集放在集中式數據庫中。這完全取決於程式碼在做什麼的幾個不同方面。因此，我們需要看看這些方面是什麼，以確定是否有選擇開始，如果有，利弊是什麼。
SQLCLR 特定的功能方面
使用者定義類型（UDTs）： UDTs不能跨數據庫引用；它們不能用 3 部分名稱（即 DatabaseName.SchemaName.UserDefinedTypeName）聲明。如果正在使用任何 UDT，則需要將程序集添加到將使用 UDT 的每個數據庫中。但是，如果正在使用其他 SQLCLR 對象，那麼假設可以選擇將這些對象放置在集中式數據庫或每個客戶/應用程序數據庫中，那麼您始終可以將 UDT 放置在一個組件中，該組件被放置在每個客戶中/application DB 和另一個包含函式/儲存過程/使用者定義的聚合/觸發器的程序集。
安全：
- 有多少個數據庫受到影響： CLR 程式碼是否在執行任何需要將程序集標記
PERMISSION_SET為EXTERNAL_ACCESSor 或的操作UNSAFE？如果是這樣，您是否導入了任何在您控制之外簽名且無法退出的 DLL？通常，這些是不受支持的 .NET Framework 庫或第 3 方 DLL。當您無法控制需要標記為EXTERNAL_ACCESS或的程序集的簽名時UNSAFE，您可能會被迫將包含程序集的數據庫設置為TRUSTWORTHY ON。由於將數據庫設置為TRUSTWORTHY ON是一種安全風險，最好盡量減少需要執行此操作的數據庫數量，在這種情況下，將程式碼放入集中式數據庫似乎是一種更好的方法。如果您已經有一個用於其他程式碼的中央數據庫，並且希望真正將這種類型的安全風險降到最低，那麼您可以為該程式碼創建第二個中央數據庫。如果您確實可以控制 DLL 的簽名，那麼您絕對應該
master基於 DLL 在數據庫中創建證書或非對稱密鑰，然後基於該證書或非對稱密鑰創建登錄，然後分配或對該登錄名的EXTERNAL ACCESS ASSEMBLY權限UNSAFE ASSEMBLY。那裡的那幾個步驟（唯一要創建的是證書或密鑰和登錄名）將允許使用相同私鑰簽名的任何程序集設置為EXTERNAL_ACCESS或者UNSAFE（取決於授予登錄名的權限），否不管它被載入到什麼數據庫中。如果您能夠做到這一點，那麼您可以將程序集設置為EXTERNAL_ACCESS或者UNSAFE在所有客戶/應用程序數據庫中，沒有比將相同程式碼放入集中式數據庫中更多的安全風險**。
- **不同客戶端/應用程序需要不同的權限：**如果出於任何原因，某些客戶端/應用程序可能需要與
PERMISSION_SET其他客戶端/應用程序不同，則需要將程序集載入到每個客戶端/應用程序數據庫中。這將允許您使用一些數據庫，SAFE而其他人正在使用EXTERNAL_ACCESS. 這超出了對象級權限所能做的事情。通過設置具有執行文件系統功能SAFE的程式碼的程序集，您可以保證程式碼無法工作，即使有人確實找到了繞過您的正常安全性的方法並且仍然可以EXECUTE使用 SQLCLR 儲存過程。**AppDomains：**這方面涉及記憶體/資源利用和分離。就考慮因素而言，這可能是影響最大的領域，但也可能是最不了解的領域。因此，讓我們首先看看 T-SQL 對像如何處理相同的中央數據庫與每個客戶端/應用程序數據庫問題。
T-SQL 函式和儲存過程在執行時，將它們的執行計劃儲存在記憶體中的計劃記憶體中（嗯，不是內聯 TVF）。僅從記憶體使用率的角度考慮，使用集中式數據庫具有儲存單個計劃而不是每個客戶端/應用程序數據庫一個計劃的優勢，尤其是在有 100 個或更多數據庫的情況下。但是，擁有一個記憶體計劃會引發一個問題，即它是否是後續執行的最佳計劃。有可能，由於在如此多的客戶端/應用程序數據庫中執行它的方式可能存在廣泛的變化，一個單一的計劃對某些人來說是很好的，但對其他人來說卻是相當可怕的。如果您不希望指定的性能受到影響
WITH RECOMPILE，然後將其部署到每個客戶端/應用程序數據庫將允許更個性化的優化。簡而言之：中央數據庫用於計劃記憶體的記憶體較少，但性能可能更差；單獨的數據庫為計劃記憶體提供更多記憶體，但潛在的性能問題更少。對於 SQLCLR 對象，每種方法都存在相同的計劃記憶體優缺點。但是現在我們正在處理應用程序域，還有其他需要考慮的後果。應用程序域是 .NET 用於在其中執行程式碼的記憶體空間/沙箱。每個應用程序域都是它自己獨立的沙箱。在 SQL Server 中，每個數據庫和程序集所有者組合都會創建應用程序域。因此，同一使用者擁有的同一數據庫中的多個程序集將共享一個應用程序域，但另一個使用者擁有的同一數據庫中的程序集將具有不同的應用程序域，而其他數據庫中的程序集將位於它們自己的應用程序域中。考慮到這一點：
- 部署到單個客戶端/應用程序數據庫時，記憶體消耗會以更快的速度增加，因為正在使用的程序集被載入到應用程序域中（但它們在第一次使用之前不會載入）。應用程序域還包含所有變數、資源句柄等（直到這些東西被標記為垃圾收集和GC 認為月亮和星星完美對齊，並將其作為執行的標誌）。因此，一個數據庫中的 2 MB 程序集使用一個 AppDomain 為變數等保留了一定數量的記憶體，這與將同一個程序集載入到現在為 200 MB 的 100 個數據庫中（技術上是 DLL 的某些部分）完全不同它在多個實例的記憶體中共享，但我不確定如何衡量）加上為變數等保留的空間量的 100 倍。
一個相關的問題是，如果您使用正則表達式並使用 RegEx 選項
Compiled將表達式編譯為中間語言 (MSIL)。這確實加快了重複使用的表達式的速度，但是一旦表達式被編譯，它就不能被垃圾收集，並且會留在 AppDomain 中直到它重新啟動。如果有一個常用的 RegEx 函式正在使用該Compiled選項，則如果將程序集載入到每個 DB 中，則用於儲存它的記憶體將在每個 DB 中重複。在這種情況下，將此程式碼放在集中式數據庫中可能是有意義的。
使用集中式數據庫時，資源限制可能是一個問題。根據您使用的類，您可能會在不知不覺中造成資源瓶頸。例如：
- 當使用靜態 RegEx 方法而不是實例方法時，您使用的正則表達式會被記憶體。但預設記憶體大小只有 15 個表達式。如果從大量客戶端或應用程序中發送各種各樣的表達式，則表達式不會在記憶體中停留很長時間。因此，如果這是考慮將程序集載入到每個數據庫中的唯一原因，那麼您可以增加記憶體大小。有關詳細資訊，請參閱RegEx.CacheSize的 MSDN 頁面。
- 同樣，如果進行
WebRequests，則可以對特定 URI 進行預設的最大活動連接數。而該預設值僅為 2。如果您向同一個 URI 發出更多請求（如果它是靜態位置並且您為此程式碼使用集中式數據庫，則非常容易做到），那麼任何超過該最大值的請求都將簡單地排隊等待要關閉的目前連接（即阻塞）。因此，您必須將程序集載入到每個客戶端/應用程序數據庫中，或者增加每個 URI 的連接數限制。您可以通過設置ServicePointManager.DefaultConnectionLimit為目前應用程序域中的所有URI設置預設最大值（這可以在每次啟動應用程序域時設置一次，例如在靜態類建構子中），或者可以通過創建 HttpWebRequest 然後設置其.ServicePoint.ConnectionLimit屬性基於每個 URI 進行設置（這需要每次實例化 WebRequest 時都會執行此操作，因為對象具有最大生存時間，並且一旦垃圾收集，ConnectionLimit 將恢復為該ServicePointManager.DefaultConnectionLimit值，如上所述，當創建新實例時）。如果您使用靜態變數來記憶體某些值（共享記憶體 - 很少見但仍有可能），那麼您需要決定共享這些值的範圍應該是什麼。如果您希望共享包含在每個客戶端/應用程序數據庫中，則將程序集載入到每個客戶端/應用程序數據庫中。但是，如果您想在所有數據庫中共享這些值，則將程序集放入一個共享的集中式數據庫中。
一般功能方面
- **數據庫訪問：**程式碼是否引用了任何特定於數據庫的對象？請記住，使用程序內/
Context Connection = true;連接執行 SQL 最初將在“目前”數據庫設置為對象存在的數據庫時執行，而不一定是從其中呼叫對象的位置。因此，在客戶/應用程序數據庫中執行並呼叫集中式數據庫中的對象的程式碼將無法僅使用兩部分名稱來引用對象。但是，您仍然可以為此類程式碼使用集中式數據庫，只要您有@DatabaseName(use:[SqlFacet(MaxSize = 128)] SqlString DatabaseName) 的輸入參數，然後將其傳遞DB_NAME()給它。然後您可以DatabaseName.Value在 SQLCLR 程式碼中使用USE語句或連接到動態 SQL 以創建適當的完全限定的對象名稱（即 3 部分名稱）。如果您只引用基於系統的對象（即
sys.databases），這可能不是決定因素，無論您在哪個數據庫中都返回相同的行。如果您正在建立外部連接，這也不應該是一個問題，因為您已經是傳遞連接字元串的數據庫名稱，或者您將登錄到預設數據庫以進行連接登錄。
- **排序規則差異：**如果集中式數據庫和客戶端/應用程序數據庫之間的排序規則相同，那麼這不是決定這兩種模型的決定因素。但是，如果您的系統要支持不同的排序規則，那麼您需要了解您的程式碼在做什麼，因為它可能會受到排序規則的影響. 如果您發送的字元串將與其他字元串進行比較，那麼即使沒有產生錯誤，行為也可能不是預期的。用於比較局部變數和字元串文字的排序規則將是對象（即儲存過程或函式）所在的預設排序規則。如果此排序規則與呼叫該對象時的“目前”數據庫的排序規則不同（如果傳入文字或變數），或者與傳入的欄位不同，那麼該比較的方式可能會存在多種差異已經完成了。因此，如果支持各種排序規則，那麼當程式碼部署到每個客戶端/應用程序數據庫時，基於字元串的操作可能會更加穩定/一致。
分心
以下是此問題以及此答案頂部連結的重複問題中給出的原因，用於選擇一種方法或另一種方法，但我認為在決定哪種方法更適合時並不真正相關：
- **集中式數據庫更容易支持：**怎麼會？程式碼在原始碼管理中應該只存在一次。並且假設載入到每個客戶端/應用程序數據庫中的程式碼是相同的，那麼在任何一種情況下，故障排除都應該大致相同。如果有的話，將 SQLCLR 程式碼放在一個公共數據庫中，與客戶端/應用程序數據庫分開會增加跨數據庫呼叫的複雜性，這在技術上可以被視為不集中此（或其他）程式碼的原因。
- **集中式數據庫在部署時更容易：**如果是這種情況，那麼我會說需要修復他們的部署過程。如果您正在使用複制客戶端/應用程序數據庫以具有相同架構和不同數據的模型，那麼在部署架構更改時您已經面臨這個問題。部署 SQLCLR 也只是 DDL 語句。如果嘗試從 DLL 載入程序集，部署 SQLCLR 程式碼可能會更加困難，但沒有理由這樣做。只需確保有一個獨立的 SQL 腳本，它執行 a
CREATE ASSEMBLY或ALTER ASSEMBLY使用十六進製字節（即FROM 0x4D5F000002C...）。- **單個客戶端/應用程序數據庫更適合備份/恢復：**這裡的論點是，如果出現問題並且您需要恢復，那麼恢復客戶端/應用程序數據庫中包含的所有內容是最容易的。我想說，如果您需要進行恢復，那麼恢復 2 個數據庫（客戶端/應用程序數據庫和通用數據庫）幾乎是相同的工作，恢復 101 個數據庫（100 個客戶端/應用程序數據庫和 1 個通用數據庫）仍然很漂亮幾乎相同。從可靠性的角度來看，您的備份過程是可靠的並且可以信任以正確備份客戶端/應用程序數據庫和公共數據庫，或者您需要修復您的備份過程;-)。
- **單個客戶端/應用程序數據庫更容易測試變體：**在某種程度上，如果您需要在不更改所有引用的情況下測試一個版本的程式碼，這是正確的。雖然通常這應該通過在完全不同的環境中進行測試來完成。但是，例如，如果有一個客戶要對某些程式碼更改進行 beta 測試，那麼可以通過使用@AaronBertrand 在他對這個問題的回答中建議的模型很容易地緩解這種情況，這個問題非常相似（它主要關注T-SQL 對象，而不是專門的 SQLCLR 程式碼）：在中央數據庫中創建函式還是在每個數據庫中重複？. 該答案中討論的模型是在大多數/所有情況下使用集中式數據庫，並且在每個客戶端/應用程序數據庫中創建同義詞，以便本地程式碼可以引用本地名稱。然後，如果需要變體，可以將程式碼放置在特定的客戶端/應用程序數據庫中，然後刪除同義詞。在這種情況下，本地 T-SQL 程式碼仍將引用相同的本地名稱並且不知道差異。就個人而言，我真的很喜歡這種方法，除了需要創建/刪除同義詞之外，我想不出它有任何特別的缺點，這似乎並不是為獲得的靈活性付出高昂的代價。
**爾格：**對於這個問題中描述的特殊情況（即標量函式，沒有外部資源，沒有數據庫對象訪問，沒有資源限制），看起來使用你的單一配置數據庫就可以了。
**如果您發現自己在想“但是設置為 EXTERNAL_ACCESS 或 UNSAFE的程序集是安全風險，因為它們允許您執行此操作”：我並不是說如果您使用設置為 EXTERNAL_ACCESS 或 UNSAFE 的程序集根本沒有風險基於證書/非對稱密鑰的方法。我要說的是，在這種配置中，無論存在什麼風險，將程序集放置在集中式數據庫中與每個客戶端/應用程序數據庫中都沒有什麼不同。這是因為設置為 EXTERNAL_ACCESS 或 UNSAFE 的程序集可能導致的任何潛在安全問題都未本地化到這些程序集所在的數據庫（與設置
TRUSTWORTHY為不同ON）。任何安全問題都是系統範圍的。但是，當將數據庫設置為TRUSTWORTHY ON，那麼您有額外的每個數據庫的安全問題。