SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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


Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.


No comments.

Leave a Comment

Please register or log in to leave a comment.