SQLSERVER调优解决方案Word格式文档下载.docx

SQLSERVER调优解决方案Word格式文档下载.docx

《SQLSERVER调优解决方案Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《SQLSERVER调优解决方案Word格式文档下载.docx（15页珍藏版）》请在冰豆网上搜索。

数据行本身构成聚集索引的最低级别（叶子节点）。

只有当表包含聚集索引时，表内的数据行才按排序次序存储。

如果表没有聚集索引，则其数据行按堆集方式存储。

聚集索引对于那些经常要搜索范围值的列特别有效。

使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

例如：

如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。

这样有助于提高此类查询的性能。

同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

非聚集索引

非聚集索引具有完全独立于数据行的结构。

非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。

数据行不按基于非聚集键的次序存储。

如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储,非聚集索引可以建多个。

唯一索引

唯一索引可以确保索引列不包含重复的值。

在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。

唯一索引既是索引也是约束。

复合索引

索引项是多个的就叫组合索引，也叫复合索引。

复合索引使用时需要注意索引项的次序。

索引对性能的作用

使用索引的优点

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资源的使用。

BatchComplete　SQL批处理完成执行所需的时间。

CPU,Reads，Writes数据列将显示由该事件使用的资源。

3.检测死锁

产生死锁现象，对于系统来讲是一个非常严重问题，尤其对在线事务处理（OLTP）影响非常大，那么查找死锁产生的原也就等同于改善系统性能。

在跟踪定义选择下列事件。

．TSQL,SQL:

BatchStarting正运行的SQL批处理。

．Locks,Lock:

Deadlock死锁本身事件。

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

templog'

4.关闭SQLSERVER重启

5.删掉旧的tempdb文件

一十三、服务器性能监控

调整CPU

　　　　　　要监视CPU的使用情况，必须保障对系统处理非常繁忙的某一天全天进行监视，这样就可以对系统进行配置，从而能处理最繁忙的任务，可以使用SystemMonitor并选择如下的对象和计数器进行监视：

　　　　　　ProcessorObject（处理器对象），%ProcessorTime（处理器时间计数）,选择所有实例可以查看每个处理器的使用情况，以及所有处理器的平均使用率.如果处理器使用率保持在80%或更高，或者经常出现峰值使用率，系统就可能具有CPU瓶颈，可以在系统中添加更多或更快的处理器，这样就可以提高系统性能．

调整内存

　　　　　　如果条件允许，最好是SQLServer独立占用数据库服务器，这就允许SQLServer能够尽可能地使用系统内存，而不用与其他应用程序一起共享系统内存。

　　　　　　　通过SystemMonitor　可以监视如下对象

　　　　　　　．MemoryObject,AvaliableMbytes表示系统中可供进程使用的内存

　　　　　　　．SQLServer:

MemoryManagerObject,TotalServerMemory（KB）SQLServer所分配总内存大小

BufferManagerObject,BufferCacheHitRatio缓冲存储器命中率

如果AvaliableMbytes计数器的值非常小，意味着系统中已经没有足够的物理内存可供使用，必须查看其它计数器确定是否增加物理内存。

如果缓冲存储器命中率低于90%，那么系统通常需要更多的物理内存。

对于数据库内存配置，通常要求设置为固定内存大小，这样可以强制给SQLServer分配内存，提高内存的使用率。

磁盘调整

　　　一．监测磁盘I/O

使用SystemMonitor并选择PhysicalDisk对象，并选用下列计数器:

　　　　　　　DiskReads/sec所选择磁盘每秒所执行的读操作数

　　　　　　　DiskWrites/sec所选择磁盘每秒所执行的写操作

二．分析磁盘指标（可参考制造商规格说书）:

平均寻道时间（毫秒）=[平均寻道（读）+平均寻道（写）]/2

磁盘旋转等待时间（毫秒）=500/[转速（转/分）/60]

磁盘最佳I/O=[1000*0.8]/[平均寻道时间+磁盘旋转等待时间]

RAID0:

I/0操作数=（读+写）

每个磁盘的I/0操作数=I/0操作数/磁盘数量

RAID1:

I/0操作数=[读+（2*写）]

每个磁盘的I/0操作数=I/0操作数/2

RAID5:

I/0操作数=[读+（4*写）]

每个磁盘的I/0操作数=I/0操作数/磁盘数量

RAID10:

如果每个磁盘的I/0操作数大于磁盘最佳I/O数那么磁盘系统存在瓶颈，需要添加磁盘：

　　磁盘个数=I/0操作数/磁盘最佳I/O

数据库调整

一．备份数据库

二．用备份文件重新恢复一个测试库

三．根据《调整方案》记录的问题及改进方法，在测试库中修改，并把实际修改结果记录《调整方案》中.

四．系统测试

五．测试成功后，调整正式数据库。

案例

A公司业务系统上线运行一年后系统速度变慢，而且经常出现死锁现象。

使用SQL事件探查器跟踪发现maxbh表被锁死，查看表索引情况，maxbh表中没有主键。

调整后该表不再出现死锁。

通过实地观察系统使用情况：

销售开票检索商品，开票结算存盘速度较慢。

查看商品检索方案，方案中为了　获取商品的批号，数量等信息使用了视图：

selectspid,pihao,sum（shl）shlfromsphwph　groupbyspid,pihao。

同时为了拦截负库存销售，关联jxdjhz,jxdjmx表，冲减已开票未执行的商品数量，而且商品的过滤条件为模糊查找。

经过分析，视图在每次检索商品时都要对所有的品种分组求和，对系统压力较大，如果这时有出库业务发生，记帐回写sphwph表，开票将会产生资源等待。

针对这种情况，作出如下调整：

1.取消视图，直接在方案中关联sphwph表，通过商品过滤，减少分组求合的运算量，减少与出库记帐冲突的概率。

2.新建一业务临时表，在销售开票时，另存一