SQL系统性能优化解决方案共12页文档.docx
《SQL系统性能优化解决方案共12页文档.docx》由会员分享，可在线阅读，更多相关《SQL系统性能优化解决方案共12页文档.docx（20页珍藏版）》请在冰点文库上搜索。
SQL系统性能优化解决方案共12页文档
SQLServer
唐宋或更早之前，针对“经学”“律学”“算学”和“书学”各科目，其相应传授者称为“博士”，这与当今“博士”含义已经相去甚远。
而对那些特别讲授“武事”或讲解“经籍”者，又称“讲师”。
“教授”和“助教”均原为学官称谓。
前者始于宋，乃“宗学”“律学”“医学”“武学”等科目的讲授者；而后者则于西晋武帝时代即已设立了，主要协助国子、博士培养生徒。
“助教”在古代不仅要作入流的学问，其教书育人的职责也十分明晰。
唐代国子学、太学等所设之“助教”一席，也是当朝打眼的学官。
至明清两代，只设国子监（国子学）一科的“助教”，其身价不谓显赫，也称得上朝廷要员。
至此，无论是“博士”“讲师”，还是“教授”“助教”，其今日教师应具有的基本概念都具有了。
系统性能调优解决方案
我国古代的读书人,从上学之日起,就日诵不辍,一般在几年内就能识记几千个汉字,熟记几百篇文章,写出的诗文也是字斟句酌,琅琅上口,成为满腹经纶的文人。
为什么在现代化教学的今天,我们念了十几年书的高中毕业生甚至大学生,竟提起作文就头疼,写不出像样的文章呢?
吕叔湘先生早在1978年就尖锐地提出:
“中小学语文教学效果差,中学语文毕业生语文水平低,……十几年上课总时数是9160课时,语文是2749课时,恰好是30%,十年的时间,二千七百多课时,用来学本国语文,却是大多数不过关,岂非咄咄怪事!
”寻根究底,其主要原因就是腹中无物。
特别是写议论文,初中水平以上的学生都知道议论文的“三要素”是论点、论据、论证,也通晓议论文的基本结构:
提出问题――分析问题――解决问题,但真正动起笔来就犯难了。
知道“是这样”,就是讲不出“为什么”。
根本原因还是无“米”下“锅”。
于是便翻开作文集锦之类的书大段抄起来,抄人家的名言警句,抄人家的事例,不参考作文书就很难写出像样的文章。
所以,词汇贫乏、内容空洞、千篇一律便成了中学生作文的通病。
要解决这个问题,不能单在布局谋篇等写作技方面下功夫,必须认识到“死记硬背”的重要性,让学生积累足够的“米”。
前言
要练说，先练胆。
说话胆小是幼儿语言发展的障碍。
不少幼儿当众说话时显得胆怯：
有的结巴重复，面红耳赤；有的声音极低，自讲自听；有的低头不语，扯衣服，扭身子。
总之，说话时外部表现不自然。
我抓住练胆这个关键，面向全体，偏向差生。
一是和幼儿建立和谐的语言交流关系。
每当和幼儿讲话时，我总是笑脸相迎，声音亲切，动作亲昵，消除幼儿畏惧心理，让他能主动的、无拘无束地和我交谈。
二是注重培养幼儿敢于当众说话的习惯。
或在课堂教学中，改变过去老师讲学生听的传统的教学模式，取消了先举手后发言的约束，多采取自由讨论和谈话的形式，给每个幼儿较多的当众说话的机会，培养幼儿爱说话敢说话的兴趣，对一些说话有困难的幼儿，我总是认真地耐心地听，热情地帮助和鼓励他把话说完、说好，增强其说话的勇气和把话说好的信心。
三是要提明确的说话要求，在说话训练中不断提高，我要求每个幼儿在说话时要仪态大方，口齿清楚，声音响亮，学会用眼神。
对说得好的幼儿，即使是某一方面，我都抓住教育，提出表扬，并要其他幼儿模仿。
长期坚持，不断训练，幼儿说话胆量也在不断提高。
近几年,医药流通市场经历了激烈的震荡,导致行业逐步成熟和企业的快速变革,差异化经营成为众多医药流通的竞争选择。
时空产品在中国医药流通企业的发展过程中得到了广泛且深入应用,大量的客户化开发和定制支撑了企业管理中横向和纵向的变化，很好的适应了企业在发展过程中不断变化的需求。
对于数据库管理系统的使用，很多用户都面临着一个很棘手的问题：
系统效率下降。
产生效率下降的因素是多方面：
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.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语句将对系统性能有显著影响。
通过跟踪SQL SERVER的活动，可以区分哪个应用程序，存储过程和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命令。
结果按照时间顺序进行排序，对于那些调用存储过程的过程意义较大。
应用示例：
1.查找运行时间较长的SQL语句
查找长时间运行的查询的最好方法是使用下面的事件，并按Duration（（时间的）持续）数据列分组
．TSQL,SQL:
BatchComplete SQL批处理完成执行所花费的时间．
根据Duration数据列的顺序进行分组，将使跟踪的结果按照每一个语句执行所需的时间进行排序，在跟踪数据窗口的底部列出了运行时间最长的SQL语句，这可能是调整系统性能的最好地方。
2.查找资源消耗型作业
这种跟踪类型查看消耗了CPU和I/O资源的SQL语句。
最佳方法是选择以下事件进行监视，并按照CPU,Reads或者Writes列进行分组，这取决于你更关心I/O资源还是CPU资源的使用。
．TSQL,SQL:
BatchComplete SQL批处理完成执行所需的时间。
CPU,Reads，Writes数据列将显示由该事件使用的资源。
3.检测死锁
产生死锁现象，对于系统来讲是一个非常严重问题，尤其对在线事务处理（OLTP）影响非常大，那么查找死锁产生的原也就等同于改善系统性能。
在跟踪定义选择下列事件。
．TSQL,SQL:
BatchStarting正运行的SQL批处理。
．Locks,Lock:
Deadlock死锁本身事件。
．Locks,Lock:
Deadlockchain导致死锁的一系列事件。
八、查看执行计划
SQL查询分析器，是一个非常重要的工具，在系统效率调整过程具有不可替代的作用，它允许用户对SQLSERVER数据库运行特定查询，还可以提供一个查询所消耗的系统资源的信息，这些信息在分析和调整系统性能方面很有帮助，技术人员能够交互式地设计和测试SQL语句。
在实际操作中，应遵循这样一个原则：
尽量避免全表扫描，全表扫描非常消耗系统资源，通过建主键或者调整索引的方法，使SQL语句执行时扫描索引。
九、调整业务逻辑
在时空业务系统中存在一些大数据量的业务表，而且使用非常频繁。
对于大表数据的检索更新耗时较长，系统反应迟钝。
尤其在客户业务量比较大的时候，表现更加突出，影响销售进程，并且时常会产生死锁现象。
在这种情况下，调整SQL语句，调整索引也达不到预期效果。
这时我们应该考虑借助第三方数据表来达到我们管理控制的要求。
比如：
销售开票时，为了避免负库存销售，经常要校验商品已开票未出库数量，一般的方法就是在检索商品的时候与jxdjmx表关联。
这样的处理逻辑达到了拦截负库存的目的，便不是最优的。
随着表中数据量的逐渐增长，开票的效率也逐渐下降，直至系统不可用。
是不是还有更好的方法呢？
答案是肯定。
我们的目标是拦截负库销售，和这件事有关的数据是未执行的开票单据，并且未执行单据数据量较小，如果把这部分数据同历史数据区分开，系统效率将会有很大的提升，所以我们可再创建一个结构同原表一样的临时数据表，在开票存盘时另存一个副本到临时表当中。
当需要数据校验时我们就可以避开原来的大表，直接从临时表提取数据。
接下来的问题是在什么时机删除临时表中的数据，对于这个问题我们首先要确认，在什么时候票据完成了它所承载的业务活动，应该是已执行的票据，或者是状态为清的票据，那么我们可以在jxdjmx表中增加触发器――当回写is_zx字段时，通过单据编号关联删除临时表中数据。
通过上面的例子，我们可以发现只是处理方法的简单转换，就可以达到既满足管理要求，系统又很有效率的目的，所以我们在处理客户需求过程中需要经常换个角度去考虑问题，去寻找更有效的法。
一十、数据封存
时空公司针对业务表数据量越来越大问题，在产品中提供了数据封存功能。
把数据
从原表转移到封存表（原表名+‘_fc’）中。
也可以显著提高系统效率。
在封存过程中要
注意控制数据量（一般不超过30万条数据）。
一十一、流程重组
业务流程是企业为了实现某一特定目标而采取的一系列行动。
一个流程包括许多项活动。
流程重组就是对企业的业务流程进行根本性的再思考和改变，从调整效率角度来看，重组主要目的把系统压力进行分摊，从而获得在服务和速度等方面业绩的改善，使企业能最大限度地适应顾客、竞争、变化为特征的企业经营环境。
例如，时空产品中提供销售出库且结算功能，由于销售出库需要记录商品帐页，往来帐页，回写开票单据，产生结算信息，所以对系统压力较大，同时锁定很多相关的业务表，对系统其他业务环节影响也很大。
如果把功能拆解成：
开票结算――出库，这样可以减轻出库环节的系统压力，保证整个业务流程高效，快速运行。
在流程重组过程中要充分考虑企业管理和控制要求，同时兼顾系统本身运行特点，从而达到双赢的结果。
一十二、移动tempdb
Tempdb在SQLSERVERK是一个临时数据库，它对性能的影响较大。
tempdb和其他数据库一样可以增大，可以缩小。
当数据文件需要增长的时候，通常不能保持剩余部分的连续性。
这时文件就会产生碎片，这种碎片会造成性能下降。
这种碎片属于外来性碎片。
要阻止在tempdb中产生外来性碎片，必须保证有足够的硬盘空间。
一般将tempdb的容量放到平均使用容量。
而你也应该允许tempdb自动增长，比如你有个一个超大的join操作，它建立了一个超过tempdb容量的时候，该查询将失败。
你还要设置一个合理的单位增长量。
因为如果你设得太小，将会产生许多外来性碎片，反而会占用更多资源。
sqlserver调优最有效的做法之一，就是把争夺资源的操作独立出去。
tempdb就是一个需要独立出去的部分，而tempdb和其他系统库一样是公用的，是存取最可能频繁的库，所有处理临时表、子查询、GROUPBY、排序、DISTINCT、连接等等。
它最适合放到一个具有快速读写能力的设备上。
移动tempdb的方法：
1.sp_helpdb查看tempdb现在存放的位置
2.alterdatabasetmpdbmodifyfile
（name='tempdev',filename='newpath\newfilename',size=500mb）
3.alterdatabasetmpdbmodifyfile
（name='templog',filename='newpath\newfilename',size=500mb）
4.关闭SQLSERVER重启
5.删掉旧的tempdb文件
一十三、服务器性能监控
Ø调整CPU
要监视CPU的使用情况，必须保障对系统处理非常繁忙的某一天全天进行监视，这样就可以对系统进行配置，从而能处理最繁忙的任务，可以使用SystemMonitor并选择如下的对象和计数器进行监视：
ProcessorObject（处理器对象），%ProcessorTime（处理器时间计数）,选择所有实例可以查看每个处理器的使用情况，以及所有处理器的平均使用率.如果处理器使用率保持在80%或更高，或者经常出现峰值使用率，系统就可能具有CPU瓶颈，可以在系统中添加更多或更快的处理器，这样就可以提高系统性能．
Ø调整内存
如果条件允许，最好是SQLServer独立占用数据库服务器，这就允许SQLServer能够尽可能地使用系统内存，而不用与其他应用程序一起共享系统内存。
通过SystemMonitor 可以监视如下对象
．MemoryObject,AvaliableMbytes表示系统中可供进程使用的内存
．SQLServer:
MemoryManagerObject,TotalServerMemory（KB）SQLServer所分配总内存大小
．SQLServer:
BufferManagerObject,BufferCacheHitRatio缓冲存储器命中率
如果AvaliableMbytes计数器的值非常小，意味着系统中已经没有足够的物理内存可
升级会员 