Monitoring Performance of SQL Server 2005 Objects

  • Article

Ever since the SQLCLR talk at TechEd San Diego, I've been trying to find out some more information on the monitoring options available in SQL Server 2005 for managed assemblies catalogued in the database. Balaji Rathakrishnan (one of the Group Program Managers on the SQL engine team) kindly granted me permission to reproduce some information he posted to one of the internal mailing lists on the subject:

We have a whole new set of new dynamic management views some of which can be used for monitoring CLR, notably:

  • sys.dm_os_loaded_modules shows whether CLR is currently loaded
    (e.g.)

      select name, description, * 
 from sys.dm_os_loaded_modules 
 where description like 
 '%Common Language Runtime%'

  • sys.dm_os_memory_clerks shows amount of memory being used by each component in SQL Server including SQLCLR; for example, how much memory is currently committed to the sqlclr gc heap?

      select virtual_memory_committed_kb, * 
 from sys.dm_os_memory_clerks 
 where type like '%SQLCLR%' 
 go

  • sys.dm_exec_query_stats shows execution statistics on the set of queries/batches that are currently in the plan cache. This will include queries/batches that invoke CLR code as well.

Thanks, Balaji!