SQLMCM Training – Day 17

Today Bob spoke about Auditing, SQLCLR, XML, and Spatial Data. Because I already know these concepts very well, my study notes for today are very sparse.


  • SQL Trace has too much drawbacks from Auditing
    • Separate program
  • Column Level GRANT overrides a DENY
  • Auditing audits changes to Auditing
  • Auditing Targets can’t be shared
  • Auditing uses Extended Events in the background
  • You always need to do a CREATE SERVER AUDIT
    • Defines the target
    • Defines what to do when the audit is not available
    • QUEUE_DELAY = 0
      • Synchronous auditing without data loss


  • SQLCLR is always loaded, because it is internally used
    • Spatial, HierarchyID data types
  • Only for functions
    • If you can’t change the .NET code to use nullable types
  • SQLCLR uses the Multi Page Allocator
    • Uses memory outside the Buffer Pool
    • “MemToLeave” area, can be controlled through the “-g” startup parameter on x32
  • SQLCLR is not controlled by Max Server Memory
    • So you need to set Max Server Memory, so that SQLCLR also has some memory available
  • You get for each owner of an assembly per database an AppDomain
  • SQL Server Log shows which AppDomains are loaded
    • DDL AppDomain
      • For Assembly Verification during startup
    • Runtime AppDomain
      • For executing SQLCLR code
    • sys.dm_clr_appdomains
  • SQL Server calls the .NET GC, when memory pressure occurs
  • sys.dm_clr_tasks
    • forced_yield_count
  • SQL Server creates invisible assemblies, when you call one assembly from another assembly
    • The dependent assembly is invisible
    • You can’t register .NET code in an invisible assembly
    • You have to make that assembly visible by ALTER ASSEMBLY
    • Dependent assemblies are automatically dropped when the “root” assembly is dropped
    • sys.assembly_references
  • sys.dm_exec_query_stats
    • clr_time
  • When you execute SQL code in SQLCLR, it’s dynamic TSQL Code
    • This breaks Ownership Chaining
    • Use EXECUTE AS OWNER instead

Thanks for reading