数据库性能优化

1、技术支持中心目的目的 保证系统的高效运行 日常工作符合高效要求（数据库维护、程序开发） 如何初步解决性能问题对象对象熟悉Win Server及SQL Server，有操作经验时间时间1.5小时产生系统性能的原因产生系统性能的原因系统优化办法系统优化办法数据库系统性能调优数据库系统性能调优数据库性能调优（数据库设计）数据库性能调优（数据库设计）程序调优（程序调优（SQL语言）语言）如何解决性能问题如何解决性能问题网络问题：网络速度慢硬件问题 Cpu 内存：内存不足内存：内存不足 硬盘：硬盘： I/O吞吐量小，形成了瓶颈效应吞吐量小，形成了瓶颈效应操作系统&数据库系统应用程序（程序设计、数据库设计

2、、SQL语句） 没有索引或者没有用到索引没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺这是查询慢最常见的问题，是程序设计的缺陷陷) 查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 锁或者死锁锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺这也是查询慢最常见的问题，是程序设计的缺陷陷)sp_lock,sp_who,活动的用户查看活动的用户查看,原因是读写竞争资源。原因是读写竞争资源。 返回了不必要的行和列返回了不必要的行和列 查询语句不好，没有优化查询语句不好，没有优化 没有创建计算列导致查询不优化没

3、有创建计算列导致查询不优化 可调整的部分可调整的部分:网络硬件操作系统数据库应用程序应用程序应用程序数据库数据库操作系统操作系统硬件硬件网络网络 为什么需要讲系统的设置？为什么需要讲系统的设置？ 微小的差异可能照成系统性能的巨大差别 内容内容 操作系统安装设置 数据库参数设置回想回想10秒钟秒钟 启用网络应用模式为启用网络应用模式为“最大化网络应用程最大化网络应用程序数据量吞吐量序数据量吞吐量” 避免讲系统避免讲系统pagefile.sys与与ms sql server档案放在同一硬盘上档案放在同一硬盘上 文件系统选择文件系统选择 安全与性能考虑：NTFS 绝对不要使用压缩 开关不必要的服务开

4、关不必要的服务 IIS Web Server,SMTP Virtual Server 不要与大量网络流量应用服务期安装在同一机器中 主域控制器、Exchange Server、MSMQ、FTP Server、Print Server、MTS 通讯协议设定通讯协议设定 仅安装需要用到的协议，多余的会拖跨系统 内存设置内存设置:由由SQL Server自动调整自动调整 考虑使用考虑使用“为为SQL Server保留物理内保留物理内存存” 考虑使用考虑使用“在在Windows上提升上提升SQL Server的优先级的优先级” 预设值是 0，也就是优先权7。 启动后 SQL Server 将在 Mic

5、rosoft Windows NT 4.0 或 Windows 2000 排程器中以优先权13 执行。 专为执行 SQL Server 的 Server 适用 考虑使用考虑使用 “使用查询使用查询调控防止超出成本的调控防止超出成本的查询查询” 使用成本查询管理者，做为防止查询超过指定成本的选项。End 数据库设计目标：数据库设计目标： 以最有效率的方法将资料存放在硬盘上：成本上的考虑 最短回应时间来存储与取得资料：高效率的考率 如何达到这两个要求呢？如何达到这两个要求呢？ 办法一：正规化办法一：正规化 特性 去除重复不必要的资料（成本最低） 资料的完整性易易于维护 缺点： 资料分散到数个表格上

6、，存取時必須进行关联（Join） 关联（Join）会造成效能的下降，必要时进行适当的容余 办法二：数据分割办法二：数据分割 水平分割：每個子表的结构皆相同，但存放不同的资料內容 例如：有一個包含一亿条记录的资料表，可以滤依其特性分成数个小的表231ABCDEF水平分割水平分割2345671ABCDEF记录过多时记录过多时564ABCDEF 垂直分割：以表垂直分割：以表数据列数据列做切做切割割成成为数个为数个小表格，以減少表小表格，以減少表格格宽宽度，降低度，降低I/O存取次存取次数数 故当表越宽时，其所占用的页（Data Page）会更多，将使I/O存取次数大增 例如：包含有过多列的表，可以考

7、虑将不常用的列存到第二张表中。2345671ABE垂直分割垂直分割2345671CDF2345671ABCDEF过多的列过多的列End 数据的存储方式数据的存储方式 数据页 数据盘区:1 Extent = 8 Pages 数据的访问方式数据的访问方式 表扫描(Table Scan)：速度很慢，和文件差别不大 Indexes are B-Trees Structures Which Allow Fast Access to Data 建立适当的索引可以減少I/O存取的次数AAA E IJ K LMJMM N Q 聚集聚集（Clustered）Index 资料已经做过物理排序，适合大量資料的处理

8、每一個资料表只能有一個聚集索引 非非聚集聚集（Non-Clustered）Index 不做物理排序，所以在一张表中可以有250个 适用与资料密度低的列，也就是 WHERE 条件时具有高选择性（SELECTIVITY） 复复合（合（Composited） Index 所要用到的列都已经包含在索引页（Index Page）中，故不需要去到物理的资料页（Data Page）一笔一笔去找 为特定查詢命令量身定作用 特性：特性： 索引可以迅速的找到需要的记录 建立适当的索引可以減少I/O存取的次数 正确的索引设计对性能的影响至管重要正确的索引设计对性能的影响至管重要 索引需要另外的管理开销索引需要另外的

9、管理开销 管理的成本 存储的成本 增加了数据插入/修改的成本 沒有沒有适当适当可用的索引時可用的索引時 如：在具有大量重复数据的列上创建索引没有价值 采用表扫描方式（Table Scan） 索引存在，SQL Server也不一定使用 结论结论 索引不是越多越好，一般一张表不要超过5个 不要在不合适的列上建立索引 一句话：在合适的列上建立合适的索引 建立索引的原则建立索引的原则 PK/FK可以建立索引 有大量非重复值、且经常有范围查询（between, ,=, =）和order by、group by发生的列，可考虑建立群集索引 经常进行范围查找，排序的列适合建立索引 基数较大的列很适合用来做索

10、引 避免添加与已有的索引相似的索引，考虑建立复合索引 复合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列 不需要建立索引的列不需要建立索引的列 不常使用的列 包含大量重复数据的列 bit, text, image列上不能被建立索引 具有null值的列End DBCC CheckDB 在系统使用率较低时运行 CHECKDB 一时检查一张表 确保未同时执行其它磁盘 I/O 操作，例如磁盘备份 避免运行占用大量 CPU 的查询或批处理作业 详细使用方法可参考SQL Server联机文档 DBCC CHECKTABLE DROP_EXISTING option saves work A

11、LTER TABLE isnt needed to remove constraints Nonclustered indexes dont need to be rebuilt twice DBCC DBREINDEX 索引重建 例：DBCC DBREINDEX (zy_fymx,0) DBCC INDEXDEFRAG 碎片整理 例：DBCC INDEXDEFRAG (0,zy_fymx,1)1,25,63,41,2,34,5,6 UPDATE STATISTICS对一个或多个统计组（集合）有关键值分发的信息进行更新 減少网络使用，避免商业逻辑的运作需要多次的网路来回往返呼叫，以及大量的资料

12、在网路上的传输 事物事物（Transaction）保持短小，避免）保持短小，避免Long Transaction 或是或是与与使用者互使用者互动，造动，造成死锁成死锁End 简单监视简单监视:任务管理器任务管理器 系统监视系统监视器（器（System Monitor） 监控Server I/O 内存使用 使用者连接 锁定（Lock） 复写（Replication）的活動 SQL Profiler 取得最详细的资料，查知每位使用者的活动 请利用不同机器來监控 SQL Server物件物件計數器計數器指標指標Memory： Available KByres這個計數器應該要大於這個計數器應該要大於

13、5,000 KB，當這個值，當這個值小於小於 5,000 KB 時，沒有其他的記憶體可以讓時，沒有其他的記憶體可以讓 SQL Server 使用來作最佳化執行的動作。使用來作最佳化執行的動作。Memory： Page/sec這個數值不應該持續地大於零，若持續地大這個數值不應該持續地大於零，若持續地大於零表示於零表示 Windows 作業系統一直在用作業系統一直在用 paging file 來完成記憶體的需求來完成記憶體的需求Page Faults/sec 可確認磁碟活動並非分頁可確認磁碟活動並非分頁 (Page) 所造成。所造成。1. 處理序處理序 (Process) 設定使用太多記憶體。設定

14、使用太多記憶體。2. 檔案系統檔案系統 (File System) 活動。活動。 性能对象性能对象计数计数器器指指标标Processor：% Processor Time(指处理器执行非闲置线程指处理器执行非闲置线程时间的百分比时间的百分比)這個計數器應該小於百分之九這個計數器應該小於百分之九十。如果計數器等於百分之百十。如果計數器等於百分之百就必須減低工作量，以增加工就必須減低工作量，以增加工作的執行效率，或是增加處理作的執行效率，或是增加處理器的數目或能力器的數目或能力System： Processor Queue Length(指处理列队中的线程数指处理列队中的线程数)如果处理器列队中总

15、是有两个如果处理器列队中总是有两个以上的线程以上的线程 通常表示处理器通常表示处理器堵塞。堵塞。若果若果持续持续地大地大于于 2 表示表示要減少工作量，以增加工作的要減少工作量，以增加工作的执执行效率，或是增加行效率，或是增加处处理器的理器的数数目或能力。目或能力。性能对象性能对象计数计数器器指指标标PhysicalDisk： % Disk Time(所选磁盘驱动器忙于为读所选磁盘驱动器忙于为读或写入请求提供服务所用的时间或写入请求提供服务所用的时间的百分比的百分比)這這个计数个计数器器应该要持续地应该要持续地小小于于百分之九百分之九十十 PhysicalDisk: Avg. Disk Que

16、ue Length （读取和写入请读取和写入请求求(为所选磁盘在实例间隔中列队为所选磁盘在实例间隔中列队的的)的平均数）的平均数）等候的等候的 I/O 要求要求数数不不应持续应持续超超过实际硬过实际硬盘盘所包含所包含读写头数读写头数的的 1.5 到到 2 倍。大多倍。大多数数的磁的磁盘盘都有一都有一个读写头个读写头，而通常，而通常磁盘磁盘阵列阵列(RAID) 裝置裝置则则有多有多个读写头个读写头。 PhysicalDisk: Disk Reads/sec 取決取決与制造商提供的规格与制造商提供的规格 PhysicalDisk: Disk Writes/sec 取決取決与制造商提供的规格与制造商提供的规格允许系统管理员监视允许系统管理员监视SQL Server执行个体执行个体中的事件的中的事件的图图形化工