Managed code memory issues

There is an interesting article from MSDN Magazine titled "Identify and Prevent Memory Leaks in Managed Code"

http://msdn.microsoft.com/msdnmag/issues/07/01/ManagedLeaks/default.aspx

Are there any additional documents or utilities that people would suggest for monitoring and managing CLR impact on SQL server resources and performance?

Hi,

Assuming you are interested in SQL Server's hosting of the CLR you can find a few (not many) SQLCLR-specific counters in both Profiler & Performance Monitor....CLR is "asking" SQL Server for resources....see below BOL excerpts:

The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory. SQL Server can also reject CLR memory requests when system memory is constrained, and ask CLR to reduce its memory use when other tasks need memory.

Profiler Trace Events

SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine. By recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools. For more information, see Monitoring Events.

Event Description

Assembly Load Event Class

Used to monitor assembly load requests (success and failures).

SQL:BatchStarting Event Class, SQL:BatchCompleted Event Class

Provides information about Transact-SQL batches that have started or completed.

SPtarting Event Class, SP:Completed Event Class

Used to monitor the execution of Transact-SQL stored procedures.

SQLtmtStarting Event Class, SQLtmtCompleted Event Class

Used to monitor the execution of CLR and Transact-SQL routines.

Performance Counters

SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server lock or a Windows XP process. Each object contains one or more counters that determine various aspects of the objects to monitor. For more information, see Using SQL Server Objects.

Object Description

SQL Server, CLR Object

Total time spent in CLR execution.