CLR Integration Best Practices

1
CLR Integration Best Practices

• Nauzad Kapadia MVP
• nauzadk_at_quartzsystems.com

2
Design Guidelines

• T-SQL is best suited for data access
• Familiar, simple programming model
• Optimized for data access
• Managed code is all about procedural programming
  and computation
• IL compiled to x86 code at runtime, easily
  outperforms interpreted T-SQL
• Compute-intensive business logic encapsulated as
  functions

3
TSQL Versus C For LogicConverting Binary To Hex
 
4
TSQL Versus C For Data Access
  
5
Usage Guidance Mid-tier vs. server

• CLR support does not mean move all business logic
  to server
• Traditional tradeoffs still apply
• reduced network data movement and round trips vs.
  added load on database server
• CLR makes language choice not be a factor in
  deciding code location
• Candidate for moving to server
• Frequent round-trips
• Process large amount of data while needing a
  small portion of it for application use

6
Usage guidance CLR as replacement for XPs

• Benefits
• Easier in-proc data access No need to use
  loop-back connections and binding sessions
• Better administrative control 3 permission
  buckets.
• Reliable and scalable
• Faster
• Caveat Reduced performance compared to XPs for
  scenarios involving frequent managed-native
  transitions

7
What is a Host and how does it work.
PE Header
Unmanaged Stub
Metadata
Common Language Runtime
8
A matter of control

• SQL Server must control .NET CLR resources
• to meet security and reliability goals
• needs version of runtime (.NET 2.0) that allows
  host control
• not loaded if no runtime features used
• runtime footprint approximately 10-30 meg

9
Specifying runtime behavior

• Runtime loaded by via COM interface
• ICorRuntimeHost
• ICLRRuntimeHost (only in .net 2.0)
• Behaviors specified as parameters
• server or workstation DLL (pre-2.0)
• version of the CLR (e.g. version 2.0)
• garbage collection behavior
• whether or not jitted code may be shared across
  AppDomains

10
ICLRRuntimeHost

• .NET 2.0 allows control by using ICLRRuntimeHost
• ICLRRuntimeHostSetHostControl hooks up
  IHostControl
• IHostControlGetHostManager returns vector of
  pointers
• allows host to usurp runtime control points
• GetHostManager allows control of
• resource allocation
• exceptional conditions
• code loading
• security particulars

11
Resource allocation

• SQL Server manages its own memory
• always runs close to max - more chances for
  malloc to fail
• control points let SQL Server manage
• memory allocation
• I/O completion ports
• all FX libraries clean up unmanaged resources on
• thread abort
• AppDomain unload

12
Thread management

• SQL Server and CLR threading models are different
• SQL Server uses non-preemptive execution
• threads or fibers
• CLR uses preemptive execution
• allocates thread pools
• new architecture uses tasks to allow switching
• SQL Server "blocking points" allow transition
  between modes

13
Exceptional conditions

• Reliability considerations dictate
• abnormal conditions can't bring down SQL Server
• SQL Server can't leak resources, transactions
• SQL Server can't leak memory

14
Usage guidanceUDTs and UDAggs

• Use UDTs for small types with little or no
  structure
• E.g. UTF8String, Zipcode, DateWithTimeZone,
  DateWithCalendar
• Not intended for object persistence, complex
  structured types
• Use user-defined aggregates to replace cursor
  navigation with single query for custom
  aggregations

15
Typical usage scenarios

• Data validation
• Use scalar functions and CHECK constraints
  instead of client-side validation or
  multi-statement logic in triggers
• E.g. String validation using Regular Expressions
• Benefits
• Centralized validation
• Easy to write, efficient