SQLServer性能优化

1、SQL Server性能优化性能优化 华测检测华测检测 SQL Server SQL Server 基础知基础知识回顾识回顾 SQL Server SQL Server 性能优化方法和工具性能优化方法和工具01010202课时安排课时安排 华测检测华测检测你在工作中是否也常常遇到这样的问题：你在工作中是否也常常遇到这样的问题：客户给你抱怨系统很卡很慢。当你进一步客户给你抱怨系统很卡很慢。当你进一步询问慢到什么程度，有什么表征时，得到询问慢到什么程度，有什么表征时，得到的答案往往又很模糊的答案往往又很模糊.你会从哪些方面去排查问题？你会从哪些方面去排查问题？ 华测检测华测检测应用程序中间层出现性

2、能瓶颈；处理事务、工作流的逻辑不合理；会话机制和缓存机制配置不合理逻辑算法不合理；存储过程、函数效率低下维护性差；游标、触发器使用不合理应用程序运行在与工作负荷不匹配的硬件上；硬件资源分配利用不合理CPU相关配置没有配置好；内存相关配置没有设置好 华测检测华测检测0 1SQL Server SQL Server 基础知识回基础知识回顾顾 华测检测华测检测前言前言 性能优化顺序性能优化顺序性能最优性能最优内存、处理器亲和度内存、处理器亲和度文件组、分区文件组、分区覆盖查询、锁、事务覆盖查询、锁、事务表、过程存储、视图表、过程存储、视图服务器优化服务器优化存储优化存储优化索引优化、并发控制索引优化

3、、并发控制架构设计、查询优化架构设计、查询优化 华测检测华测检测01 表与索引的物理存储结构表与索引的物理存储结构 了解表和索引的存储结构对于SQL优化，是基础，不可跨越的。不了解存储结构，知道再多的查询优化策略也无用，因为始终会知其然不知其所以然！ 华测检测华测检测01 表的物理存储结构表的物理存储结构 在在SQL SERVERSQL SERVER中中，存储数据的基本，存储数据的基本单位是页单位是页(Page)(Page)每个页上存放几十条或几百条记录。多个页组成一个表。每个页大小固定是8K(SQL SERVER)，即8192个字节.数据是一行行存在页上，而不是直接存在表中的。页是存储数据的

4、基本单位。. 华测检测华测检测01 表的物理存储结构表的物理存储结构-页构造页构造 页首页首 数据区数据区每页的开始部分是 96 字节的页首，用作SQL中的管理字节，管理字节中记录着本页的编号、上一页和下一页的编号等（还有其他信息）。页首下面紧跟着放记录，每条数据一行，数据区存放多行数据。 华测检测华测检测01 表的物理存储结构表的物理存储结构-页构造示图页构造示图 96 96字节页字节页首首 数据数据区区 华测检测华测检测01 表的物理存储结构表的物理存储结构-页链页链 SQL 通过页上的编号把所有的页串成一个链。 这个链组成了一张表。 SQL通过页链依次找到表的所有页和所有记录。 华测检测

5、华测检测01 表的物理存储结构表的物理存储结构-页链示意图页链示意图上一页上一页下一页下一页当前页当前页上一页号下一页号当前页页号数据区数据区通过通过”上一页上一页”和和”下一页下一页”串起来串起来 华测检测华测检测01 表的物理存储结构表的物理存储结构-表示意图表示意图页链页链页页页页页页页页记录记录组成了一张表组成了一张表 华测检测华测检测01 表的物理存储结构表的物理存储结构-表存储结构表存储结构每个页上最大存储819296=8096个字节的数据。如：表的每条记录的长度是1000字节，则每个页可存放8条记录。每条记录的长度不可大于8096个字节，超过存储限制，则无法存储数据。记录不可跨页

6、。就是说，假设一条记录长度为5000个字节的话，那么一页上只能存放一条记录，而页上的剩余的3096个字节就浪费了，且没办法回收利用。 华测检测华测检测01索引的物理存储结构索引的物理存储结构索引的存储结构和表差不多。也是存储在页上的。只是表中存储的字段多，而索引中存的字段非常少而已。所以索引一般比表小很多。索引是一种结构，索引和表（这里指的是加了聚集索引的表）的存储结构是一样的，都是B树，B树是一种用于查找平衡多叉树。 华测检测华测检测01索引的物理存储结构索引的物理存储结构-B树结构图树结构图 华测检测华测检测01索引的物理存储结构索引的物理存储结构-索引层的计算方法索引层的计算方法 假设每

7、页放100条索引，一个1000万笔记录的大表，那么叶级索引约有10万页，二级索引有1000页，三级索引10页，根索引1页，每一级都是上一级的1/100。这个表的索引共有4层。 华测检测华测检测01索引的物理存储结构索引的物理存储结构-根据索引找记录根据索引找记录用户要查工号是655的员工的信息工号 指针501 第5页601 第6页701 第7页.根索引1. 1. 扫描根页，工号扫描根页，工号655655介于第介于第6 6页页和第和第7 7页之间，第页之间，第7 7页的第页的第1 1行大于行大于655655，那么去在中间级搜索第，那么去在中间级搜索第6 6页页。工号 指针641 第41页651

8、第42页661 第43页中间索引2. 2. 扫描中间级页，工号扫描中间级页，工号655655介于介于第第4242页和第页和第4343页之间，接着去下级页之间，接着去下级搜索第搜索第4242页。页。工号 指针654 655 656 叶级索引3. 扫描第42页，查找到工号655，根据指针定位到所在的数据页，取得数据。 华测检测华测检测01索引的物理存储结构索引的物理存储结构-根据索引找记录根据索引找记录由上我们知道：SQL根据索引查找记录时，只需要对索引中的每一级扫描一页，最后再扫描一个数据页就可以了。索引字段一般都很短，每个页上可能会存放几百条索引记录。假设1000万笔记录、一个2G的大表，每页

9、放100条索引，根据索引查找记录，只需扫描5个页，4个索引页+1个数据页，5个页是多少？硬盘要做多少动作？5*8K=40K而已，不论记录是在表的任何一个地方：前面、后面或中间。如果对一个只有200条记录的表建索引，是没有什么效率的，因为表的数据一共可能只有一两页。使用了索引不会有任何速度提升。 华测检测华测检测01索引的物理存储结构索引的物理存储结构-索引分类索引分类 数据表本身就是索引的一部分，数据表本身就是聚集索引的子叶层，整个数据表的摆放顺序是按照你选定的键值由小到大排序聚集索引聚集索引非聚集索引非聚集索引 完全独立于数据表之外的结构,叶级页指向表中的记录,记录的物理顺序与逻辑顺序没有必

10、然的联系。 华测检测华测检测01索引的物理存储结构索引的物理存储结构-索引使用规则索引使用规则每个表只能有一个聚集索引每个表最多可以有 249个非聚集索引在非聚集索引创建之前创建聚集索引若未指定索引类型，则默认为非聚集索引 华测检测华测检测01索引的物理存储结构索引的物理存储结构-非聚集索引使用场合非聚集索引使用场合频繁的从一个大表中选择很少量的数据的情况。频繁的从一个大表中选择很少量的数据的情况。重复值非常少的时候，如物料编号、姓名。如果有重复值非常少的时候，如物料编号、姓名。如果有很多的重复值，如只有很多的重复值，如只有 男男 和和 女，大多数查询将女，大多数查询将不使用索引，因为此时表扫描通常更有效！不使用索引，因为此时表扫描通常更有效！经常用于和其他表做关联的字段。经常用于和其他表做关联的字段。 华测检测华测检测经常返回一个范围的查询：经常返回一个范围的查询：BETWEENBETWEEN、 、 。经常被连续访问的列、返回大型结果集的查询。经常被连续访问的列、返回大型结果集的查询。经常被使用联接或经常被使用联接或 GROUP B