Common Language Runtime (CLR) Host

Before Microsoft introduced the .NET Framework, they took pains to ensure that the framework itself would be non-proprietary and suitable for international standardization. To accomplish this, Microsoft introduced a standard known as the Common Language Infrastructure (CLI), which specified how applications written in different high-level languages could operate in multiple system environments without changing any of the high-level code. For more information on the Common Language Infrastructure standard, see "The CLI Specification" on MSDN at http://msdn2.microsoft.com/en-us/netframework/aa497266.aspx.

Microsoft's implementation of the CLI is known as the common language runtime (CLR). The CLR is responsible for compiling code into Intermediate Language (IL), and then managing the execution of the code. The CLR is a component of the Microsoft .NET Framework. The .NET Framework consists of several key components such as Assemblies, just-in-time compiler, and the Common Type System. Although not an actual component of the .NET Framework, the Framework does rely on the use of namespaces, which is also described here.

• Namespaces Namespaces in the .NET Framework are a logical grouping of code classes that enable developers to pick and choose which components they wish to use. For example, the Microsoft.SqlServer namespace contains .NET Framework classes that support SQL Server programming.

• Assemblies Assemblies are the smallest unit of deployment within a .NET application. Assemblies are used to group logical functions into a single execution environment.

• Just-in-Time (JIT) Compiler The JIT compiler is responsible for compiling the IL code generated by the CLR and preparing it for execution.

• Common Type System (CTS) The CTS is responsible for ensuring that object classes known as types can be understood across languages. For example, a "String" type in C#.NET is compatible with a "String" in Visual Basic .NET because of the CTS.

With SQL Server 2005, Microsoft decided to make the features of the .NET Framework available to developers using SQL Server. This presented a challenge due to the way SQL Server and SQLOS interact with the operating system. To overcome this challenge, Microsoft developed a CLR host, which enables SQL Server to host the CLR. This enables developers to build applications that are executed by the CLR, but don't require a context switch to the underlying operating system to execute. In SQL Server 2000 and earlier, if developers wanted to utilize other programming languages to develop objects within SQL Server, they needed to create extended stored procedures (XPs). The problem with extended stored procedures was that they executed "out of band" of the SQL Server process, and if something went wrong, it was possible for the XPs to cause the entire system to crash. By creating the CLR host and enabling developers to write native .NET Framework code within the SQL Server execution environment, this problem was alleviated. (Note that the SQLCLR host only accesses the operation system through SQLOS and never directly.)

This section describes how best to take advantage of this technology from the perspective of a database application developer, comparing CLR integration techniques with existing programming language support in SQL Server: Transact-SQL (T-SQL) and XPs.

Design Goals for the CLR Host

When Microsoft designed the CLR host for SQL Server 2005, they had several design goals, including reliability, scalability, security, and performance.

Reliability

Code executing in the CLR should not be permitted to perform operations that compromise the integrity of the database engine, such as displaying a message box requesting a user response or exiting the process. CLR code should not be able to directly access SQLOS memory buffers or internal data structures. This is the only way to ensure subsystem integrity and reliability.

When managed code in the .NET Framework APIs encounters critical exceptions, such as out-of-memory or stack overflow, it is not always possible to recover and ensure consistent and correct actions for their implementation. These APIs raise a thread abort exception in response to these failures.

When hosted in the CLR host, thread aborts are handled as follows: the CLR detects any shared state in the application domain in which the thread abort occurs. The CLR does this by checking for the presence of synchronization objects. If there is shared state in the application domain, then the application domain itself is unloaded. The unloading of the application domain stops database transactions that are currently running in that application domain and rolls them back. Because the presence of shared state can widen the impact of such critical exceptions to user sessions other than the one triggering the exception, Microsoft has taken steps in SQL Server and the CLR have taken steps to reduce the likelihood of shared state.

Scalability

To support the scalability goals defined for SQL Server, SQLOS and the CLR have different models for scheduling and memory management. SQLOS supports a cooperative, non-preemptive threading model in which the threads voluntarily yield execution periodically, or when they are waiting on locks or I/O. The CLR uses a preemptive threading model. If CLR code running inside the CLR host could directly call the operating system threading APIs, then it would not integrate well into the SQLOS task scheduler and could degrade the scalability of the system. The CLR does not distinguish between virtual and physical memory, but enables SQLOS to directly manage memory.

The different models for threading, scheduling, and memory management present an integration challenge for SQL Server. The architecture needs to ensure that the scalability of the system is not compromised by CLR code calling application APIs for threading, memory, and synchronization operations directly.

CLR interacts directly with SQLOS to create threads, both for running user code and for its own internal use. To synchronize between multiple threads, the CLR calls SQLOS synchronization objects. This enables the SQLOS scheduler to schedule other tasks when a thread is waiting on a synchronization object. For example, when the CLR initiates garbage collection, all of its threads wait for garbage collection to finish. Because the CLR threads and the synchronization objects they are waiting on are known to the SQLOS scheduler, SQL Server can schedule threads that are running other database tasks not involving the CLR. This also enables SQL Server to detect deadlocks that involve locks taken by CLR synchronization objects and employ traditional techniques for deadlock removal. CLR code runs preemptively in SQL Server. The SQLOS scheduler has the ability to detect and stop threads that have not yielded for a significant amount of time. The ability to hook CLR threads to SQLOS threads implies that the SQLOS scheduler can identify "runaway" threads in the CLR and manage their priority. Such runaway threads are suspended and put back in the queue. Threads that are repeatedly identified as runaway threads are not permitted to run for a given period of time so that other executing workers can run.

The CLR utilizes SQLOS 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 SQLOS 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.

Security

CLR code running in the database must follow SQL Server authentication and authorization rules when accessing database objects such as tables and columns. In addition, database administrators should be able to control access to operating system resources, such as file and network access, to database code. This becomes important because managed programming languages used by the CLR provide functions to access such resources. The system must provide a secure way for CLR code to access machine resources outside the database engine process.

Code Access Security (CAS) is implemented in the CLR host through the use of assembly-level attributes. These attributes are deployed to the database within the assembly. The attributes and their function are shown in Table B-1.

Table B-1: CLR Host Assembly Permissions
Open table as spreadsheet

Permission Set	SAFE	EXTERNAL_ACCESS	UNSAFE
Code Access Security	Execute Only	Execute + External Access	Unrestricted
Programming model restrictions	Yes	Yes	No
Verifiability	Yes	Yes	No
Ability to call native code	No	No	Yes

SAFE is the most reliable and secure mode with associated restrictions in terms of the permitted programming model. SAFE assemblies are given enough permission to run, perform computations, and are granted access to the local database. SAFE assemblies need to be verifiably type safe and are not permitted to call unmanaged code.

UNSAFE is for highly trusted code that can only be created by database administrators. This trusted code has no code access security restrictions, and it can call unmanaged (native) code. For additional information on SAFE and UNSAFE assemblies, visit http://msdn2.microsoft.com/en-us/library/ms189524.aspx.

EXTERNAL_ACCESS provides an intermediate security option; it permits code to access resources external to the database, but it still has the reliability guarantees of SAFE.

SQL Server uses the host-level CAS policy layer to set up a host policy that grants one of the three sets of permissions based on the permission set stored in SQL Server catalogs. Managed code running inside the database always gets one of these code access permission sets.

The CLR host loads assemblies belonging to the same owner in the same application domain. By virtue of a set of assemblies running in the same application domain, assemblies are able to discover each other at execution time using the Microsoft .NET Framework reflection application programming interfaces or other means, and they can call into them in late-bound fashion. Because such calls are occurring against assemblies belonging to the same owner, there are no SQL Server permissions checked for these calls. The placement scheme of assemblies in application domains is designed primarily to achieve scalability, security, and isolation goals; however, Microsoft has not guaranteed that this functionality will be present in future versions of SQL Server.

CLR Services

The CLR provides a number of services to help achieve the design goals of CLR integration with SQL Server, such as type safety verification, Application Domains, Code Access Security and Host Protection Attributes.

Type Safety Verification

Type-safe code is code that accesses memory structures only in well-defined ways. For example, given a valid object reference, type-safe code can only access objects exactly as they are defined, reducing memory access errors (or general protection faults). When assemblies are loaded in the CLR, prior to the MSIL being compiled using JIT compilation, the runtime performs a verification phase that examines code to determine its type safety.

Application Domains

The CLR supports the idea of application domains as zones of execution within the SQLOS process where managed code assemblies can be loaded and executed. The application domain boundary provides isolation between assemblies. The assemblies are completely isolated in terms of their ability to interact with one another. Application domains are also the mechanism for loading and unloading code. Code can be unloaded from memory only by unloading an entire application domain.

Code Access Security (CAS)

The CLR security system provides a way to control the kinds of operations managed code can perform by assigning permissions to code. Code access permissions are assigned based on the signature, or strong name of the code.

The CLR offers a system-wide policy that can be set by the system administrator. This policy defines the permissions for any managed code running on the system. In addition, the CLR host provides additional security to specify additional restrictions on managed code. If a managed API in the .NET Framework exposes operations on resources that are protected by a code access permission, the API will demand that permission before accessing the resource. This demand causes the CLR security system to trigger a comprehensive check of every assembly in the chain. Only if the entire call chain has permission will access to the resource be granted.

Host Protection Attributes (HPAs)

The CLR provides a mechanism for interacting with managed APIs that are part of the .NET Framework and have certain attributes that might be of interest to a host of the CLR. For example, the SharedState attribute indicates whether the API exposes the ability to create or manage static class fields. Synchronization indicates whether the API exposes the ability to perform thread management. ExternalProcessMgmt indicates whether the API exposes a way to control the host process. Given these attributes, the developer can specify a list of HPAs, such as the SharedState attribute, that should be disallowed in the hosted environment. In this case, the CLR denies attempts by user code to call APIs that are annotated by the HPAs in the prohibited list.

CLR Database Object Types

In SQL Server 2005, several database object types can be implemented in CLR code. These object types are as follows:

• CLR Stored Procedures These are stored procedures that utilize CLR code instead of TSQL code. They have the same execution characteristics as a T-SQL stored procedure; for example, the process to execute a CLR stored procedure from client code is identical to executing a TSQL stored procedure.

• CLR Triggers These are database triggers that utilize CLR code instead of TSQL code. Using CLR triggers, database developers can implement complex logic that is not possible in T-SQL. CLR triggers function identically to T-SQL stored procedures.

• User-Defined Types (UDTs) User-Defined Types are a way for database developers to apply object orientation into database code. For example, database developers can implement a custom class in .NET code that has all of the attributes required to represent a specific object. This class can then be created as a UDT and used to create a column and store data in a SQL Server 2005 table.

• CLR Table-Valued Functions (TVFs) CLR table-valued functions have one very important advantage over TSQL TVFs: They do not need a temporary work table to store results and can begin streaming the data back immediately. This is especially important for user perceptions of performance for large data sets.

• User-Defined Aggregates (UDAs) User-Defined Aggregates are useful when developing applications that perform complex mathematical operations. T-SQL can perform mathematical functions, but it is not designed for that purpose. Using UDAs, database developers can exert tight control over how aggregate calculations are performed.

CLR vs. Transact-SQL

Transact-SQL (T-SQL) is the native programming language supported by SQL Server. It is compliant with both the American National Standards Institute (ANSI) and International Organization for Standardization (ISO) standards for SQL and contains data-manipulation features and data-definition features. The data-manipulation features can be broadly categorized into two parts: a declarative query language (composed of SELECT/INSERT /UPDATE/DELETE statements) and a procedural language (WHILE, assignment, triggers, cursors, etc.). Generally, CLR support in SQL Server provides an alternative to the procedural portion of T-SQL.

Even without CLR support, it is important to recognize that database applications should use the declarative query language as much as possible. This portion of the language is able to leverage the power of the query processor, which is best able to optimize and perform bulk operations. Developers should only resort to CLR programming to express logic that cannot be expressed within the query language. All of this remains true with CLR support in SQL Server: the CLR should not be used to write procedural code that can be expressed using the declarative features of the T-SQL language. Developers should be aware that there are a number of significant enhancements to the T-SQL query language in SQL Server 2005 that augment the power of the T-SQL query language; they should ensure that they are taking full advantage of them before writing procedural code, whether in the CLR or otherwise. New features in T-SQL that should be considered before writing CLR code are the ability to write recursive queries to traverse recursive hierarchies in a table; new analytical functions such as RANK and ROW_NUMBER that enable ranking rows in a result set; and new relational operators such as EXCEPT, INTERSECT, APPLY, PIVOT, and UNPIVOT. Developers should view the CLR as an efficient alternative for logic that cannot be expressed declaratively in the query language.

In T-SQL, query language statements such as SELECT, INSERT, UPDATE, and DELETE are simply embedded within procedural code. CLR code uses the ADO.NET data access provider for SQL Server (SqlClient). Using this approach, all query language statements are represented by dynamic strings that are passed as arguments to methods and properties in the ADO.NET API. Because of this, data access written using the CLR can be more verbose than T-SQL. More importantly, because the SQL statements are encoded in dynamic strings, they are not compiled or validated until they are executed, which affects both the debugging of the code and its performance. It is important to note that both T-SQL-based and CLR-based programming models use the same SQL query language; only the procedural portions differ.

One issue that developers have with programming CLR code inside of SQL Server is the fact that data access is not as easy as it is with T-SQL. In T-SQL, returning data is as simple as using a SELECT statement. With the CLR and the Native Data Access Provider, developers must use a SqlPipe object to return data to the client, as shown below:

 //C# using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public static void SqlPipeTest()     {         // Connect through the context connection.         using (SqlConnection connection = new SqlConnection("context connection=true"))         {             connection.Open();             SqlCommand command = new SqlCommand(                 "SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor " +                 "WHERE CreditRating <= @rating", connection);             command.Parameters.AddWithValue("@rating", rating);             // Execute the command and send the results directly to the client.             SqlContext.Pipe.ExecuteAndSend(command);         }     } } 'VB     <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub StoredProcExecuteCommand(ByVal rating As Integer)     Dim command As SqlCommand     ' Connect through the context connection     Using connection As New SqlConnection("context connection=true")         connection.Open()         command = New SqlCommand( _             "SELECT VendorID, AccountNumber, Name FROM Purchasing.Vendor " & _             "WHERE CreditRating <= @rating", connection)         command.Parameters.AddWithValue("@rating", rating)         ' Execute the command and send the results directly to the client         SqlContext.Pipe.ExecuteAndSend(command)     End Using End Sub 

The code uses a SqlConnection and a SqlCommand to execute a stored procedure and then return the data to the client using a SqlPipe object. This is a very simple example of the infrastructure required to return data to client applications from within CLR objects.

The SqlPipe object coupled with the context connection enables all data access to be maintained within the SQLOS process. This has a very succinct advantage over using XPs to accomplish similar tasks. In previous releases of SQL Server, XPs were the only alternative to T-SQL with which to write server-side code with logic that was difficult to write in T-SQL. CLR integration provides a more robust alternative to XPs. In addition, with CLR integration, many stored procedures can be better expressed as table-valued functions, enabling them to be invoked and manipulated using the query language.

Following are some of the benefits of using CLR procedures rather than XPs:

• Security control. SQL Server administrators have little control over what XPs can or cannot do. Using the Code Access Security (CAS) model, a SQL Server administrator can assign one of three permission buckets-SAFE, EXTERNAL_ACCESS, or UNSAFE-to exert varying degrees of control over the operations that managed code is permitted to perform.

• Reliability. Managed code, especially in the SAFE and EXTERNAL_ACCESS permission sets, provides a more reliable programming model than XPs do. Verifiable managed code ensures that all access to objects is performed through strongly typed interfaces, reducing the likelihood that the program accesses or corrupts memory buffers belonging to SQL Server.

• Data access. With XPs, an explicit connection back to the database-a loop-back connection-must be made to access the local SQL Server database. In addition, this loop-back connection must be explicitly bound to the transaction context of the original session to ensure that the XP participates in the transaction in which it is invoked. Managed CLR code can access local data using a more natural and efficient programming model that takes advantage of the current connection and transaction context.

• Additional data types. The managed APIs support new data types (such as XML, (n)varchar(max), and varbinary(max)) introduced in SQL Server 2005, while the ODS APIs have not been extended to support these new types.

• Scalability. The managed APIs that expose resources such as memory, threads, and synchronization are implemented on top of the SQL Server resource manager, enabling SQL Server to manage these resources for CLR code. Conversely, SQL Server has no view or control over the resource usage of an XP. If an XP consumes too much CPU time or memory, there is no way to detect or control this from within SQL Server. With CLR code, SQL Server can detect that a given thread has not yielded for a long period of time and force the task to yield so that other work can be scheduled. Consequently, using managed code provides for better scalability and robustness.

As mentioned above, CLR routines can outperform XPs when the intent is to simply obtain some data and return it to the client. For code that does not involve data access or sending results, comparing the performance of XPs and managed code is a matter of comparing managed code with native code. In general, managed code cannot beat the performance of native code in these scenarios. Furthermore, there is an additional cost during transitions from managed to native code when running inside SQL Server because SQL Server needs to do additional maintenance on thread-specific settings when switching context between native code and back. Consequently, XPs can significantly outperform managed code running inside SQL Server for cases where there are frequent transitions between managed and native code.

CLR Code That Solves Common Problems

One of the challenges that database developers face when considering whether to use CLR code or T-SQL is deciding what problems will be solved within the code. For example, a common problem faced by database developers is how to parse a string of input and use the individual elements. In .NET code, this is easy using the Split() function; however, in T-SQL, the problem requires looping string manipulation. The following code demonstrates a CLR TVF that accepts input in the form of semicolon-delimited strings and returns a single row for each delimited string in the input:

 //C# using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow",TableDefinition="value nvarchar(60)")]     public static IEnumerable SplitString(SqlString str)     {         return str.Value.Split(';');     }     public static void FillRow(object row, out string str)     {         str = (string)row;     } } 'VB Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class UserDefinedFunctions     <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", _         TableDefinition:="value nvarchar(60)")> _     Public Shared Function SplitString(ByVal str As SqlString) As IEnumerable         Return str.Value.Split(";"c)     End Function     Public Shared Sub FillRow(ByVal row As Object, _         ByRef str As String)         str = CType(row, String)     End Sub End Class 

Using the TVF in a T-SQL stored procedure is a very simple process. For example, if the UI of an application captured line items for an order in a semicolon-delimited string, a T-SQL stored procedure could be created to accept the full string (with delimiters) and then call the TVF to break apart the string for insert into the LineItems table, as shown in the following T-SQL code:

 CREATE PROCEDURE Insert_Order @cust_id int, @lineitems nvarchar(8000) AS BEGIN     INSERT LineItems     SELECT * FROM dbo.SplitString(@lineitems) END 

Another common problem developers face is when to use T-SQL aggregate functions versus using middle tier code to perform aggregations. T-SQL aggregates can be very efficient when the bounds of the aggregation are known at design time; however, it is sometimes appropriate to aggregate data based on a variable. For example, the following code demonstrates a CLR aggregate that can aggregate data based on the value of a column:

 //C# using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)] public struct DomesticSales {     private double m_domesticSales;     public void Init()     {         m_domesticSales = 0;     }     public void Accumulate(SqlString Country)     {         if (Country == "USA")         {             ++m_domesticSales;         }     }     public void Merge(DomesticSales Group)     {         m_domesticSales += Group.m_domesticSales;     }     public double Terminate()     {         return m_domesticSales;     } } 'VB Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _ Public Structure DomesticSales     Private m_DomesticSales As Double     Public Sub Init()         m_DomesticSales = 0     End Sub     Public Sub Accumulate(ByVal Country As SqlString)         If Country = "USA" Then m_DomesticSales = m_DomesticSales + 1     End Sub     Public Sub Merge(ByVal value As DomesticSales)         value.m_DomesticSales = m_DomesticSales     End Sub     Public Function Terminate() As Double         Return m_DomesticSales     End Function End Structure 

Deciding When to Use CLR Code

The decision regarding whether to use a stored procedure in conjunction with the SqlPipe object or a TVF depends on several factors: usability requirements, the source of the data, the need for side effects, and the typing requirements for the results.

Usability

It might be desirable to reuse or further manipulate results produced inside a TVF or a stored procedure. Table-valued functions are more versatile from a usability perspective, as the return type of a TVF is a relational rowset that can be used in any place where such a construct is permitted. In particular, it can be used in the FROM clause of SELECT statements, and as such, the results produced can benefit from the usability of SELECT in subqueries, INSERT…SELECT statements, derived tables, table expressions, etc. However, using the T-SQL language, stored procedures can be composed only as part of the INSERT…EXEC combination that enables produced results to be stored in a permanent or temporary table. The INSERT operation represents an actual copy of the data, which will likely have a performance impact. If usability and reuse of results from within the server is a requirement, TVFs are a better alternative. If the results produced need to only be streamed back to the client or middle tier, either approach is reasonable.

Source of the Data

The source of the data being returned is another important factor in deciding between T-SQL-based implementations and CLR-based implementations. Results can be produced either by reading from some source in the local instance using the ActiveX Data Object (ADO).NET provider, or from a source external to SQL Server. For external sources, a CLR-based implementation is a better choice than T-SQL because of the ease with which the logic accessing the external data can be implemented. In the case of generating results based on a query executed on the local instance using the ADO.NET provider, a stored procedure would generally execute a query, iterate through the result, and perform some operation on the rows before sending them back through a SqlPipe.

Operations with Side Effects

In general, operations that produce side effects, such as DML statements or transaction operations, are disallowed from user-defined functions, including table-valued functions. These operations might be desired, however. For example, one might wish to set a SAVEPOINT transaction, perform some operation, and roll back to the SAVEPOINT in case an error occurs.

Typing of Results and Number of Results

The description of results produced by a CLR-stored procedure through SqlPipe differs from that of a CLR TVF and is consistent with results in T-SQL. A TVF is strongly typed, and as part of the registration (CREATE FUNCTION) statement, it must statically define the type of its return value.

Transaction Management in CLR Code

Another factor when deciding whether to use CLR objects is determining how transactions will be handled. The Microsoft .NET Framework 2.0 (which SQL Server 2005 requires) introduced a new namespace called System.Transactions. System.Transactions greatly extends the transaction-management capability of the .NET Framework. One major enhancement that System.Transactions provides is the concept of a current transaction, which is available through System.Transaction.Current. To maintain consistency with the .NET Transaction Framework, CLR code operates in the same fashion. If a transaction was active at the point where SQLCLR code is entered, then the transaction will be surfaced to the SQLCLR API through the System.Transactions.Transaction class. Specifically, Transaction.Current will be non-null. In most cases, you don't need to access the transaction explicitly. For database connections, ADO.NET will check Transaction.Current automatically when the connection is opened and will enlist the connection in that transaction transparently unless developers specify otherwise. However, there are a few scenarios in which you might want to use the transaction object directly, such as when you want to abort the external transaction from within your stored procedure or function. In this case, you can simply call Transaction.Current.Rollback(). If you want to enlist a resource that doesn't do automatic enlistment, or for some reason wasn't enlisted during initialization, you might also use the transaction object directly.

The following code demonstrates CLR code using explicit transactions through the System.Transactions namespace:

 //C# namespace TK442AppendixCodeCSharp {     using System.Data;     using System.Data.SqlClient;     using System.Transactions;     using Microsoft.SqlServer.Server;     public partial class StoredProcedures     {         [Microsoft.SqlServer.Server.SqlProcedure()]         public static void SampleSP()         {             // start a transaction block             using (TransactionScope tx = new TransactionScope())             {                 // connect to the context connection                 using (SqlConnection conn = new SqlConnection("context connection=true"))                 {                     conn.Open();                     // do some changes to the local database                 }                 // connect to the remote database                 using (SqlConnection conn = new SqlConnection(                                             "server=MyServer; database=AdventureWorks;" +                                             "user id=MyUser; password=MyPassword"))                 {                     conn.Open();                     // do some changes to the remote database                 }                 // mark the transaction as complete                 tx.Complete();             }         }     } } 'VB Imports System.Data Imports System.Data.SqlClient Imports System.Transactions Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures     <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub SampleSP()         ' start a transaction block         Using tx As New TransactionScope()             ' connect to the context connection             Using conn As New SqlConnection("context connection=true")                 conn.Open()                 ' do some changes to the local database             End Using             ' connect to a remote server (don't hardcode the conn string in real code)             Using conn As New SqlConnection("server=MyServer; database=AdventureWorks;" & _                                             "user id=MyUser; password=MyPassword")                 conn.Open()                 ' do some changes to the remote database             End Using             ' mark the transaction as completed             tx.Complete()         End Using     End Sub End Class 

The previous code shows the simplest way of using System.Transactions. Simply surround the code that needs to be part of a transaction with a transaction scope. Notice that towards the end of the block, there is a call to the Complete method on the scope indicating that this piece of code executed its part successfully and it's okay to commit the transaction. If you want to abort the transaction, simply don't call Complete. The TransactionScope object will do the "right thing" by default. That is, if there was already a transaction active, then the scope will happen within that transaction; otherwise, it will start a new transaction.

The pattern is fairly simple: The transaction scope will either pick up an already active transaction or will start a new one. It is very important to ensure the code calls the dispose() method when code execution is complete, so make sure that the transaction code is within a using block, or make sure to explicitly dispose of the object. There is no requirement to use the newer transaction models with SQLCLR code; the existing .NET Framework 1.x and explicit SQL Server transactions still exist.

These transactions can be nested, in the sense that your stored procedure or function might be called within a transaction, and it would still be valid for you to call BeginTransaction. (Note that this does not mean you get "true" nested transactions; you'll get the same behavior that you'd get when nesting BEGIN TRAN statements in T-SQL.) There is a difference between transactions started in T-SQL stored procedures and the ones started in SQLCLR code: SQLCLR code cannot unbalance the transaction state on entry/exit of a SQLCLR invocation. This brings up a couple of limitations:

• You cannot start a transaction inside a SQLCLR frame and cannot commit it or roll it back; SQL Server will generate an error during frame exit. Similarly, you cannot commit or roll back an outer transaction inside SQLCLR code.

• Any attempt to commit a transaction that you didn't start in the same procedure will cause a run-time error.

Any attempt to roll back a transaction that you didn't start in the same procedure will doom the transaction (preventing any other side-effect operation from occurring), but the transaction won't disappear until the SQLCLR code is unloaded.

Transactions can be a very powerful tool in the arsenal of database developers, but you must take care to understand exactly how they work when integrating with CLR code.

Determining CLR Code Statistics

Another factor to weigh when deciding whether to use CLR objects is determining the memory utilization of CLR code. Sometimes a developer will choose to implement a CLR object only to realize that performance suffers due to excessive memory consumption. Unfortunately, there aren't any easy ways to determine how much memory a CLR object will consume before it is deployed, but every developer should make an effort to measure memory consumption of all CLR objects during the development cycle before they are placed into production. The easiest way to measure CLR memory consumption is by querying the sys.dm_os_memory_clerks DMV, as shown in the following code:

 SELECT    single_pages_kb +    multi_pages_kb +    virtual_memory_committed_kb AS [TotalMemory] FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLCLR'; 

The column "single_pages_kb" details memory allocated in the SQL Buffer Pool, "multi_pages_kb" details memory allocated by the SQL CLR host that is outside the SQL Buffer pool, and "virtual_memory_committed_kb" details memory allocated by the CLR directly through the bulk allocation interface. The memory is mostly used for the managed .NET garbage collector heap and the JIT compiler heap, and it is also stored outside of the SQL Buffer Pool.

Once you know how much memory SQL CLR is using on the server, it would be nice to know how much memory SQL CLR is permitted to use. When there is memory pressure on the server, SQL CLR will try to release memory by explicitly running garbage collection and possibly unloading objects from memory that are not currently used.

There are two types of memory pressure to monitor:

• Physical memory pressure based on the amount of available system memory

• Virtual address space memory pressure based on the number of available virtual addresses

Physical memory pressure is pretty straightforward; if your server is under load and running low on available memory, Windows will issue a LowMemoryResourceNotification, which SQL Server will recognize and handle. Virtual address space memory pressure is more difficult and generally more limiting to SQL CLR because it might cause memory pressure even when there is enough physical memory available. This might happen because, as was noted above, most SQL CLR memory allocations occur outside of the SQL Buffer Pool in what is called the MemToLeave area. The size of this area of memory is set by the -g flag on SQL Server startup, but by default, it is at least 256 MB.

Another item that database developers want to closely manage is how much time CLR objects spend executing. You can determine this by executing the following query:

 SELECT    (SELECT text FROM sys.dm_exec_sql_text(eqs.sql_handle)) AS query_text,    eqs.* FROM sys.dm_exec_query_stats AS eqs WHERE eqs.total_clr_time > 0 ORDER BY eqs.total_clr_time DESC 

The output of this query will provide the exact statement and how much time is spent executing in CLR, as well as offer statistics on various resource consumption by the statement.

Another item that database developers will want to keep track of is the number of CLR assemblies that have been deployed to a database. It is a good idea to minimize the number of assemblies deployed to any given database by ensuring that only those assemblies that are currently used are deployed to the database. The following query provides the assemblies that have been deployed to a database:

 SELECT    a.[name],    ad.[appdomain_name],    clr.[load_time] FROM sys.dm_clr_loaded_assemblies AS clr JOIN sys.assemblies AS a    ON clr.assembly_id = a.assembly_id INNER JOIN sys.dm_clr_appdomains AS ad    ON clr.appdomain_address = ad.appdomain_address 

The CLR offers database developers a very powerful tool to solve many of the perplexing problems of the day. Understanding how the CLR functions and when to use CLR versus T-SQL code is an important part in the overall application design cycle.

This section has discussed how the CLR functions, when to consider using CLR code, how transactions function, and how to obtain various management statistics related to the SQL CLR.