- SQL Server Management Studio
- SQL Server 2005 Infrastructure Management
- Operations Management Tools
- SQL Server 2005 Remote Management Features
- SQL Server Monitoring
- Managing Very Large Databases
- SQL Server Replication Enhancements
- High Availability for the Masses
- General Data Availability
- Indexed View
- Common Language Runtime and Database Administrator
- Business Intelligence and the Database Administrator
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Common Language Runtime and Database Administrator
During the beta of SQL Server 2005, I showed the CLR functionality to hundreds of database administrators. Their reactions ranged from "Whatever" to "Don’t put that in my database!" This section hopefully will demystify the CLR integration into SQL Server 2005. I would be remiss if I just said "Don’t worry." It might be worth demystifying the integration. None of the core functionality of SQL Server 2005 is run as managed code. That said, SMO interfaces are all managed code, SQL Server Management Studio is managed code, and the new Report Builder found in Reporting Services is also managed code. Chapter 4, "Features for Database Development," covers how and when to use the CLR. This section covers the basics of managing assemblies residing in SQL Server.
How Deep Is the Integration?
The Common Language Runtime (CLR) is deeply integrated into the database engine. The CLR is really an engine of sorts that hosts applications—a special kind of application created using managed code. SQL Server is really a cohost of managed code. The SQL Engine works directly with the CLR to manage assemblies that have been placed inside SQL Server and that are called by Transact-SQL or MDX queries. Figure 3-8 illustrates the coupling between the SQL Server Engine and the CLR.
)
Figure 3-8 The coupling between the SQL Engine and the CLR.
When you create an assembly in SQL Server, the bits are loaded into a table in the database. The assembly is registered but is not automatically pulled into memory. The bits simply exist in the database. The interaction between the CLR/hosting layer and the SQL operating system (OS) occurs until the assembly is called by a procedure. Upon evocation, the SQL Server Engine works with the CLR to manage memory, execution, and destruction of the running code for that assembly. Thus, the CLR goes through the SQL OS for the following:
- Memory. SQL Server manages its own memory. The CLR asks SQL for memory as needed.
- Threads/fibers get things done. Since SQL Server uses these natively, it makes sense for SQL to manage the CLR. All the threads are managed by SQL Server.
- Synchronization is moving data into and out of assemblies to TDS and in memory.
The hosting layer has a set of APIs that manage the communication between the SQL Server OS and CLR. When objects are called, the CLR asks SQL Server for memory buffers, thread allocations, and security. The hosting layer manages the multiple security layers. The CLR uses something called an app domain to create an execution context that is really just a container for all the managed code found in a particular namespace. The app domain provides a container that controls interassembly interaction. Basically, no interaction occurs between different app domains. Thus, if a problem occurs, such as a memory issue, the app domain is the main container for SQL Server to unload the assembly. The CLR manages the escalation policy for assemblies. The CLR also maintains the state of the assembly.
This topic could take up an entire chapter, but briefly, here’s what you need to know under All memory allocation from CLR through SQL Server. Most of the CLR memory (GC Heap) comes from a multipage memory allocator (outside Buffer-pool or MemToLeave). For memory-intensive operations, the SQL OS tells the CLR to initiate garbage collection to clear memory. Note that the MAX Memory setting in SQL Server does not cover CLR. This is because the CLR exists in the operating system memory, not the SQL OS. To understand what’s happening with the CLR, look at the following DMVs:
Sys.dm_os_memory_clerks Sys.dm_os_memory_objects
Most of the interaction between SQL OS and the CLR is thread oriented. The threads are managed by the SQL Server Scheduler, so they are visible to SQL Profiler and Activity Monitor. The SQL Profiler Per counters that are useful for assembly monitoring include # GC and the amount of memory allocated. Look at the memory clerks for SQLCLR and SQLCLRASSEMBLY. The threads are scheduled cooperatively. SQL Server scheduler was designed to punish nonyielding tasks—either managed or Transact-SQL—such that they are forced back. This should prevent a nonyielding task from taking down the server. A task that doesn’t yield is called back, stopped, and put back into the queue or forced to miss turns.
Here's a brief list of interesting ways to monitor assemblies:
- Profiler trace events:
- CLR:load assembly monitors assembly load requests (successes and failures)
- SQL:BatchStarting, BatchCompleted
- SP:Starting, Completed, StmtStarting, StmtCompleted monitor execution of Transact-SQL and CLR routines
- Performance counters:
- SQL Server: Total CLR time
- .NET CLR Memory
- Processor
- DMVs and catalog views:
- sys.assembly* shows basic information about the assemblies stored
- sys.dm_os_memory_clerks
- sys.dm_clr*
- sys.dm_exec_query_stats
- sys.dm_exec_requests
- sys.dm_exec_cached_plans
For more insight into when and how to use managed code, refer to Chapter 4 and Books Online. This topic is extremely new to SQL Server, and significant concern was voiced during the beta process, but you have a number of good resources for getting up to speed.