SQLSERVER调优解决方案

SQLServer调优解决方案前言近几年,医药流通市场经历了激烈的震荡,导致行业逐步成熟和企业的快速变革,差异化经营成为众多医药流通的竞争选择。时空产品在中国医药流通企业的发展过程中得到了广泛且深入应用,大量的客户化开发和定制支撑了企业管理中横向和纵向的变化，很好的适应了企业在发展过程中不断变化的需求。对于数据库管理系统的使用，很多用户都面临着一个很棘手的问题：系统效率下降。产生效率下降的因素是多方面：1.硬件问题2.软件问题3.实施问题正因为产生效率下降的因素很多，所以如何去查找原因成为我们首要关注的问题，时空公司也处在积极探索过程中。时空公司在解决一些客户问题的过程中积累了一些方法和思路，归纳总结后呈现给体系内的技术人员，本方案就系统效率调整所必需的基础知识、方法、技巧等几个方面进行阐述，从而让技术人员能够快速定位问题，解决问题，为合作伙伴提供优质，快捷的服务。索引简介索引是根据数据库表中一个或多个列的值进行排序的结构。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似，通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。索引键：用于创建索引的列。索引类型聚集索引：聚集索引基于数据行的键值在表内排序和存储这些数据行。由于数据行按基于聚集索引键的排序次序存储，因此聚集索引对查找行很有效。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。数据行本身构成聚集索引的最低级别（叶子节点）。只有当表包含聚集索引时，表内的数据行才按排序次序存储。如果表没有聚集索引，则其数据行按堆集方式存储。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如：如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。A-BC-DE-FA数据E数据B数据F数据ABEF根节点分支节点叶节点（包括数据）非聚集索引非聚集索引具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储,非聚集索引可以建多个。A-BC-DE-FA行IDE行IDB行IDF行IDABEF根节点分支节点叶节点（指向数据）数据数据数据数据唯一索引唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。唯一索引既是索引也是约束。复合索引索引项是多个的就叫组合索引，也叫复合索引。复合索引使用时需要注意索引项的次序。索引对性能的作用使用索引的优点1.通过唯一性索引（unique）可确保数据的唯一性2.加快数据的检索速度3.加快表之间的连接4.减少分组和排序的时间使用索引的原则1.在需要经常搜索的列上创建索引2.经常用于连接的列上创建索引3.经常需要根据范围进行搜索的列上创建索引4.经常需要排序的列上创建索引5.经常用于where子句的列上创建索引不使用索引的原则1.查询很少使用和参考的列不建索引2.对只有少数值的列不建索引3.定义为text、image、bit的列不建索引4.当需要update性能远远高于select性能时不建或少建索引常用命令1.sp_helpindex:报告表或视图上的索引信息2.dbccshowcontig:显示指定表的数据和索引的碎片信息3.dbccdbreindex:重建指定数据库中一个或多个索引4.dbccindexdefrag:整理指定表或视图的聚集索引或辅助索引的碎片创建索引1.定义索引时，可以指定每列的数据是按升序还是降序存储。如果不指定，则默认为升序2.为索引指定填充因子，可标识填充因子来指定每个索引页的填满程度。索引页上的空余空间量很重要，因为当索引页填满时，系统必须花时间拆分它以便为新行腾出空间。优化索引1.重建索引（dbccdbreindex）2.索引优化向导3.整理指定的表或视图的聚集索引和辅助索引碎片(dbccindexefrag)问题定位时空在产品开发过程中遵循大开发理，共四个研发层次，第一层技术研发，由时空技术研发部负责产品技术架构，平台工具的构建，第二层产品研发，由时空产品研发部负责应用系统搭建。第三层项目研发，由渠道技术部负责客户化定制，第四层客户研发，由客户信息中心根据自己需求进行产品的定制。随着层次的增加，产品研发过程控制能力逐渐减弱，而且对系统的关注角度也不同，随着系统内数据量的增加，效率问题将逐渐显现出来，如何查找影响系统效率的原因成为能否解决问题的关键。在查找问题的过程中，把可能需要改进的程序或数据库对象及改进方法详细列举出来记录在《调整方案》(见附录)中。一、检查数据表结构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）。五、优化数据库布局数据文件和日志文件的位置和分布对系统的性能来说非常重要。数据库布局的两个关键性指导原则：第一．将连续访问的文件分布在专用磁盘上．一般情况下日志文件需要单独分配一个磁盘．第二．当布置数据文件时，应该将数据文件分布尽可能多的磁盘驱动器上，从而允许更多的并行磁盘访问。我们可以多创建一些附属数据文件，把数据量较大的业务表单独放在一个磁盘上，为了明确地将数据库表和索引放在特定的磁盘驱动上，必须创建用户定义文件组，文件组提供了逻辑地将文件组合地起来的方法，以及将单个文件与主文件组分离的方法，如果不创建其他文件组，在默认情况下，所有文件都进入主文件组。当在含有多个数据文件的文件组中创建表或索引时，SQLServer使用按比例填充机在文件之间分布数据。使用这种机制SQLServer按数据文件的大小成比例地填充每个数据文件。六、整体业务控制提高系统运行效率，是综合多方面，多环节调整结果的最终体现，我们要求的是整体最优，而不是局部最优。要从全局的角度去衡量系统，而不是把目光只盯在某一个环节上，只有这样才能查找到系统当中一些隐含的问题，否则在实际运行时可能不会达预期效果，关注细节只是一个最基本工作要求。如何提高从宏观角度去衡量系统所需要的素质，首先，必需了解客户管理理念，管理方式，熟悉客户的业务流程，从而确定系统应该为客户提供一个什么样的服务。其次，了解使用人员的业务需求及其在使用过程中所关注的信息点。第三，技术人员要非常熟悉时空的产品，掌握每一个功能模块的存在的价值和意义，以及业务处理的方法和逻辑。具备了上述几种技能，才能在思考的过程把整个系统包融在自己思维中，才能跳出系统本身去透视产品运作流程，感受产品的使用方法，应用价值。销售开票，是系统的一个基本的应用，选择商品，填写批号，数量等信息，但是使用人员发现检索数据的速度比较慢，影响业务的快速进行，这时就要考虑在操作过程中使用的方案是否有效，信息是否有意义，方案中使用的表在哪些环节经常被使用，在使用的过程中是否被锁定，我们可以按照这种方法进行横向或纵向的比较分析，逐步去找出问题的根源。七、SQL语句跟踪系统效率下降，在许多情况下，产生问题的根本原因是效率低下的SQL语句，SQL事件探查器（SQLProfiler）将帮助技术人员确定是哪一个语句出现问题，当查找需要调整的SQL语句时，从使用资源最多或者运行时间最长或者最经常执行的SQL语句入手，调整一条或几条使用大量系统资源的SQL语句将对系统性能有显著影响。通过跟踪SQLSERVER的活动，可以区分哪个应用程序，存储过程和SQL语句占用了最长时间，或者哪些语句使用频率较高。SQLProfiler所提供的预定义的跟踪模板，在许多情况下组织和功能都非常优秀，可以根据特性需求修改这些跟踪模板，并将这些修改后的跟踪模板保存为新模板，这样可以减少大量工作。这些预定义跟踪模板如下所示：1.Standard(SQLServerProfilerStandard.tdf)提供所执行的SQL语句和所完成的SQL批处理的详细息2.StoredProcedureCounts(SQLServerProfilerSP_Counts.tdf)记录已经执行的存储过程以及这些存储过程运行频率的数据，了解不同的存储过程运行的次数将有助于确定哪个存储过程是最好的调整对象。一个执行频率较高，但效率低下的存储过程是一个需要调整的好对象，在这个跟踪中，增加SP:Completed事件和Duration数据是非常有用的。3.TSQL(SQLServerProfilerTSQL.tdf)按照SQL语句的提交顺序搜集SQL语句，可以使用这些信息来查看系统的活动。可以将这些活动与系统的其它的事件相关联例如，死锁或其它系统问题4.TSQLByDuration(SQLServerProfilerTSQL_Duration.tdf)显示已经执行的SQL语句以及执行这些SQL语句所需要的时间。5.TSQLGrouped(SQLServerProfilerTSQL_Grouped.tdf)提供已经执行的SQL语句的详细信息并且是根据应用程序名称，WINDOWSNT用户名称以及进程ID进行分组。这个信息对于查找特定用户报告的问题非常有用，例如少数用户正在经历死锁。通过检查SQL批处理开始的时间戳，可以清楚地知道应用程序中每一步执行所花费的时间。6.TSQLStoredProcedures(SQLServerProfilerTSQL_SPs.tdf)显示存储过程和存储过程内部的SQL命令。结果按照时间顺序进行排序，对于那些调用存储过