SQL2008系统性能优化解决方案

SQL Server

系统性能调优解决方案

前言

近几年,医药流通市场经历了激烈的震荡,导致行业逐步成熟和企业的快速变革,差异化经营成为众多医药流通的竞争选择。时空产品在中国医药流通企业的发展过程中得到了广泛且深入应用,大量的客户化开发和定制支撑了企业管理中横向和纵向的变化，很好的适应了企业在发展过程中不断变化的需求。

对于数据库管理系统的使用，很多用户都面临着一个很棘手的问题：系统效率下降。产生效率下降的因素是多方面：

1.硬件问题

2.软件问题

3.实施问题

正因为产生效率下降的因素很多，所以如何去查找原因成为我们首要关注的问题，时空公司也处在积极探索过程中。时空公司在解决一些客户问题的过程中积累了一些方法和思路，归纳总结后呈现给体系内的技术人员，本方案就系统效率调整所必需的基础知识、方法、技巧等几个方面进行阐述，从而让技术人员能够快速定位问题，解决问题，为合作伙伴提供优质，快捷的服务。

索引简介

索引是根据数据库表中一个或多个列的值进行排序的结构。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似，通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。

索引键：用于创建索引的列。

索引类型

聚集索引：

聚集索引基于数据行的键值在表内排序和存储这些数据行。由于数据行按基于聚集索引键的排序次序存储，因此聚集索引对查找行很有效。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。数据行本身构成聚集索引的最低级别（叶子节点）。只有当表包含聚集索引时，表内的数据行才按排序次序存储。如果表没有聚集索引，则其数据行按堆集方式存储。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如：如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

叶节点（包括数据）

非聚集索引

非聚集索引具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储,非聚集索引可以建多个。

叶节点（指向数据）

唯一索引

唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。唯一索引既是索引也是约束。

复合索引

索引项是多个的就叫组合索引，也叫复合索引。复合索引使用时需要注意索引项的次序。索引对性能的作用

使用索引的优点

1.通过唯一性索引（unique）可确保数据的唯一性

2.加快数据的检索速度

3.加快表之间的连接

4.减少分组和排序的时间

使用索引的原则

1.在需要经常搜索的列上创建索引

2.经常用于连接的列上创建索引

3.经常需要根据范围进行搜索的列上创建索引

4.经常需要排序的列上创建索引

5.经常用于where子句的列上创建索引

不使用索引的原则

1.查询很少使用和参考的列不建索引

2.对只有少数值的列不建索引

3.定义为text、image、bit的列不建索引

4.当需要update性能远远高于select性能时不建或少建索引

常用命令

1.sp_helpindex : 报告表或视图上的索引信息

2.dbcc showcontig :显示指定表的数据和索引的碎片信息

3.dbcc dbreindex :重建指定数据库中一个或多个索引

4.dbcc indexdefrag :整理指定表或视图的聚集索引或辅助索引的碎片

创建索引

1.定义索引时，可以指定每列的数据是按升序还是降序存储。如果不指定，则默认为升

序

2.为索引指定填充因子，可标识填充因子来指定每个索引页的填满程度。索引页上的空余空

间量很重要，因为当索引页填满时，系统必须花时间拆分它以便为新行腾出空间。

优化索引

1.重建索引（dbcc dbreindex）

2.索引优化向导

3.整理指定的表或视图的聚集索引和辅助索引碎片(dbcc indexefrag)

问题定位

时空在产品开发过程中遵循大开发理，共四个研发层次，第一层技术研发，由时空技术研发部负责产品技术架构，平台工具的构建，第二层产品研发，由时空产品研发部负责应用系统搭建。第三层项目研发，由渠道技术部负责客户化定制，第四层客户研发，由客户信息中心根据自己需求进行产品的定制。随着层次的增加，产品研发过程控制能力逐渐减弱，而且对系统的关注角度也不同，随着系统内数据量的增加，效率问题将逐渐显现出来，如何查找影响系统效率的原因成为能否解决问题的关键。在查找问题的过程中，把可能需要改进的程序或数据库对象及改进方法详细列举出来记录在《调整方案》(见附录)中。

一、检查数据表结构

1.查看在客户化开发过程中增加的新表，字段类型是否合适，特别要关注字段长度较长字符

型字段，可以考虑更改为VARCHAR类型。检查数据表中主键设置情况。明确数据表在系统

中存在的意义以及使用情况。

2.检查系统当中频繁使用的数据表：

maxbh,spkfk,spkfjc,hwsp,jxdjhz,jxdjmx,mchk,cwk,ywmxk,mxysyf,ywjsmxk,jsmxk,

splsk ，查看主键，索引的设置是否合理，根据客户的实际使用情况对索引进行调整，

对于在表中新增加的字段，一般来讲应针对该字段建单键索引或复合索引。把检查情

况记录在《调整方案》中。

二、检查存储过程

时空产品在发布时是一个通用版本，为了兼容广大客户的需求，在业务处理逻辑上需要考虑方面比较多，而客户的业务流程和需求和产品本身差别可能很大，导致一些存储过

程改动比较大。

例如：

SBP_KP_JS(开票结算)

SBP_JX_DJ(进销单据存储)

SPU_Z_sp_account（商品帐页登记）

SBP_WD_DJ（外调单据存储）

首先，查看过程中业务处理逻辑，把不必要的语句屏蔽或删除，以减轻系统压力。

其次，查看过程中SQL语句编写情况，在满足需求的前提下，作进一步优化处理。

第三，关注对大表（数据量较大）进行操作的SQL语句，拷贝到查询分析器中，查看执行计划，根据计划情况，调整SQL语句或者相关表的索引。

三、检查检索方案

第一．检查方案的数据过滤条件,尽量避免使用模糊匹配，在模糊查找时进行全表扫描，SQL 语句执行效率低下。

第二．仔细评定方案中需查询的字段必要性，减少网络流量。

第三．尽量减少方案中的连接子句所涉及的数据表。

第四．如果执行结果对数据实时性要求不高，或者没有数量，金额，成本等字段，应该使用锁定提示（NOLOCK）.

第五．根据客户使用习惯，拆分方案，分批获取所需要的数据。

如：销售开票时可以先提取商品，然后再根据商品内码提取货位，批号，数量等信息。

第六．分析查询方案的执行计划，调整SQL语句或者相关表索引。

四、检查查询方案

第一．控制查询方案的字段个数。

第二．明确查询的过滤条件。

第三．提取数据时考虑是否有可替代的表（数据量小），尽量避开操作比较频繁的数据表。

第四．对于查询数据实时性要求不高，应该使用锁定提示（NOLOCK）。

五、优化数据库布局

数据文件和日志文件的位置和分布对系统的性能来说非常重要。数据库布局的两个关键性指导原则：

第一．将连续访问的文件分布在专用磁盘上．一般情况下日志文件需要单独分配一个磁盘．第二．当布置数据文件时，应该将数据文件分布尽可能多的磁盘驱动器上，从而允许更多的并行磁盘访问。我们可以多创建一些附属数据文件，把数据量较大的业务表单独放在一个磁