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.

Auditing

• 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

• SQLCLR is always loaded, because it is internally used
  • Spatial, HierarchyID data types
• Only for functions
  • WITH RETURNS NULL ON NULL INPUT
  • 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

-Klaus