SQL2023年系统优化解决方案

SQLServer系统调优解决方案前言近几年,医药流通市场经受了剧烈的震荡,导致行业逐步成熟和企业的快速变革,差异化经营成为众多医药流通的竞争选择。时空产品在中国医药流通企业的进展过程中得到了广泛且深入应用,大量的客户化开发和定制支撑了企业治理中横向和纵向的变化，很好的适应了企业在进展过程中不断变化的需求。对于数据库治理系统的使用，很多用户都面临着一个很麻烦的问题：系统效率下降。产生效率下降的因素是多方面：硬件问题软件问题实施问题正由于产生效率下降的因素很多，所以如何去查找缘由成为我们首要关注的问题，时空公司也处在乐观探究过程中。时空公司在解决一些客户问题的过程中积存了一些方法和思路，归纳总结后呈现给体系内的技术人员，本方案就系统效率调整所必需的根底学问、方法、技巧等几个方面进展阐述，从而让技术人员能够快速定位问题，解决问题，为合作伙伴供给优质，快捷的效劳。索引简介索引是依据数据库表中一个或多个列的值进展排序的构造指定列的数据值，然后依据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的名目很相像，通过搜寻索引找到特定的值，然后跟随指针到达包含该值的行。索引键：用于创立索引的列。索引类型聚拢索引：聚拢索引基于数据行的键值在表内排序和存储这些数据行。由于数据行按基于聚拢索引键的排序次序存储，因此聚拢索引对查找行很有效。每个表只能有一个聚拢索引，由于数据行本身只能按一个挨次存储。数据行本身构成聚拢索引的最低级别〔叶子节点。只有当表包含聚拢索引时，表内的数据行才按排序次序存储。假设表没有聚拢索引，则其数据行按堆集方式存储。聚拢索引对于那些常常要搜寻范围值的列特别有效。使用聚拢索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如：假设应用程序执行的一个查询常常检索某一日期范围内的记录，则使用聚拢索引可以快速找到包含开头日期的行，然后检索表中全部相邻的行，直到到达完毕日期。这样有助于提高此类查询的性能。同样，假设对从表中检索的数据进展排序时常常要用到某一列，则可以将该表在该列上聚拢〔物理排序，避开每次查询该列时都进展排序，从而节约本钱。A-BC-DE-F

根节点E 分支节点FA B数据

E F数据

叶节点〔包括数据〕非聚拢索引非聚拢索引具有完全独立于数据行的构造。非聚拢索引的最低行包含非聚拢索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚拢键的次序存储。假设一个表只有非聚拢索引，它的数据行将按无序的堆集方式存储,非聚拢索引可以建多个。A-BC-DE-F

根节点E 分支节点FA

B

E

F

叶节点〔指向数据〕唯一索引

数据 数据 数据 数据索引列中每个值组合都是唯一的。唯一索引既是索引也是约束。复合索引索引对性能的作用使用索引的优点通过唯一性索引〔unique〕可确保数据的唯一性加快数据的检索速度加快表之间的连接削减分组和排序的时间使用索引的原则在需要常常搜寻的列上创立索引常常用于连接的列上创立索引常常需要依据范围进展搜寻的列上创立索引常常需要排序的列上创立索引常常用于where子句的列上创立索引不使用索引的原则1.查询很少使用和参考的列不建索引对只有少数值的列不建索引对只有少数值的列不建索引定义为text、image、bit的列不建索引当需要update性能远远高于select性能时不建或少建索引常用命令sp_helpindex:报告表或视图上的索引信息dbccshowcontig:显示指定表的数据和索引的碎片信息dbccdbreindex:重建指定数据库中一个或多个索引dbccindexdefrag:整理指定表或视图的聚拢索引或关心索引的碎片创立索引定义索引时，可以指定每列的数据是按升序还是降序存储。假设不指定，则默认为升序为索引指定填充因子，可标识填充因子来指定每个索引页的填满程度。索引页上的空余空间量很重要，由于当索引页填满时，系统必需花时间拆分它以便为行腾出空间。优化索引重建索引〔dbccdbreindex〕索引优化向导整理指定的表或视图的聚拢索引和关心索引碎片(dbccindexefrag)问题定位时空在产品开发过程中遵循大开发理，共四个研发层次，第一层技术研发，由时空技术研发部负责产品技术架构，平台工具的构建，其次层产品研发，由时空产品研发部负责应用系统搭建。第三层工程研发，由渠道技术部负责客户化定制，第四层客户研发，由客户信息中心依据自己需求进展产品的定制。随着层次的增加，产品研发过程掌握力量渐渐减弱，而且对系统的关注角度也不同，随着系统内数据量的增加，效率问题将渐渐显现出来，如何查找影响系统效率的缘由成为能否解决问题的关键。在查找问题的过程中，把可能需要改进的程序或数据库对象及改进方法具体列举出来记录在《调整方案》(见附录)中。一、检查数据表构造查看在客户化开发过程中增加的表，字段类型是否适宜，特别要关注字段长度较长字符型字段，可以考虑更改为VARCHAR类型。检查数据表中主键设置状况。明确数据表在系统中存在的意义以及使用状况。检查系统当中频繁使用的数据表：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四、检查查询方案第一．掌握查询方案的字段个数。其次．明确查询的过滤条件。第三．提取数据时考虑是否有可替代的表〔数据量小第四．对于查询数据实时性要求不高，应当使用锁定提示NOLOC。五、优化数据库布局数据文件和日志文件的位置和分布对系统的性能来说格外重要。数据库布局的两个关键性指导原则：第一．将连续访问的文件分布在专用磁盘上．一般状况下日志文件需要单独安排一个磁盘．其次．当布置数据文件时，应当将数据文件分布尽可能多的磁盘驱动器上，从而允许更多的并行磁盘访问。我们可以多创立一些附属数据文件，把数据量较大的业务表单独放在一个磁盘上，为了明确地将数据库表和索引放在特定的磁盘驱动上，必需创立用户定义文件组，文件组供给了规律地将文件组合地起来的方法，以及将单个文件与主文件组分别的方法，假设不创立其他文件组，在默认状况下，全部文件都进入主文件组。当在含有多个数据文件的文件组中创立表或索引时，SQLServerSQLServer按数据文件的大小成比例地填充每个数据文件。六、整体业务掌握提高系统运行效率，是综合多方面，多环节调整结果的最终表达，我们要求的是整体最优，而不是局部最优。要从全局的角度去衡量系统，而不是把目光只盯在某一个环节上，只有这样才能查找到系统当中一些隐含的问题，否则在实际运行时可能不会达预期效果，关注细节只是一个最根本工作要求。如何提高从宏观角度去衡量系统所需要的素养，首先，必需了解客户治理理念，治理方式，生疏客户的业务流程，从而确定系统应当为客户供给一个什么样的效劳。其次，了解使用人员的业务需求及其在使用过程中所关注的信息点。第三，技术人员要格外生疏时空的产品，把握每一个功能模块的存在的价值和意义，以及业务处理的方法和规律。具备了上述几种技能，才能在思考的过程把整个系统包融在自己思维中，才能跳出系统本身去透视产品运作流程，感受产品的使用方法，应用价值。销售开票，是系统的一个根本的应用，选择商品，填写批号，数量等信息，但是使用人员觉察检索数据的速度比较慢，影响业务的快速进展，这时就要考虑在操作过程中使用的方案是否有效，信息是否有意义，方案中使用的表在哪些环节常常被使用，在使用的过程中是否被锁七、SQL语句跟踪系统效率下降，在很多状况下，产生问题的根本缘由是效率低下的SQL语句，SQL大事探查器〔SQLProfiler〕将帮助技术人员确定是哪一个语句消灭问题，当查找需要调整的SQL语句时，从使用资源最多或者运行时间最长或者最常常执行的SQL语句入手，调整一条或几条使用大量系统资源的SQLSQLSERVER的活动，可以区分哪个应用程序，存储过程和SQL语句占用了最长时间，或者哪些语句使用频率较高。SQLProfiler所供给的预定义的跟踪模板，在很多状况下组织和功能都格外优秀，可以依据特性需求修改这些跟踪模板，并将这些修改后的跟踪模板保存为模板，这样可以削减大量工作。这些预定义跟踪模板如下所示：Standard(SQLServerProfilerStandard.tdf)SQLSQL批处理的具体息常有用的。StoredProcedureCounts(SQLServerProfilerSP_Counts.tdf)记录已经执行的存储过程以及这些存储过程运行频率的数据，了解不同的存储过程运行的次数将有助于确定哪个存储过程是最好的调整对象。一个执行频率较高，但效率低下的存储过程是一个需要调整的好对象，在这个跟踪中，增加SP:Completed大事和Duration数据是非常有用的。TSQL(SQLServerProfilerTSQL.tdfSQL语句的提交挨次搜集SQL这些信息来查看系统的活动。可以将这些活动与系统的其它的大事相关联例如，死锁或其它系统问题TSQLByDuration(SQLServerProfilerTSQL_Duration.tdf)SQL句以及执行这些SQLTSQLGrouped(SQLServerProfilerTSQL_Grouped.tdf)SQL语句的具体信息并且是依据应用程序名称，WINDOWSNT用户名称以及进程ID信息对于查找特定用户报告的问题格外有用，例如少数用户正在经受死锁。通过检查SQLTSQLStoredProcedures(SQLServerProfilerTSQL_SPs.tdf)显示存储过程和存储过程内部的SQL命令。结果依据时间挨次进展排序，对于那些调用存储过程的过程意义较大。应用例如：查找运行时间较长的SQL语句查找长时间运行的查询的最好方法是使用下面的大事，并按Duration〔查找长时间运行的查询的最好方法是使用下面的大事，并按Duration〔(时间的)持续〕数据列分组．TSQL,SQL:BatchCompleteSQL依据Duration间进展排序，在跟踪数据窗口的底部列出了运行时间最长的SQL语句，这可能是调整系统性能的最好地方。查找资源消耗型作业这种跟踪类型查看消耗了CPUI/OSQLCPU,ReadsWritesI/OCPUTSQL,SQL:BatchCompleteSQLCPU,ReadWrites数据列将显示由该大事使用的资源。检测死锁产生死锁现象，对于系统来讲是一个格外严峻问题，尤其对在线事务处理〔OLTP〕影响格外大，那么查找死锁产生的原也就等同于改善系统性能。在跟踪定义选择以下大事。．TSQL,SQL:BatchStarting正运行的SQL．Locks,Lock:Deadlock死锁本身大事。．Locks,Lock:Deadlockchain导致死锁的一系列大事。八、查看执行打算SQL查询分析器，是一个格外重要的工具，在系统效率调整过程具有不行替代的作用，它允许用户对SQLSERVER数据库运行特定查询，还可以供给一个查询所消耗的系统资源的信息，这些信息在分析和调整系统性能方面很有帮助，技术人员能够交互式地设计和测试SQL在实际操作中，应遵循这样一个原则：尽量避开全表扫描，全表扫描格外消耗系统资源，通过建主键或者调整索引的方法，使SQL九、调整业务规律在时空业务系统中存在一些大数据量的业务表，而且使用格外频繁。对于大表数据的检索更耗时较长，系统反响迟钝。尤其在客户业务量比较大的时候，表现更加突出，影响销售进程，并且时常会产生死锁现象。在这种状况下，调整SQL语句，调整索引也达不到预期效果。这时我们应当考虑借助第三方数据表来到达我们治理掌握的要求。比方：销售开票时，为了避开负库存销售，常常要校验商品已开票未出库数量，一般的方法就是在检索商品的时候与jxdjmx目的，便不是最优的。随着表中数据量的渐渐增长，开票的效率也渐渐下降，直至系统不行用。是不是还有更好的方法呢？答案是确定。我们的目标是拦截负库销售，和这件事有关的数据是未执行的开票单据，并且未执行单据数据量较小，假设把这局部数据同历史数据区分开，系统效率将会有很大的提升，所以我们可再创立一个构造同原表一样的临时数据表，在开票存盘时另存一个副本到临时表当中。当需要数据校验时我们就可以避开原来的大表，直接从临时表提取数据。接下来的问题是在什么时机删除临时表中的数据，对于这个问题我们首先要确认，在什么时候票据完成了它所承载的业务活动，应当是已执行的票据，或者是状态为清的票据，那么我们可以在jxdjmx表中增加触发器――当回写is_zx通过上面的例子，我们可以觉察只是处理方法的简洁转换，就可以到达既满足治理要求，系统又很有效率的目的，所以我们在处理客户需求过程中需要常常换个角度去考虑问题，去查找更有效的法。十、数据封存时空公司针对业务表数据量越来越大问题，在产品中供给了数据封存功能。把数据从原表转移到封存表〔‘_f〕留意掌握数据量〔30。十一、十二、1.2.3.4.5.

流程重组业务流程是企业为了实现某一特定目标而实行的一系列行动。一个流程包括很多项活动。流程重组就是对企业的业务流程进展根本性的再思考和转变，从调整效率角度来看，重组主要目的把系统压力进展分摊，从而获得在效劳和速度等方面业绩的改善，使企业能最大限度地适应顾客、竞争、变化为特征的企业经营环境。例如，时空产品中供给销售出库且结算功能，由于销售出库需要记录商品帐页，往来帐页，回写开票单据，产生结算信息，所以对系统压力较大，同时锁定很多相关的业务表，对系统其他业务环节影响也很大。假设把功能拆解成：开票结算――出库，这样可以减轻出库环节的系统压力，保证整个业务流程高效，快速运行。在流程重组过程中要充分考虑企业治理和掌握要求，同时兼顾系统本身运行特点，从而到达双赢的结果。移动tempdbTempdbSQLSERVERK是一个临时数据库，它对性能的影响较大。tempdb数据库一样可以增大，可以缩小。当数据文件需要增长的时候，通常不能保持剩余局部的连续性。这时文件就会产生碎片，这种碎片会造成性能下降。这种碎片属于外来性碎片。要阻挡在tempdb中产生外来性碎片，必需保证有足够的硬盘空间。一般将tempdb的容量放到平均使用容量。而你也应当允许tempdb自动增长，比方你有个一个超大的join操作，它建立了一个超过tempdb容量的时候，该查询将失败。你还要设置一个合理的单位增长量。由于假设你设得太小，将会产生很多外来性碎片，反而会占用更多资源。sqlserver调优最有效的做法之一，就是把争夺资源的操作独立出去。tempdb就是tempdb库，全部处理临时表、子查询、GROUPBY、排序、DISTINCT、连接等等。它最适合放到一个具有快速读写力量的设备上。tempdbsp_helpdb查看tempdbalterdatabasetmpdbmodifyfile(name=”tempdev”,filename=”newpath\newfilename”,size=500mb)alterdatabasetmpdbmodifyfile(name=”templog”,filename=”newpath\newfilename”,size=500mb)SQLSERVER删掉旧的tempdb十三、 效劳器性能监控CPU要监视CPUSystemMonitor并选择如下的对象和计数器进展监视：ProcessorObject(处理器对象)，%ProcessorTime(处理器时间计数),选择全部实例可以查看每个处理器的使用状况，以及全部处理器的平均使用率.假设处理器使用率保持在80%或更高，或者常常消灭峰值使用率，系统就可能具有CPU瓶颈，可以在系统中添加更多或更快的处理器，这样就可以提高系统性能．调整内存假设条件允许，最好是SQLServerSQLServer尽可能地使用系统内存，而不用与其他应用程序一起共享系统内存。通过SystemMonitor可以监视如下对象．MemoryObject,AvaliableMbytes表示系统中可供进程使用的内存．SQLServer:MemoryManagerObject,TotalServerMemory(KB)SQLServer所安排总内存大小．SQLServer:BufferManagerObject,BufferCacheHitRatio假设AvaliableMbytes计数器的值格外小，意味着系统中已经没有足够的物理内存可供使用，必需查看其它计数器确定是否增加物理内存。假设缓冲存储器命中率低于90%，那么系统通常需要更多的物理内存。对于数据库内存配置，通常要求设置为固定内存大小，这样可以强制给SQLServer安排内存，提高内存的使用率。磁盘调整一．监测磁盘I/OSystemMonitor并选择PhysicalDiskDiskReads/secDiskWrites/sec所选择磁盘每秒所执行的写操作二．分析磁盘指标〔可参考制造商规格说书〕:平均寻道时间(毫秒)=[平均寻道〔读〕+平均寻道〔写〕]/2磁盘旋转等待时间〔毫秒〕=500/[转速〔转/分〕/60]磁盘最正确I/O=[1000*0.8]/[平均寻道时间+磁盘旋转等待时间]RAID0:I/0每个磁盘的I/0I/0/磁盘数量RAID1:I/0〔2*写)]每个磁盘的I/0I/0/2RAID5:I/0〔4*写)]每个磁盘的I/0I/0/磁盘数量RAID10:I/0〔2*写)]每个磁盘的I/0I/0/磁盘数量I/0I/O盘：磁盘个数=I/0/磁盘最正确I/O数据库调整一．备份数据库二．用备份文件重恢复一个测试库三．依据《调整方案》记录的问题及改进方法，在测试库中修改，并把实际修改结果记录《调整方案》中.四．系统测试案例五．测试成功后，调整正式数据库。A公司业务系统上线运行一年后系统速度变慢，而且常常消灭死锁现象。使用SQL大事探查器跟踪觉察maxbhmaxbh通过实地观看系统使用状况：销售开票检索商品，开票结算存盘速度较慢。查看商品检索方案，selectspid,pihao,sum(shl)shlfromsphwphgroupbyspid,pihao。同时为了拦截负库存销售，关联jxdjhz,jxdjmx执行的商品数量，而且商品的过滤条件为模糊查找。经过分析，视图在每次检索商品时都要对全部的品种分组求和，对系统压力较大，假设这时有出库业务发生，记帐回写sphwph表，开票将会产生资源等待。针对这种状况，作出如下调整：取消视图，直接在方案中关联sphwph表，通过商品过滤，削减分组求合的运算量，削减与出库记帐冲突的概率。建一业务临时表，在销售开票时，另存一份明细到该表，从临时表中检索已开票未执行数据。在jxdjmxis_zx同客户技术人员协商，商品检索承受左匹配方式跟踪查看开票结算存储过程，觉察回写mxysyf语句执行时间较长，分析客户实际业务，这个时机还没有产生应收应付信息，所以把回写语句删除。通过针对上述几个问题的处理，系统效率得到了很大提升。过程编写技术保证在实现功能的根底上，尽量削减对数据库的访问次数；通过搜寻参数，尽量削减对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速SQL时，尽量把使用的索引放在选择的首列；算法的构造尽量简洁；在查询时，不要过多地使用通配符，而且要用到几列就选择几列，如：SELECTC1,C2FROMT1；在可能的状况下尽量限制尽量结果集行数，如：SELECTTOP300C1,C2FROMT1,由于某些状况下用户是不需要那么多的数据的,避开用!=或<>ISNULLISNOTNULL、IN，NOTIN用索引,而只能直接搜寻表中的数据。例如:SELECTC1FROMT1WHEREC1!=”B%”合理使用EXISTS,NOTEXISTSSELECTSUM(T1.C1)FROMT1WHERE((SELECTCOUNT(1)FROMT2WHERET2.C2=T1.C2)>0)SELECTSUM(T1.C1)FROMT1WHEREEXISTS(SELECT1FROMT2WHERET2.C2=T1.C2)两者产生一样的结果，但是后者的效率明显要高于前者。由于后者不会产生大量锁定的表count(*)那样效率很低，而且铺张效劳器资源。可以用EXISTSIF(SELECTCOUNT(1)FROMtable_nameWHEREcolumn_name=”xxx”)>0可以写成：IFEXISTS(SELECT1FROMtable_nameWHEREcolumn_name=”xxx”)常常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如：SELECTa.C1FROMT1aWHERENOTEXISTS(SELECT1FROMT2bWHEREa.C1=b.C1)SELECTa.C1FROMT1aLEFTJOINT2bONa.C1=b.C1WHEREb.C1ISNULLSELECTa.C1FROMT1aWHEREa.C1NOTIN(SELECTC1FROMT2)三种写法都可以得到同样正确的结果，但是效率依次降低。能够用BETWEEN的就不要用INSELECT*FROMT1WHEREIDIN(10,11,12,13,14)改成：SELECT*FROMT1WHEREIDBETWEEN10AND14由于IN能够用DISTINCT的就不用GROUPBYSELECTC1FROMT1WHEREC2>10GROUPBYC1可改为：SELECTDISTINCTC1FROMT1WHEREC2>10UNIONALLUNIONUNIONALL不执行SELECTDISTINCT尽量避开大事务操作，慎用LOCK子句，提高系统并发力量。尽量避开反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先依据条件