SQL Server数据库引擎.NET CLR环境数据库管理员向导(9)

-- Loaded Assemblies (run in each database)
SELECT sa.[name]
, ad.[appdomain_name]
, clr.[load_time]
FROM sys.dm_clr_loaded_assemblies AS clr
INNER JOIN sys.assemblies AS sa
ON clr.assembly_id = sa.assembly_id
INNER JOIN sys.dm_clr_appdomains AS ad
ON clr.appdomain_address = ad.appdomain_address
-- SQL CLR Memory Usage
SELECT mo.[type]
, sum(mo.pages_allocated_count * mo.page_size_in_bytes/1024) 
AS N'Current KB'
, sum(mo.max_pages_allocated_count * mo.page_size_in_bytes/1024) 
AS N'Max KB'
FROM sys.dm_os_memory_objects AS mo
WHERE mo.[type] LIKE '%clr%'
GROUP BY mo.[type]
ORDER BY mo.[type]
-- SQL CLR Wait Statistics
SELECT ws.* 
FROM sys.dm_os_wait_stats AS ws
WHERE ws.wait_type LIKE '%clr%'
-- Requests that are currently in SQL CLR
SELECT session_id, request_id, start_time, status, command, database_id,
wait_type, wait_time, last_wait_type, wait_resource, cpu_time, 
total_elapsed_time, nest_level, executing_managed_code
FROM sys.dm_exec_requests
WHERE executing_managed_code = 1
-- Query performance and time spent in SQL CLR.
SELECT 
(SELECT text FROM sys.dm_exec_sql_text(qs.sql_handle)) AS query_text, qs.*
FROM sys.dm_exec_query_stats AS qs
WHERE qs.total_clr_time > 0 
ORDER BY qs.total_clr_time desc
-- Obtaining CLR Execution performance counter values.
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%CLR%'