TechEd Braindump: SQL Server as a CLR Runtime Host

 Speaker: Vineet Rao

 

  • CLR goes thought SQL OS for Memory, Threads/fibers, Synchronization
  • SQL-CLR host co-ordinates Assembly Loading, Security Management, App domains, Escalation policy for exceptions
  • Memory Management
    • Most of CLR memory (GC Heap_ comes from multi-page memory allocator (outside Buffer-pol, from “MemToLeave”).
    • Max server memory setting does not cover CLR memory
  • Performance counters can be used within SQL-CRL
  • SQL Server sets a pre-defined list of FX assemblies that can be referenced in users assemblies.
    • The list is set by SQL server at the time of CLR initialization
    • Certain restrictions apply ie.. cannot call process.exit.
    • All other assembly load requests come to SQL Server.
  • All dependent assemblies should be either present and owned by the same user or supplied.
  • App Domains are the CLR mechanism for isolating code.
  • App domains are created and managed by SQL Server
  • SQL Server used two kinds of app domains DDL-time (design), execution-time
  • App domain mangement:
    • One per assembly owner in each database
    • All assemblies owned by same user are in same app domain
  • All managed “threads” are mapped to tasks managed by the SQL Server scheduler. Co-operative scheduling. Exception: GC threads.
  • Three permission sets in SQL Server
    • Safe
      • Internal computation plus data access
      • No p/invoke calls
      • .NET API’s with HostProtectionAttribute not callable
      • Must be verifiable
    • EXTERNAL_ACCESS
      • Save – Access to external resources through managed APIs
      • No direct pInvoke cals
      • .NET API’s with HostProtectionAttribute not callable
      • Must be verifiable
      • A reliability scalablity bucket
    • Unsafe – No controls.
  • Security – Use lowest permission set
  • SQL Server dictates CLR behavior on resource failure
    • Initial action on failure
    • Timeouts
    • “Escalation Policy”
  • Monitoring and Troubleshooting
    • Profiler trace events
      • CLR:Assembly Load – monitor assembly load request (success and failures)
      • SQL:BatchStarting, BatchCompleted
      • SP: Starting, Completed, StmtStarting, StmtCompleted
    • Performance Counters
      • CLR Execution
      • All .Net performance counters.

 

Disclaimer: These are my notes from my experience at TechEd 2005. I do not guarantee the validity or accuracy of these notes. They are my thoughts and what information I felt was important at the time.