Performance Tuning for SQL Server 2005 Solutions

1. Performance Tuning for SQL Server 2005 Solutions Ryan Chen ryan.chen@emc.com Solution Architect – Microsoft EMC Global Service – MS Practice

2. 议程 • 关于EMC微软实践部门 • SQL Server 2005的架构 • 性能调优的方法学 • 如何对基于SQL Server 2005的OLTP系统进行调优 • 如何对基于SQL Server 2005的OLAP系统进行调优 • 如何对基于SQL Server 2005的ETL解决方案进行调优

3. 关于EMC微软实践部门 • EMC MS PracticeEMC微软实践部门 • EMCMSP针对SQL Server的服务 • EMC MSP针对其他微软产品的服务

4. R2 EMC MS PracticeEMC微软实践部门 • 在北美市场占据主导地位 • 全面进入亚太及中东市场 • 提供经验丰富的领域专家 • 服务各种规模的行业客户 EMC微软实践提供业务驱动的IT咨询服务，整合Microsoft及相关技术。 经验 权威 影响 认证 微软全球5大服务提供商之一 • 先进的基础架构 • 业务流程及整合 • 数据管理 (BI) – 未决 • 微软商务解决方案 (CRM) • 信息工作者 • 定制开发 • 安全 • 独立软件供应商/软件 TAP及早期部署 8 PAC Operations Manager 2007 合作伙伴咨询委员会成员

5. EMCMSP针对SQL Server的服务 • SQL Server平台的设计及迁移规划及实施 • SQL Server数据应用架构规划及建议 • SQL Server高可用及数据保护方案规划及设计 • SQL Server性能故障分析及排除 • SQL Server平台商务智能解决方案的规划及实施

6. EMC MSP针对其他微软产品的服务 • Notes到Exchange的迁移 • Exchange的升级规划及实施 • Exchange的容灾及备份规划及实施 • SharePoint的应用设计及开发 • 桌面及服务器的部署规划及实施 • 安全架构的规划及实施 • .Net应用的设计与开发 • …… 更多关于EMC MSP的问题可以访问http://www.emc.com/msp 或直接联系ryan.chen@emc.com

7. SQL Server 2005的架构

8. 性能调优的方法学 硬件配置 内存 处理器亲和度 存储设计 文件组 分区 数据架构 索引 表 查询代码 存储过程 视图 调优顺序 最困难 但最有成效 最简单 但是收效最少

9. 如何对基于SQL Server 2005的OLTP系统进行调优 • OLTP系统的特点 • 常见的性能问题 • 如何设计良好的关系型数据库架构 • 如何编写良好的T-SQL代码 • 如何优化SQL Server的配置

10. OLTP系统的特点 • 事务处理逻辑比较简单 • 单个事务要求响应速度快 • 并发更新事务多

11. 常见的性能问题 常规情况下 磁盘负载过重 架构不良 调整架构 内存负载过重 代码不良 调整代码 处理器负载过重 代码不良 调整存储 网络负载过重 客户端代码不良 调整客户端设计

12. 如何设计良好的关系型数据库架构 • 对数据热区的判断 • 根据数据热区定义索引、表分割定义 • 优化SELECT查询 • 尽量将数据存储在同一张表中 • 使用索引及索引覆盖策略 • 优化UPDATE事务 • 尽量将需要更新的数据放在一张较小的表中 • 优化DELETE事务 • 在大规模删除中评估分区的效果 • 优化INSERT事务 • 减少对自动编号的依赖

13. 如何编写良好的T-SQL代码 • 尽量使用基于Set的逻辑，减少使用基于Row的逻辑 • 减少游标的使用 • 对于小数据集使用表变量而不是临时表 • 对于需要反复使用的大数据集使用临时表而不是表变量 • 尽量使用WHERE替代HAVING

14. 如何优化SQL Server的配置 • 对于中高端服务器 • 在Boot.ini文件中启用/3gb参数以及/pae参数 • 在SQL Server配置中启用AWE • 在SQL Server中配置CPU Affinity以及Parallel Cost Threshold • 对于所有服务器 • 配置tempdb

15. 如何对基于SQL Server 2005的OLAP系统进行调优 • 优化Dimension的设计 • 优化Measure Group的设计 • 优化MDX语句 • 优化SQL Server Analysis Service的配置

16. 优化Dimension的设计 • 利用Attribute Hierarchy减少Dimension的数量 • 禁用不需要的Attribute • 禁用不常用的Attribute聚合及Attribute Hierarchy • 设置合理的Attribute Relationship

17. 优化Measure Group的设计 • 对于简单的可计算度量使用Calculated Member • 尽量避免MeasureExpression • 使用Cube分区

18. 优化MDX语句 • 限制Calculation的计算范围 • 移除空的元组 • 需要自定义返回值格式时，使用Format String属性来替代使用条件逻辑 • 当需要Cross Join的时候，将较小的Set放在表达式的右侧 • 需要比较的时候，尽量使用IS运算符而不是=

19. 优化Analysis ServiceSolution的配置 • 设置Process时候的Temp Dir • 在生产环境关闭Flight Log • 选用NativeProvider读取数据源 • 选用Aggregation Designer设计聚合方案 • 生产环境中使用Usage Based Aggregation Design优化聚合方案

20. 如何对基于SQL Server 2005的ETL解决方案进行调优 • 理解SSIS两种引擎的差异 • Pipeline的工作机制 • SSIS的执行计划 • 执行计划对性能的影响

21. 理解SSIS两种引擎的差异 • Run time引擎 • 依赖于外部资源 • 例如：网络带宽(FTP task)，应用服务器的性能(Mail task、Execute SQL task) • Data flow引擎 • 同样依赖于外部资源(当需要访问数据源和数据目的的时候) • 同时依赖于内部资源(执行数据转换操作的时候) • 基于内存管道pipeline缓存管理机制

22. Pipeline的工作机制

23. SSIS的执行计划

24. 执行计划对性能的影响

25. SSIS性能调优的建议 • 减少读入数据缓存的数据行尺寸 • 最大化线程并行度 • 使用条件性拆分处理聚合运算 • 最大化缓存的利用率 • 利用已排序的数据源定义来避免排序转换