承接第一篇，本文深入 CLR 整合後的系統目錄物件查詢、權限模型、原始碼與版本控制策略、效能監控計數器與 DMV 查詢、偵錯與疑難排解指南，協助 DBA 全面掌控 CLR 物件生命週期。
SQL Server 2005 引入新目錄檢視追蹤組件 (Assembly) 及相關 CLR 物件：
-- 組件基本資訊：名稱、ID、權限集、可見性
SELECT * FROM sys.assemblies;
-- 組件檔案：每個組件包含的檔案 (含原始碼)
SELECT * FROM sys.assembly_files;
-- 組件模組：SQL 物件 ID 對應組件方法
SELECT * FROM sys.assembly_modules;
-- 組件參考：組件間相依關係
SELECT * FROM sys.assembly_references;
-- 模組組件使用：SQL 物件 ID 與組件 ID 連結 (部分重複 assembly_modules)
SELECT * FROM sys.module_assembly_usages;sys.assemblies.is_visible 可隱藏組件 (如 XML Serializer 產生的輔助組件)。assembly_modules 中 assembly_method 為 NULL 表示使用者定義彙總 (UDA)。
EXEC sys.sp_assemblies_rowset N'<AssemblyName>';
EXEC sys.sp_assembly_dependencies_rowset <AssemblyID>;| 物件類型 | 關鍵查詢條件 |
|---|---|
| CLR 預存程序 | sp.type_desc = 'CLR_STORED_PROCEDURE' |
| CLR 觸發器 | tr.type_desc = 'CLR_TRIGGER' |
| CLR 純量函數 | so.type_desc = 'CLR_SCALAR_FUNCTION' |
| CLR 資料表值函數 | so.type_desc = 'CLR_TABLE_VALUED_FUNCTION' |
| CLR 使用者定義彙總 | so.type_desc = 'AGGREGATE_FUNCTION' |
| CLR 使用者定義型別 (UDT) | 查詢 sys.types join sys.type_assembly_usages |
完整查詢範本請參考原文，核心皆為：sys.objects → module_assembly_usages → sys.assemblies 三表聯結。
| 權限集 | 允許操作 | 風險與建議 |
|---|---|---|
| SAFE (預設) | 純運算、資料存取、邏輯處理，同 T-SQL 權限。受信任呼叫者模型。 | 最安全，優先使用。無法存取外部資源 (檔案、網路、註冊表)。 |
| EXTERNAL_ACCESS | SAFE 所有權限 + 檔案/網路 I/O、Web Service 呼叫。以 SQL Server 服務帳號身分執行。 | 需嚴格審核。確認呼叫者權限、輸入驗證。建議僅受信任組件使用。 |
| UNSAFE | EXTERNAL_ACCESS 所有權限 + Unmanaged 程式碼呼叫、執行緒控制、記憶體直接操作、COM Interop。 | 極高風險。可能導致 SQL Server 程序崩潰、記憶體洩漏、安全性破洞。嚴格限制使用，生產環境原則上禁止。 |
建立組件時指定：CREATE ASSEMBLY ... WITH PERMISSION_SET = SAFE | EXTERNAL_ACCESS | UNSAFE。
CREATE ASSEMBLY 僅載入二進位 DLL，原始碼不進資料庫。DBA 需建立流程確保原始碼與部署版本一致。CREATE/ALTER ASSEMBLY，並將原始碼、PDB、建置日誌存入受控儲存庫ALTER ASSEMBLY LeastSquaresSqlFunction
ADD FILE FROM 'D:\Production\Source\LeastSquares\LS1.cs';ALTER ASSEMBLY 支援單一陳述式加入多檔案。| 計數器物件 | 關鍵指標 | 異常判讀 |
|---|---|---|
| .NET CLR Exceptions | Exceptions/sec | 驟增預示應用程式錯誤，需檢視錯誤日誌 |
| .NET CLR Loading | AppDomains、Loaded Assemblies | 過大表示 Procedure Cache 壓力 |
| .NET CLR Memory | Gen 0/1/2 Heap Size、% Time in GC | 記憶體異常增長需重構程式碼 |
| .NET Data Provider for SqlServer | 連接數、連接池狀態 | 資料庫層級活動監控 |
-- 已載入組件 (各資料庫執行)
SELECT sa.name, ad.appdomain_name, clr.load_time
FROM sys.dm_clr_loaded_assemblies clr
JOIN sys.assemblies sa ON clr.assembly_id = sa.assembly_id
JOIN sys.dm_clr_appdomains ad ON clr.appdomain_address = ad.appdomain_address;
-- CLR 記憶體使用
SELECT mo.type, SUM(mo.pages_allocated_count * mo.page_size_in_bytes/1024) AS Current_KB,
SUM(mo.max_pages_allocated_count * mo.page_size_in_bytes/1024) AS Max_KB
FROM sys.dm_os_memory_objects mo
WHERE mo.type LIKE '%clr%'
GROUP BY mo.type ORDER BY mo.type;
-- CLR 等待統計
SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%clr%';
-- 正在執行 CLR 程式碼的請求
SELECT session_id, request_id, start_time, status, command, database_id,
wait_type, wait_time, cpu_time, total_elapsed_time, executing_managed_code
FROM sys.dm_exec_requests WHERE executing_managed_code = 1;
-- CLR 耗時最久的查詢
SELECT (SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text, qs.*
FROM sys.dm_exec_query_stats qs
WHERE qs.total_clr_time > 0
ORDER BY qs.total_clr_time DESC;
-- CLR 效能計數器值
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters WHERE counter_name LIKE '%CLR%';注意：CLR 整合不支援並發偵錯，建議開發人員使用專屬開發實例。
| 反模式 | 徵兆 | 對策 |
|---|---|---|
| 缺乏 Set-based 操作 (迴圈套迴圈) | Profiler 顯示大量單行查詢 | 改寫為 JOIN/集合運算；移至中介層處理 |
| CLR 內大量 CPU 密集運算 | sys.dm_clr_appdomains 顯示高 CPU | 評估移至中介層；若留資料庫需嚴格負載測試 |
| 記憶體中建立大型物件 (DataSet、集合) | .NET CLR Memory 計數器異常 | 分頁處理、確認資料提交、僅傳遞彙總結果 |
