1 / 26

DAT321 SQLCLR-- 在 SQL Server 2005 中运行 .NET 程序

DAT321 SQLCLR-- 在 SQL Server 2005 中运行 .NET 程序. 邓英达 yindeng@microsoft.com 产品技术部 微软 ( 中国 ) 有限公司. 内容列表. 第一部分：概念介绍 第二部分：技术架构 + 实战演示 第三部分：监控与查错. Common Language Runtime 概述. CLR 是 C#, VB.NET 等语言的运行环境 代码编译为中间语言层 Intermediate Language (IL) 自动内存管理 (GC) 通用数据类型 代码检验 代码访问安全 丰富的类库支持

stephen-ochoa
Download Presentation

DAT321 SQLCLR-- 在 SQL Server 2005 中运行 .NET 程序

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DAT321SQLCLR--在SQL Server 2005中运行.NET程序 邓英达 yindeng@microsoft.com 产品技术部 微软(中国)有限公司

  2. 内容列表 • 第一部分：概念介绍 • 第二部分：技术架构+实战演示 • 第三部分：监控与查错

  3. Common Language Runtime概述 • CLR 是 C#, VB.NET等语言的运行环境 • 代码编译为中间语言层 Intermediate Language (IL) • 自动内存管理 (GC) • 通用数据类型 • 代码检验 • 代码访问安全 • 丰富的类库支持 • 应用程序域 – 隔离单元 • 支持程序调试

  4. .NET Framework 和 CLR CLR 执行模型 源代码 VB C# C++ Unmanaged Component Compiler Compiler Compiler 管理代码 Managed code Assembly IL Code Assembly IL Code Assembly IL Code 公共语言运行时 即时编译器JIT Compiler 二进制代码 操作系统服务

  5. VB, C#, … Assembly: “TaxLib.dll” SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Queries: select sum(tax(sal,state) ) from Emp where county = ‘King’ SQL Server 在SQL2005中运行CLR Build VS 2005 Project deploy Runtime hosted inside SQL

  6. 内容列表 • 第一部分：概念介绍 • 第二部分：技术架构+实战演示 • 第三部分：监控与查错

  7. SQL CLR创建与调用

  8. CLR通过SQL Server进行: Memory Threads Synchronization SQL和CLR协作完成： Assembly Loading Security management App domains Escalation policy for exceptions 在SQL中Hosting CLR SQL Server Process CLR Hosting Layer SQL OS Windows

  9. 技术架构 • 内存管理 • 程序集管理 • 应用程序域管理 • CPU管理: 线程、同步、死锁等 • 安全管理

  10. 内存管理 • All memory allocation from CLR through SQL Server • Most of CLR memory (GC Heap) comes from multi-page 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

  11. 程序集管理 • 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.

  12. 程序集管理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

  13. Database A Database B 应用程序域管理 • 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 User a SQL process CLR AppDomain 1 AppDomain 2 User b AppDomain 3

  14. 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

  15. 安全管理 • App-domain policy level on top of enterprise/machine/user-levels 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

  16. 权限集介绍

  17. 外部资源操作与运行权限

  18. SQL CLR与T-SQL的区别

  19. 内容列表 • 第一部分：概念介绍 • 第二部分：技术架构+实战演示 • 第三部分：监控与查错

  20. 监控与查错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

  21. 监控与查错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

  22. “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: sqlclrpm@microsoft.com 参考网站

  23. Your Feedbackis Important!

More Related