Transcript 自动内存管理 - Microsoft

DAT321
SQLCLR--在SQL Server 2005中运
行.NET程序
邓英达
[email protected]
产品技术部
微软(中国)有限公司
内容列表
第一部分：概念介绍
第二部分：技术架构+实战演示
第三部分：监控与查错
Common Language Runtime概述
CLR 是 C#, VB.NET等语言的运行环境
代码编译为中间语言层 Intermediate
Language (IL)
自动内存管理 (GC)
通用数据类型
代码检验
代码访问安全
丰富的类库支持
应用程序域 – 隔离单元
支持程序调试
.NET Framework 和 CLR
CLR 执行模型
源代码
管理代码
Managed
code
VB
C#
C++
Compiler
Compiler
Compiler
Assembly
IL Code
Assembly
IL Code
Assembly
IL Code
公共语言运行时
即时编译器JIT Compiler
二进制代码
操作系统服务
Unmanaged
Component
在SQL2005中运行CLR
VB, C#, …
Runtime
hosted inside
SQL
SQL Queries:
select
sum(tax(sal,state) )
from Emp where county
= ‘King’
VS 2005
Project
Build
Assembly:
“TaxLib.dll”
deploy
SQL Data Definition:
create assembly …
create function …
create procedure …
create trigger …
create type …
SQL Server
内容列表
第一部分：概念介绍
第二部分：技术架构+实战演示
第三部分：监控与查错
SQL CLR创建与调用
在SQL中Hosting CLR
SQL Server
Process
CLR
Hosting
Layer
SQL OS
Windows
CLR通过SQL Server进行:
Memory
Threads
Synchronization
SQL和CLR协作完成：
Assembly Loading
Security management
App domains
Escalation policy for exceptions
技术架构
内存管理
程序集管理
应用程序域管理
CPU管理: 线程、同步、死锁等
安全管理
内存管理
All memory allocation from CLR through SQL Server
Most of CLR memory (GC Heap) comes from multipage memory allocator (outside Buffer-pool, from
“MemToLeave”)
Max server memory setting does not cover CLR memory
On memory pressure, SQL and CLR coordinate in
order to initiate a GC
DMVs for monitoring CLR memory usage
Sys.dm_os_memory_clerks
Sys.dm_os_memory_objects
Memory clerks of interest: SQLCLR,
SQLCLRASSEMBLY
Perf counters: everything under .NET CLR Memory
程序集管理
SQL Server sets a pre-defined list of FX
assemblies that can be referenced in user
assemblies.
This ‘blessed’ list is set by SQL Server at the
time of initializing CLR
CLR automatically loads these from GAC
These assemblies underwent heavy reliability
work to meet the server reliability bar
Certain restrictions apply in these assemblies
(e.g. cannot do Process.Exit)
All other assembly load requests come to
SQL Server.
All user assemblies required by a query
should be present in the database.
程序集管理
Blessed Assemblies
Microsoft.VisualBasic
Mscorlib
System
System.Data
System.Data.SqlXml
System.Data.OracleClient
System.Transactions
System.Configuration
System.Xml
System.Security
System.Web.Services
Microsoft.VisualC
CustomMarshalers
应用程序域管理
App domains: CLR mechanism for isolating code
App domains are created and managed by SQL Server
Appropriate security is defined at creation
Failure behavior is controlled by SQL Server
Granularity:
One per assembly owner in each database
All assemblies owned by same user are in same app domain
SQL process
CLR
AppDomain 1
User a
Database A
AppDomain 2
User b
AppDomain 3
Database B
CPU管理: 线程, 同步
All managed “threads” are mapped to tasks managed by the
SQL Server scheduler
Co-operative scheduling
Exception: GC threads
SQL Scheduler detects non-yielding tasks (managed or T-SQL)
and “punishes” them
Force the task to stop, put back in the queue and miss some turns
Callback to SQL Server on P/Invoke (when calling native code)
Switched to pre-emptive mode
CLR lock requests go through SQL Server
Unified deadlock detection between locks held by CLR and native
SQL
安全管理
App-domain policy level on top of enterprise/machine/userlevels of policy
Net permissions given to code: intersection of all policy levels
SQL Server Policy level:
Maps user/system assemblies to corresponding permission sets
User assemblies given one of 3 permission sets based on
user-specification
Different SQL Permissions required for creating CLR
permission set
SAFE: CREATE ASSEMBLY (db level)
EA: CREATE ASSEMBLY (db level), EXTERNAL ACCESS
(login level)
UNSAFE: Sysadmin only
权限集介绍
SAFE
EXTERNAL_ACCESS
UNSAFE
CAS permissions
Execute
only
Execute+ access
external resources
Unrestricted
Programming model
restrictions (no
statics, no HPAed
attributes)
Yes
Yes
No
Local data access
Yes
Yes
Yes
Verifiability required
Yes
Yes
No
外部资源操作与运行权限
SQL CLR与T-SQL的区别
内容列表
第一部分：概念介绍
第二部分：技术架构+实战演示
第三部分：监控与查错
监控与查错
Profiler and Performance Counters
Profiler trace events
CLR:Assembly Load – monitor assembly
load requests (success and failures)
SQL:BatchStarting, BatchCompleted
SP:Starting, Completed, StmtStarting,
StmtCompleted – monitor execution of
T-SQL and CLR routines
Performance Counters
SQL Server:CLR-CLR Execution – Total
time spent in CLR execution
.NET CLR Memory
监控与查错
DMVs and Catalog views
sys.assembly*
sys.dm_os_memory_clerks
sys.dm_clr*
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_cached_plans
参考网站
“Using CLR Integration” white paper:
http://msdn.microsoft.com/library/en-us/dnsql90/html/sqlclrguidance.asp
SQL Server 2005 books online
Stay tuned for more whitepapers on internals and
manageability
Send us your feedback: [email protected]
Your Feedback
is Important!