Professional SQL Server 2005 Administration (Wrox Professional Guides)

In this section, we discuss what it means to be a .NET runtime host, deploying .NET assemblies, maintaining security for .NET assemblies, and monitoring performance. Also included is a short example demonstrating how to debug a .NET assembly. This section is not about how to write a cool .NET assembly, although we do create a small assembly to further your understanding of how it works. This section focuses more on administration.

SQL Server as .NET Runtime Host

A runtime host is defined as any process that loads the .NET runtime and runs code in the managed environment. The database programming model in SQL Server 2005 is significantly enhanced by hosting the Microsoft .NET Framework 2.0 Common Language Runtime (CLR). With CLR integration, also called SQLCLR, SQL Server 2005 enables .NET programmers to write stored procedures, user-defined functions, and triggers in any .NET compatible language, particularly C# and VB.NET.

The .NET code that SQL Server runs is completely isolated from SQL Server itself. Of course .NET code runs in the SQL Server process space, but SQL Server uses a construct in .NET called the AppDomain (Application Domain) to completely isolate all resources that the .NET code uses from the resources that SQL Server uses. The AppDomain protects SQL Server from all malicious use of system resources. Keep in mind that SQL Server manages its own thread scheduling, synchronization and locking, and of course memory management. There are other .NET hosts, such as ASP.NET and Internet Explorer, for which these tasks are managed by the CLR. There is no conflict of interest between SQL Server and CLR regarding who manages the resources. Obviously SQL Server wins because reliability, security, and performance are of the utmost importance for SQL Server, and changes have been made in how the managed hosting APIs work as well as in how the CLR works internally.

Figure 8-17 shows how SQL Server hosts the CLR .NET 2.0 hosts want to have hooks into the CLR's resource management and allocations. They achieve that by calling ICLRRunTimeHost. These APIs calls a shim DLL, MSCOREE.DLL, whose job is to load the runtime. The host (SQL Server) then can call ICLRHostRunTime::SetHostControl(). This method points to the IHostControl interface, which contains the method GetHostControl, which the CLR can call to delegate tasks such as thread management and memory management to the host (SQL Server). SQL Server uses this interface to take control of some functions that the CLR calls down to the OS directly.

Figure 8-17

The CLR calls SQL Server APIs for creating threads, both for running user code and for its own internal use. SQL Server uses a cooperative thread schedule model, whereas managed code uses preemptive thread scheduling. In cooperative thread scheduling, the thread must voluntarily yield control of the processor, while in preemptive thread scheduling the processor takes control back from the thread after its time slice has expired. Some greedy managed code may not yield for a long time and may monopolize the CPU time. SQL Server can identify those "runaway" threads, suspend them, and put them back in the queue. Some threads that are identified repeatedly as runaway threads are not allowed to run for a given period of time, which enables other worker threads to run.

Only one instance of the runtime engine can be loaded into the process space during the lifetime of a process. It is not possible to run multiple versions of the CLR within the same host.

Application Domains

In .NET, processes can be subdivided into execution zones called application domains (AppDomains) within a host (SQL Server) process. The managed code assemblies can be loaded and executed in these AppDomains. Figure 8-18 shows the relationship between a process and AppDomains.

Figure 8-18

SQL Server isolates code between databases by using AppDomains. This means that for each database, if you have registered an assembly and you invoke some function from that assembly, an AppDomain is created for that database. Only one AppDomain is created per database. Later you will look at some DMVs that you can use to find out information about AppDomains.

T-SQL versus CLR

We mentioned earlier that you can write stored procedures, triggers, and functions using CLR-compatible languages. Does that mean DBAs need to take a crash course on C# or VB.NET? No, but you need to at least understand why code is returned in .NET languages versus T-SQL. You are the one who is going to run the production environments, so you need to be part of the decision regarding which set of tools is chosen for your application.

The data manipulation can be broadly categorized into two parts: a declarative query language and a procedural query language. The declarative query language is composed of SELECT,INSERT,UPDATE, and DELETE statements, while a procedural language is composed of triggers, cursors, and WHILE statements. SQL CLR integration provides an alternative support to the procedural portion of T-SQL. Database applications should look to procedural programming if you cannot express the business logic you need with a query language. T-SQL is best when you perform set-based operations. It can take advantage of the query processor, which is best able to optimize the set operations. Do not write CLR code to start processing row-by-row operations, which you can do best with T-SQL in set operations. However, if your application requires performing complex calculations on a per-row basis over values stored in database tables, you can access the results from your table by first using SELECT and then by performing row-by-row operations using CLR code. Of course, there is a transition cost between the CLR and SQL layer, but if you are performing operations on high-volume data, the transition cost may be negligible.

Extended Stored Procedure versus CLR

To write server-side code with logic that was difficult to write in T-SQL, the only option prior to SQL Server 2005 was to write extended stored procedures (XPs). CLR integration in SQL Server 2005 now provides a more robust way to do those operations with managed code. The following list describes some of the benefits SQL CLR integration provides over extended stored procedures:

• Granular control: SQL Server administrators have little control over what XPs can or cannot do. Using the Code Access Security 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 allowed to perform.

• Reliability: There is no possibility of managed, user-code access violations making SQL Server crash, especially with SAFE and EXTERNAL_ACCESS assemblies.

• New 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 Open Data Services (ODS) APIs have not been extended to support these new types.

These advantages do not apply if you register the assemblies with the UNSAFE permission set. Most extended stored procedures can be replaced, especially considering that Managed C++ is available as a coding option.

Enabling CLR Integration

By default, CLR integration is disabled. You cannot execute any .NET code until you intentionally change the configuration in SQL Server to allow CLR integration. You can do that with the GUI, the Surface Area Configuration tool, or using a T-SQL script. The shortcut to the tool is located in StartAll ProgramsSQL Server 2005Configuration Tools. Select the SQL Server Surface Area Configuration option, and then select Surface Area Configuration for Features. Figure 8-19 shows where you can enable or disable CLR integration within the Surface Area Configuration tool.

Figure 8-19

If you prefer to use a T-SQL script, the following script will do the same thing:

EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO SELECT * FROM sys.configurations WHERE name = 'clr enabled'

You can check serverwide configuration using the catalog view sys.configuration. Look in the value_in_use column for the current configured value in the sys.configuration view.

Creating the CLR Assembly

In this section we will create a small C# Table Valued Function in order to demonstrate how to deploy an assembly, maintain security, and look for CLR objects in the database. The following code will access the specified directory and list all the files and attributes as a tabular result set. This example also illustrates how impersonation works. If you don't have Visual Studio 2005 installed, you can just take the compiled DLL from this book's Web site at www.wrox.com, and register that in the database, as shown later, or you can use the command we provide to compile the DLL from the .cs file using csc.exe (the C# compiler), which ships free with the .NET SDK. The following code will create a Table Valued Function:

using System; using System.Collections; using System.Collections.Generic; using System.Text; using System.IO; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.Security.Principal; public class FileDetails { [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillFileRow" ,TableDefinition = "name nvarchar(4000), creationTime datetime, lastAccessTime datetime, lastWriteTime datetime, isDirectory bit, isReadOnly bit, length bigint" )] public static IEnumerable GetFileDetails(string directoryPath) { try { DirectoryInfo di = new DirectoryInfo(directoryPath); return di.GetFiles(); } catch (DirectoryNotFoundException dnf) { return new string[1] { dnf.ToString() }; } catch (UnauthorizedAccessException ave) { return new string[1] { ave.ToString() }; } } [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillFileRowWithImpersonation", TableDefinition = "name nvarchar(4000), creationTime datetime, lastAccessTime datetime, lastWriteTime datetime, isDirectory bit, isReadOnly bit, length bigint")] public static IEnumerable GetFileDetailsWithImpersonation(string directoryPath) { WindowsIdentity clientId = null; WindowsImpersonationContext impersonatedUser = null; clientId = SqlContext.WindowsIdentity; try { try { impersonatedUser = clientId.Impersonate(); if (impersonatedUser != null) return GetFileDetails(directoryPath); else return null; } finally { if (impersonatedUser != null) impersonatedUser.Undo(); } } catch { throw; } } public static void FillFileRow(object fileData, out SqlString name, out SqlDateTime creationTime, out SqlDateTime lastAccessTime, out SqlDateTime lastWriteTime, out SqlBoolean isDirectory, out SqlBoolean isReadOnly, out SqlInt64 length) { FileInfo info = fileData as FileInfo; if (info == null) { name = "Error, directory list failed: "+ fileData.ToString(); creationTime = SqlDateTime.Null; lastAccessTime = SqlDateTime.Null; lastWriteTime = SqlDateTime.Null; isDirectory = SqlBoolean.Null; isReadOnly = SqlBoolean.Null; length = SqlInt64.Null; } else { name = info.Name; creationTime = info.CreationTime; lastAccessTime = info.LastAccessTime; lastWriteTime = info.LastWriteTime; isDirectory = (info.Attributes & FileAttributes.Directory) > 0; isReadOnly = info.IsReadOnly; length = info.Length; } } public static void FillFileRowWithImpersonation(object fileData, out SqlString name, out SqlDateTime creationTime, out SqlDateTime lastAccessTime, out SqlDateTime lastWriteTime, out SqlBoolean isDirectory, out SqlBoolean isReadOnly, out SqlInt64 length) { WindowsIdentity clientId = null; WindowsImpersonationContext impersonatedUser = null; clientId = SqlContext.WindowsIdentity; try { try { impersonatedUser = clientId.Impersonate(); if (impersonatedUser != null) FillFileRow(fileData, out name, out creationTime, out lastAccessTime, out lastWriteTime, out isDirectory, out isReadOnly, out length); else { FillFileRow("Error: Impersonation failed!", out name, out creationTime, out lastAccessTime, out lastWriteTime, out isDirectory, out isReadOnly, out length); } } finally { if (impersonatedUser != null) impersonatedUser.Undo(); } } catch { throw; } } }

The preceding code has two main methods: GetFileDetails and GetFileDetailsWithImpersonation. The method GetFileDetails calls the method FillFileRow. The method GetFileDetailsWith Impersonation calls the methods FillFileRowWithImpersonation, GetFileDetails, FillFileRowWithImpersonation, and FillFileRow, in that order.

Later we will define two SQL TVFs called GetFileDetails and GetFileDetailsWithImpersonation. The function GetFileDetails will call the method GetFileDetails from this code. This method just gets the files list and other details such as creation time and last modified time using the SQL Server service account. The FillFileRow method will get this property and return it to the SQL TVF. The SQL TVF function GetFileDetailsWithImpersonation will call the method GetFileDetailsWith Impersonation, which sets the impersonation to your login; and when it calls the GetFileDetails method, it uses your login identity rather than the SQL Server service account. You'll see how to call these SQL TVF functions later.

Now you need to compile the code and create an assembly called GetFileDetails.dll. If you have Visual Studio 2005, you can open the solution GetFileDetails.sln. Build the project by clicking BuildBuild GetFileDetails. That will create the GetFileDetails.dll file in the debug directory under the bin directory in your solution directory. You can deploy this assembly using Visual Studio 2005, but we will do that manually so that you know how to write T-SQL script to deploy the assembly in a database.

If you do not have Visual Studio 2005, you can use following command-line utility, csc.exe, to build GetFileDetails.dll:

C:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Csc.exe /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C :\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /keyfile:c:\assembly \keypair.snk /out:c:\assembly\GetFileDetails.dll /target:library C:\assembly\Get FileDetails.cs

The /out parameter specifies the target location where GetFileDetails.dll will be created. The /target parameter specifies that this is a library (DLL). When you specify GetFileDetails.cs, you may have to specify the full path of the file if you are not in the directory where GetFileDetails.cs is located. In addition, the option /keyfile is used to sign the assembly. You will see why we have used this option when we register the assembly.

Now that you have created the DLL, you need to deploy the assembly and associate it with SQL user-defined functions.

Deploying the Assembly

Managed code is compiled and then deployed in units called assemblies. An assembly is packaged as a DLL or executable (.exe). The executable can run on its own, but a DLL needs to be hosted in an existing application. We have created a DLL in this case because SQL Server will host it. You now have to register this assembly using the new DDL statement CREATE ASSEMBLY, like this:

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] } [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ] [ ; ] <client_assembly_specifier> :: = '[\\computer_name\]share_name\[path\]manifest_file_name' | '[local_path\]manifest_file_name' <assembly_bits> :: = { varbinary_literal | varbinary_expression }

The arguments of the CREATE ASSEMBLY statement are as follows:

• assembly_name: This is the name of the assembly, and must be unique within the database. It should be a valid SQL identifier.

• Authorization: This specifies the name of a user or role as an owner of the assembly. If you do not specify this, then the owner will be the user executing this statement.

• client_assembly_specifier: This is the location of the assembly being loaded, with the filename of that assembly. You can specify the local path or a network location (such as a UNC path). SQL Server does not load multimodule assemblies. If the assembly you are loading is dependent on other assemblies, SQL Server will look for those dependent assemblies in the same directory and load them with the same owner as the root-level assembly. The owner of the dependent assemblies must be the same as the root assembly. The user executing the CREATE ASSEMBLY statement must have read permission to the share where the file is located.

• assembly_bits: This is the list of binary values that make up the assembly and its dependent assemblies. The value is considered as the root-level assembly. The values corresponding to the dependent assemblies can be supplied in any order.

• PERMISSION_SET: Grouped into three categories, the code access security specifies what the assembly can do:.

  • SAFE: The assembly runs under the caller's security context. An assembly with this code access security cannot access any resources outside of the SQL Server instance. This is the default and the most restrictive permission you can set for an assembly.

  • EXTERNAL_ACCESS: Assemblies created with this permission set can access external resources such as the file system, the network, environment variables, the registry, and more.

  • UNSAFE: This permission extends the external_access permission set. This permission allows the assembly to call unmanaged code.

Security Notes

You have just learned about what code access security can be applied to an assembly, but this section provides a bit more detail about the various options.

SAFE is the most restrictive option and the default. If the assembly doesn't need access to external resources, this is the permission you should use when you register the assembly.

You should use EXTERNAL_ACCESS whenever you need access to external resources. Keep in mind that when an assembly with this permission accesses external resources, it uses the SQL Server service account to do so. So make sure during the code review that impersonation is used while accessing the external resource. If impersonation is used, the external resources would be accessed under the caller's security context. Because this code can access external resources, before you register this assembly, you have to either set the TRUSTWORTHY bit in the database to 1, or sign the assembly, as you'll see later. The EXTERNAL_ACCESS assembly includes the reliability and scalability of the SAFE assembly.

We do not recommend using an UNSAFE assembly, because it could compromise SQL Server. It is no better than an extended stored procedure. You should have a very solid reason to use this option and should carefully examine what the assembly does, documenting it exactly. Keep in mind that when an assembly with this permission accesses external resources, it uses the SQL Server service account to do so.

The following table should help clarify how SQL Server applies rules for accessing resources outside of SQL Server when the assembly is created with either with the EXTERNAL_ACCESS or UNSAFE permission sets.

Registering the Assembly

Now you are ready to register the assembly into a database. Open the solution Sample3.

Open and run the script CreateDB.sql. This will create a database called CLRDB. Now open the script CreateAssembly.sql, shown here:

--------------------- USE CLRDB GO IF OBJECT_ID('GetFileDetails') IS NOT NULL DROP FUNCTION [GetFileDetails]; GO IF OBJECT_ID('GetFileDetailsWithImpersonation') IS NOT NULL DROP FUNCTION [GetFileDetailsWithImpersonation]; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'GetFileDetails') DROP ASSEMBLY [GetFileDetails]; GO --------------------- USE master GO IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'ExternalAccess_Login') DROP LOGIN ExternalAccess_Login; GO IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'ExternalAccess_Key') DROP ASYMMETRIC KEY ExternalAccess_Key; GO CREATE ASYMMETRIC KEY ExternalAccess_Key FROM EXECUTABLE FILE = 'C:\Assembly\GetFileDetails.dll' GO CREATE LOGIN ExternalAccess_Login FROM ASYMMETRIC KEY ExternalAccess_Key GO GRANT EXTERNAL ACCESS ASSEMBLY TO ExternalAccess_Login GO --------------------- USE CLRDB GO CREATE ASSEMBLY GetFileDetails FROM 'C:\Assembly\GetFileDetails.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; GO

Before you register the assembly with SQL Server, you must arrange for the appropriate permissions. Assemblies with UNSAFE or EXTERNAL_ACCESS permissions can only be registered and operate correctly if either the database TRUSTWORTHY bit is set (ALTER DATABASE CLRDB SET TRUSTWORTHY ON), or the assembly is signed with a key, that key is registered with SQL Server, a server principal is created from that key, and that principal is granted the external access or unsafe assembly permission. Here you'll use the latter approach so that you can also see how you can sign the assembly and register it, because the first approach is a simple matter of just setting the TRUSTWORTHY bit to ON. The latter approach is also more granular, and therefore safer. You should never register an assembly with SQL Server (especially with EXTERNAL_ACCESS or UNSAFE permissions) without thoroughly reviewing the source code of the assembly to make sure that its actions do not pose an operational or security risk.

In the previous code, you created a login called ExternalAccess_Login from the asymmetric key ExternalAccess_Key. When you created the assembly using the command-line option csc.exe, you specified the /keyfile option. Because of that option, the assembly GetFileDetails.dll was signed. You are now registering that key into SQL Server because in the CREATE ASYMMETRIC KEY statement in this code you specified the EXECUTABLE FILE option to point to the signed DLL. You have granted the EXTERNAL_ACCESS permission to ExternalAccess_Login. Then you create the assembly with the CREATE ASSEMBLY command. The assembly is now registered in the CLRDB database. Remember that once the assembly is registered in the database, you don't need the assembly file .dll, so you can move your database from server to server without worrying about that file. Of course, you have to put the source code from which you have created the DLL into your source control so that in the future, if you need to modify anything, you will do that in the source. If you want to set the TRUSTWORTHY bit to 1 for the database, you do not need to perform any of these steps except for creating the assembly.

Now open the CreateTVF.sql file and run it. The code is shown here:

USE CLRDB GO IF OBJECT_ID('GetFileDetails') IS NOT NULL DROP FUNCTION GetFileDetails GO CREATE FUNCTION GetFileDetails(@directory nvarchar(256)) RETURNS TABLE ( Name nvarchar(max) ,CreationTime datetime ,LastAccessTime datetime ,LastWriteTime datetime ,IsDirectory bit ,IsReadOnly bit ,Length bigint ) AS EXTERNAL NAME [GetFileDetails].[FileDetails].[GetFileDetails] GO IF OBJECT_ID('GetFileDetailsWithImpersonation') IS NOT NULL DROP FUNCTION GetFileDetailsWithImpersonation GO CREATE FUNCTION GetFileDetailsWithImpersonation(@directory nvarchar(256)) RETURNS TABLE ( Name nvarchar(max) ,CreationTime datetime ,LastAccessTime datetime ,LastWriteTime datetime ,IsDirectory bit ,IsReadOnly bit ,Length bigint ) AS EXTERNAL NAME [GetFileDetails].[FileDetails].[GetFileDetailsWithImpersonation] GO

You are creating two Table Value Functions, GetFileDetails and GetFileDetailsWithImpersonation. The T-SQL function GetFileDetails calls the method GetFileDetails of the class FileDetails from the assembly GetFileDetails. The T-SQL function GetFileDetailsWithImpersonation calls the method GetFileDetailsWithImpersonation of the class FileDetails from the assembly GetFile Details. Now you have mapped the T-SQL TVF to the methods in the assembly GetFileDetails.dll.

Now it's time to test these functions. Open the file TestGetFileDetails.sql. The code is shown here:

USE CLRDB GO DECLARE @TestDir nvarchar(256); SELECT @TestDir = 'D:\test' SELECT [Name], CreationTime, LastAccessTime, LastWriteTime, IsDirectory, IsReadOnly, Length FROM GetFileDetails(@TestDir) SELECT [Name], CreationTime, LastAccessTime, LastWriteTime, IsDirectory, IsReadOnly, Length FROM GetFileDetailsWithImpersonation(@TestDir)

In the preceding script, you set the variable @TestDir to 'D:\test' because you want to list all the files in the directory D:\test. You should set the permission on the directory D:\test so that only you have permission to that directory. If you have set the permission correctly, you will see the results shown in Figure 8-20 when you run the script.

Figure 8-20

The first function, GetFileDetails, fails with an Unauthorized Access exception. Because this assembly is registered with the EXTERNAL_ACCESS permission set, SQL Server uses the SQL Server service account to access the external resource, and because you set the permission on the directory D:\test so that only you can access it, the function failed. Of course, in this case the SQL Server Service account is not running under the security context with which you have run the function.

The second function, GetFileDetailsWithImpersonation, succeeded because you set the impersonation to the user who is connecting to SQL Server to execute the function. Now SQL Server will access the directory D:\test under the security context of the user executing the function, rather than the SQL Server service account.

ALTER ASSEMBLY

You can change the properties of an assembly or change the assembly code using the ALTER ASSEMBLY statement. Reload the assembly with the modified code and it will refresh the assembly to the latest copy of the .NET Framework module that holds its implementation, adding or removing files associated with it:

ALTER ASSEMBLY GetFileDetails FROM '\\MyMachine\Assembly\GetFileDetails.dll'

If you decide that an assembly should only be called by another assembly and not from outside, you can change the visibility of the assembly as follows:

ALTER ASSEMBLY GetFileDetails SET VISIBILTY = OFF

For full syntax details, you can refer to Books Online.

DROP ASSEMBLY

You can drop an assembly using the DROP ASSEMBLY statement. If the assembly is referenced by other objects, such as user-defined functions or stored procedures, you cannot drop the assembly until you drop those dependent objects:

DROP ASSEMBLY GetFileDetails

There is a NO DEPENDENTS option to the DROP ASSEMBLY statement. If you don't specify this option, all the dependent assemblies will also be dropped:

DROP ASSEMBLY GetFileDetails WITH NO DEPENDENTS

Cataloging Objects

You can get information about different CLR objects, such as CLR stored procedures and CLR functions, using the queries described in the following sections.

Assemblies

The following are some queries from catalog views you can use to get more information on registered assemblies.

The sys.assemblies view gives you all the registered assemblies in the database:

SELECT * FROM sys.assemblies

The following view will give you all the files associated with the assembly:

SELECT a.Name AS AssemblyName, f.name AS AssemblyFileName FROM sys.assembly_files f JOIN sys.assemblies a ON a.assembly_id = f.assembly_id

The following view will provide information about the assembly, its associated class, the methods in the class, and the SQL object associated with each assembly:

SELECT a.Name AS AssemblyName ,m.Assembly_Class ,m.Assembly_Method ,OBJECT_NAME(um.object_id) AS SQL_Object_Associated ,so.type_desc AS SQL_Object_Type ,u.name AS Execute_As_Principal_Name FROM sys.assembly_modules m JOIN sys.assemblies a ON a.assembly_id = m.assembly_id LEFT JOIN sys.module_assembly_usages um ON um.assembly_id = a.assembly_id LEFT JOIN sys.all_objects so ON so.object_id = um.object_id LEFT JOIN sys.sysusers u ON u.uid = m.Execute_As_Principal_id

CLR Stored Procedures

This query will give you the CLR stored procedure and other details associated with it:

SELECT schema_name(sp.schema_id) + '.' + sp.[name] AS [SPName] ,sp.create_date ,sp.modify_date ,sa.permission_set_desc AS [Access] FROM sys.procedures AS sp JOIN sys.module_assembly_usages AS sau ON sp.object_id = sau.object_id JOIN sys.assemblies AS sa ON sau.assembly_id = sa.assembly_id WHERE sp.type = 'PC'

CLR Trigger Metadata

This query will give you the CLR trigger's details:

SELECT schema_name(so.schema_id) + '.' + tr.[name] AS [TriggerName] ,schema_name(so.schema_id) + '.' + object_name(tr.parent_id) AS [Parent] ,a.name AS AssemblyName ,te.type_desc AS [Trigger Type] ,te.is_first ,te.is_last ,tr.create_date ,tr.modify_date ,a.permission_set_desc AS Aseembly_Permission ,tr.is_disabled ,tr.is_instead_of_trigger FROM sys.triggers AS tr JOIN sys.objects AS so ON tr.object_id = so.object_id JOIN sys.trigger_events AS te ON tr.object_id = te.object_id JOIN sys.module_assembly_usages AS mau ON tr.object_id = mau.object_id JOIN sys.assemblies AS a ON mau.assembly_id = a.assembly_id WHERE tr.type_desc = N'CLR_TRIGGER'

CLR Scalar Function

The following query will give you the CLR scalar function:

SELECT schema_name(so.schema_id) + '.' + so.[name] AS [FunctionName] ,a.name AS AssemblyName ,so.create_date ,so.modify_date ,a.permission_set_desc AS Aseembly_Permission FROM sys.objects AS so JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id JOIN sys.assemblies AS a ON sau.assembly_id = a.assembly_id WHERE so.type_desc = N'CLR_SCALAR_FUNCTION'

CLR Table Valued Function

This query will give you all CLR Table Valued Functions:

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [FunctionName] ,a.name AS AssemblyName ,so.create_date, so.modify_date ,a.permission_set_desc AS Aseembly_Permission FROM sys.objects AS so JOIN sys.module_assembly_usages AS sau ON so.object_id = sau.object_id JOIN sys.assemblies AS a ON sau.assembly_id = a.assembly_id WHERE so.type_desc = N'CLR_TABLE_VALUED_FUNCTION'

CLR User-Defined Aggregates

The following query will give you the CLR user-defined aggregates:

SELECT schema_name(so.schema_id) + N'.' + so.[name] AS [FunctionName] ,a.name AS AssemblyName ,so.create_date ,so.modify_date ,a.permission_set_desc AS Aseembly_Permission FROM sys.objects AS so JOIN sys.module_assembly_usages AS mau ON so.object_id = mau.object_id JOIN sys.assemblies AS a ON mau.assembly_id = a.assembly_id WHERE so.type_desc = N'AGGREGATE_FUNCTION'

CLR User Defined Types

This query will provide a list of the CLR user-defined types:

SELECT st.[name] AS [TypeName] ,a.name AS [AssemblyName] ,a.permission_set_desc AS AssemblyName ,a.create_date AssemblyCreateDate ,st.max_length ,st.[precision] ,st.scale ,st.collation_name ,st.is_nullable FROM sys.types AS st JOIN sys.type_assembly_usages AS tau ON st.user_type_id = tau.user_type_id JOIN sys.assemblies AS a ON tau.assembly_id = a.assembly_id

Application Domains

We talked earlier about application domains. Here we look at some DMVs that will give you more information about AppDomains in your servers. The following DMV will provide information about all the AppDomains currently loaded and their state:

SELECT * FROM sys.dm_clr_appdomains

Recall that an AppDomain is created for each database. An AppDomain has different states described in the column state in the DMV sys.dm_clr_appdomains. The following list describes the different possible AppDomain states:

• E_APPDOMAIN_CREATING: An AppDomain is being created. After the AppDomain is created, you will see the following type of entry in the SQL error log:.

  AppDomain 70 (InitiatorDB.dbo[runtime].69) created.

• E_APPDOMAIN_SHARED: An AppDomain is ready for use by multiple users. This is the state you will normally see after the AppDomain is loaded.

• E_APPDOMAIN_SINGLEUSER: An AppDomain is ready to use by a single user to perform the DDL operations (such as CREATE ASSEMBLY).

• E_APPDOMAIN_DOOMED: An AppDomain is about to be unloaded, but cannot be yet because of some threads still executing in it.

• E_APPDOMAIN_UNLOADING: SQL is telling the CLR to unload the AppDomain, usually because an assembly is dropped or altered. You will see the following type of message in the SQL error log when this happens:

  AppDomain 70 (InitiatorDB.dbo[runtime].69) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations. AppDomain 70 (InitiatorDB.dbo[runtime].69) unloaded.

• E_APPDOMAIN_UNLOADED: The CLR has unloaded the AppDomain. This is usually the result of an escalation procedure due to ThreadAbort,OutOfMemory, or an unhandled exception in user code. You will see an error message in the error log if any of these conditions occur before unloading the app domain.

• E_APPDOMAIN_ENQUEUE_DESTROY: An AppDomain is unloaded in the CLR and ready to be destroyed by SQL.

• E_APPDOMAIN_DESTROY: An App Domain is being destroyed by SQL.

• E_APPDOMAIN_ZOMBIE: An App Domain has been destroyed but all the references to it have not yet been cleaned up.

This view is helpful when the AppDomain in question is loaded, but it doesn't help much when the AppDomain is unloaded. The following query will give you all the states an AppDomain has gone through so you can see what exactly happened to it:

SELECT Timestamp ,rec.value('/Record[1]/AppDomain[1]/@address', 'nvarchar(10)') as Address ,rec.value('/Record[1]/AppDomain[1]/@dbId', 'int') as DBID ,d.name AS DatabaseName ,rec.value('/Record[1]/AppDomain[1]/@ownerId', 'int') as OwnerID ,u.Name AS AppDomainOwner ,rec.value('/Record[1]/AppDomain[1]/@type', 'nvarchar(32)') as AppDomainType ,rec.value('/Record[1]/AppDomain[1]/State[1]', 'nvarchar(32)')as AppDomainState FROM ( SELECT timestamp, cast(record as xml) as rec FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CLRAPPDOMAIN' ) T JOIN sys.sysdatabases d ON d.dbid = rec.value('/Record[1]/AppDomain[1]/@dbId', 'int') JOIN sys.sysusers u on u.uid = rec.value('/Record[1]/AppDomain[1]/@ownerId', 'int') ORDER BY timestamp DESC

Note that the DMV used in the sys.dm_os_ring_buffers query is not documented, so you should not rely on it for future releases of SQL Server. The DatabaseName column will indicate which database the AppDomain belongs to and what stages it has gone through.

Performance Monitoring

You can use Windows System Monitor, DMVs, and SQL Profiler for SQLCLR performance monitoring.

System Monitor

You can use Windows System Monitor (PerfMon.exe) to monitor CLR activities for SQL Server. Use the counter in the .NET CLR group in System Monitor. Choose the sqlservr instance when you monitor CLR counters for SQL Server. Use the following counters to understand the health and activity of the programs running under the SQL-hosted environment:

• .NET CLR Memory: Provides detailed information about the three types of CLR heap memory, as well as garbage collection. These counters can be used to monitor CLR memory usage and to flag alerts if the memory used gets too large. If code is copying a lot of data into memory, you may have to check the code and take a different approach to reduce memory consumption, or add more memory.

• .NET CLR Loading: SQL Server isolates code between databases by using an AppDomain. This set of counters enables monitoring of the number of AppDomains and the number of assemblies loaded in the system. You can also use some DMVs for AppDomain monitoring as described in the previous section.

• .NET CLR Exceptions: The Exceptions/Sec counter provides you with a good idea of how many exceptions the code is generating. The values vary from application to application because sometime developers use exceptions for some functionality, so you should monitor overtime to set the baseline and go from there.

SQL Profiler

SQL Profiler is not much use for monitoring the CLR because it has only one event for the CLR, called Assembly Load. This event tells you when an assembly is loaded with the message "Assembly Load Succeeded." If the load fails, it will provide a message indicating which assembly load failed and some error code.

DMVs

The DMVs related to SQL CLR provide very useful information on CLR memory usage, which assemblies are currently loaded, the current requests waiting in CLR, and more. The following sections describe some queries that help in monitoring the CLR.

SQL CLR Memory Usage

You can find out how much memory SQL CLR is using in KB with the following query:

SELECT single_pages_kb + multi_pages_kb + virtual_memory_committed_kb FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLCLR'

The first column, single_pages_kb, is the memory allocated in the SQL buffer pool. The second column, multi_pages_kb, indicates the memory allocated by the SQL CLR host outside of the buffer pool. The third column, virtual_memory_committed_kb, indicates the amount of memory allocated by the CLR directly through bulk allocation (instead of heap allocation) through SQL server.

Note that you will see a second row with 0 if you don't have a NUMA (non-uniform memory access) system. In a NUMA system, each node has its own memory clerk, so in that case, you would have to add the node totals to get the total memory usage.

Loaded Assemblies

The following query will give you all the currently loaded assemblies:.

SELECT a.name AS Assembly_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 JOIN sys.dm_clr_appdomains AS ad ON clr.appdomain_address = ad.appdomain_address

CLR Request Status

The following query will give you current request status in the 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

Time Spent in SQL CLR By a Query

The next query provides interesting statistics on a CLR query, such as execution count (how many times that query was executed), logical reads, physical reads, time elapsed, and last execution time:

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

Finding the .NET Framework Version in SQL Server

If the .NET Framework is loaded you can use the following query to determine which .NET Framework version is used in SQL Server. (The .NET load is a lazy load in SQL Server. It is only loaded when any CLR assembly is called for the first time.)

SELECT value AS [.NET FrameWork Version] FROM sys.dm_clr_properties WHERE name = 'version'

If the .NET Framework is not loaded and you want to find out which .NET Framework version SQL Server is using, run the following query:

SELECT Product_Version AS [.NET FrameWork Version] FROM sys.dm_os_loaded_modules WHERE name LIKE N'%\MSCOREE.DLL'