怎样获得SQL Server的优化性能.docx
《怎样获得SQL Server的优化性能.docx》由会员分享，可在线阅读，更多相关《怎样获得SQL Server的优化性能.docx（13页珍藏版）》请在冰豆网上搜索。
怎样获得SQLServer的优化性能
怎样获得SQLServer的优化性能？
---摘自互联网
为了最大程度优化SQLServer的性能，首先您必须确定几个方面。
当这几个方面得到优化时，可以最大程度的提高整个系统性能。
然后您可以针对这几个方面进行分析。
否则，您可能事倍功半。
经验证明，SQLServer的性能提高主要取决于逻辑数据库设计，索引设计和查询设计。
反之，最大的性能问题也往往源于这几个方面的设计缺陷。
如果数据库性能是一个关注焦点，您应该首先着眼这几个方面，因为在此的很小的投资可以带来很大的利益。
而其它系统级的性能问题，例如内存、高速缓存、硬件等，的确值得研究。
经验证明系统在这些方面容量越大，性能越高。
SQLServer对现有硬件资源采用自动方式管理，从而减少系统级的手工调整。
如何提高应用程序中SQL语句的性能
---摘自互联网
∙对频繁使用的语句要进行PREPARE处理
∙可能需要使用“WITHREOPTIMIZATION"打开游标
∙使用游标（cursor）或使用executeinto
∙prepare可节省客户机/服务器间的网络交换量
∙对于大量插入操作使用insert游标
∙明确FET_BUF_SIZE带来的益处（对ISQL,4GL不适用）
∙使用恰当地、最低限制隔离级别
∙使用主键，避免使用ROWID
∙避免不必要的类型转换，要了解”隐藏"的类型转换，
例如selectcol1,col2fromtab1wherecol1>10,
如col1是字符型（char），俞每行的比较中，整数10就会转换为字符型，这条语句应写为：
selectcol1,col2fromtab1wherecol1>'10'
∙decimal类型数据的各种运算和转换代价是高的
∙适当使用UPDATE....WHERECURRENTOF
∙避免频繁的库间切换－－使用分布式查询或通过CONNECT语句进行多处联结。
让服务器来完成这样的工作（但要使工作量最小）
不要Fetch不需要的行、列和只更新被改变的列，这样可以减少软件冲突（可以避免锁住索引项）。
在服务器中进行过滤、连接，而不要在应用程序中进行
在服务器中排序、分组记录（仅在必要时）
数据库设计，索引，存储过程和查询规划对程序性能的影响
---摘自互联网
数据库设计
∙为性能而改变常规设计
有时为了获得所需的性能，减少连接运算而设计非常规数据模型是必要的，但这是最后的解决办法。
∙保证记录的长度较小，当然要小于一个Online页的大小
∙跨越多页的长记录对性能无益
∙varchars是有上下边界的字符串
∙有效地减少存储空间
如一次数据更新增加了varchar值的长度，由于这条记录可能会跨越多页而对性能带来负面影响。
在绝对有必要的情况下才使用BLOBS
BLOBS比其它数据的处理的效率要低
索引
∙避免高度重复索引
高度重复索引会降低性能。
如果觉得有必要在包含高度重复值的列上建立索引，最好与包含唯一值（或近似唯一的其它列）建立一个复合索引，服务器仍可以使用该索引来访问重复列值，而该索引得处理效率更高。
∙精心选择索引
索引的目的是为提供快速的数据访问，创建的索引要对SQL操作提供显著的性能改进，使用查询规划（QueryPlan）和测试确定最佳的索引策略。
∙不要建立过多的索引
太多的索引与不充分、不正确的索引对性能都无益，在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理开支。
7.2版本之后可以反向处理索引，因此任何升序、降序索引的效果都是一样的。
∙对插入操作，使用FILLFACTOR控制索引页中的空间
∙优化器按照索引建立顺序选择索引。
例如：
colA列上建立索引的表和colA,colB,colC上建立的另一个索引效果都是一样的，即对colA过滤时将使用首先建立的索引，而不一定使用最佳索引。
∙只在读/扫描时使用索引
考虑下面的SQL语句
selectcola,colbfromtab1wherecola='ABC1243';
如果colb长度较小（如整形）而cola上有索引，那么可能在cola和colb上建立复合索引会更好些，可通过读取索引项不访问整行而加快查询速度，该方法只对频繁执行的查询有使用价值。
存储过程
∙可以减少客户机/服务器间的通信量
∙通过将复杂的应用逻辑交送给服务器，使用存储过程可以减少客户机/服务器间通信量（当然这将增加服务器的运行开支）
∙避免小的存储过程
存储过程对强制应用逻辑十分有用，但它会导致性能下降，要避免使用只有较少语句的存储过程。
∙避免嵌套的、递归的存储过程
存储过程调用其它的存储过程，这些存储过程又调用其它的存储过程，这对性能是有害的。
∙存储过程可以明显减少系统并行查询的能力
系统不能同时进行多个涉及存储过程的查询，但可以并行执行存储过程各自的SQL语句。
∙每行的检测中，where子句表达式中的存储过程都会被计算。
规划查询
查询规划优化器（由SETEXPLAINON产生）对于观察SQL语句的效率是十分有用的。
可以看到统计、数据分布、索引、数据分片及SQL语句等对查询性能影响的效果。
注意在输出查询规划对性能有极大的影响，因此在计时或运行系统中不要用SETEXPLAINON。
数据加载
---摘自互联网
1.数据加载的方法
∙高性能加载器（并行加载）（HighPerformanceLoader）
十分灵活的加载工具，适用于7.2以后各版，性能高。
∙用户的ESQL加载程序
可提供高性能，但依靠程序的复杂程度。
∙Bulk加载器（BLOADER）
早版本的并行加载工具，运行在老版本的Online中，非正式支持，性能良好。
∙DBLOAD
标准加载工具，可加载ASCII文件，对于少量数据有较好的性能。
∙DBACCESSLOAD语句
加载分界ASCII文件的简单方法。
2.FET_BUF_SIZE对插入游标缓冲区的作用
注意：
FET_BUF_SIZE影响插入游标及fetch游标缓冲区的大小，因而对用ESQL/C/DBLOAD,DBACCESS（不是4GL）加载数据时十分有益。
3加载数据时关闭逻辑日志
关闭逻辑日志时加载数据会更快。
加载数据后再建立索引，参照完整性与约束
索引、参照完整性的存在会使加载性能大大降低。
4.OnLine
应使用原始磁盘空间，不要使用文件系统空间。
原始磁盘空间意味着通过一个字符模式特殊文件（在ls-l命令显示的第一列总出现的一个'c'）访问一个原始磁盘分区，不是指磁盘空间相关联的块模式特殊文件（为ls-l命令显示的第1列为'b'），也不是指卷管理控制设备以及其它任何不是原始磁盘分区的设备。
OnLine的设置对性能的影响
---摘自互联网
系统与原始磁盘空间
应使用原始磁盘空间，不要使用文件系统空间。
原始磁盘空间意味着通过一个字符模式特殊文件（在ls-l命令显示的第一列总出现的一个'c'）访问一个原始磁盘分区，不是指磁盘空间相关联的块模式特殊文件（为ls-l命令显示的第1列为'b'），也不是指卷管理控制设备以及其它任何不是原始磁盘分区的设备。
更新统计
这是十分重要的。
当相关各列上的数值分布发生明显变化时，适时运行UPDATESTATISTICS命令后能使数据分布和统计数据得到更新。
对建有索引的列运行UPDATESTATISTICSHIGH，对其它所有列使用UPDATESTATISTICSMEDIUM.由于UPDATESTATISTICS处理的内部并行程度低，因而使用多个并发任务进行更新统计会改善全局的吞吐量。
可能需要增加DBUPSPACE环境变量的值。
预读
对非并行顺序扫描（只对数据、只按索引，按索引/数据）十分有用。
通过onstat-p监测。
如果ixda-RA,idx_RA及da_RA的总和与RA-pgsused接近，则要增加预读参数，如果它们的总和远远小于RA-pgsused,则要减小相应参数，通常保持RA_PAGES<=32,并使RA_THRESHOLD大约等于PA_PAGES的一半。
OLTP与DSS
OLTP的特点：
用户多
高事务率
在数据处理方面，事务相对较小
明显的页操作（插入/更新/删除）
通过索引的快速数据访问
高度使用缓冲区快速缓存
并行范围有限
DSS特点
涉及大量数据
很少的写操作（除临时空间和数据加载之外）
用户相对较少
处理大批数据的大量、复杂查询
主要通过顺序扫描访问数据
并行范围广
磁盘分布与分片
磁盘硬件的选项：
传输速度高，定位时间少
大量的小盘比少量的大盘好
避免控制器过载
将Online磁盘空间与其它系统操作分开
文件系统
交换空间
将物理、逻辑日志分开
对于有大量的写操作（插入、更新、删除）的任何Online应用环境应将逻辑和物理日志分别放在不同的磁盘/控制器上。
在放置物理、逻辑日志的磁盘上不要再放其它数据。
将逻辑日志放在不同的磁盘上
避免当前日志于各份日志之间出现冲突
减少磁头移动
如果可能的话，控制原始分区的分配以减少磁头的移动
控制表分割
尽量防止表分为多个段（extent）,这对OnLine5.0及以前的各版十分重要。
避免过多的chunk
即使没有dirty页，大量的chunk也会明显增加检查点的时间。
明智地使用数据分片
不要对小表进行分片
不要对所有的表进行分片
确定数据量大且频繁访问重要表
将表的每个分片放在单独的磁盘上
使用轮转（roundrobin）方式分割索引要小心
OLTP--轮询方式分割数据，分离（也可分割）索引
通常为使OLTP性能最佳，要考虑用轮询方式将使用高的大表分割到不同磁盘的DBSPACE中（为分散多个磁盘间I/O）。
这些表的索引也应明确地生成在特定的dbspace上（与数据不同的磁盘上）。
也可以按表达式进行分片。
DSS--轮转或基于表达式方式分割数据，无索引或尽量少
通常为使DSS性能最佳，考虑哪种方式分割大表最好，是轮询还是表达式方式，对不同的表使用不同的分片方式，其目标是为了平衡总体I/O，并可以使优化器对频繁运行的查询消除对不必要分片的扫描
基于表达式方式分片
表达式应该简洁。
对给定的dbspace，首先使用最有约束力的、限制条件最多的表达式：
例如使用x<=10andx>=1indbspace1,而不要使用x>=1andx<=10indbspace1。
将访问频度高的dbspace放在访问频度低的dbspace前
避免使用进行类型转换的表达式
不要按经常改换值得列进行分片
在分片间进行的移动要付出额外的开销
仔细选择表达式
为了均衡I/O负载，而不是数据量
为了帮助消除查询中无需扫描的段
避免使用REMAINDERIN子句
使用多个数据库临时空间（DBSPACETEMP）
如果DBSPACETEMP列出多个dbspace的话，将大大并行涉及临时空间的操作。
如使用多个临时dbspace，将它们放在与其它活动频繁的dbspace不同、且彼此分开的磁盘上。
OPTCOMPIND
OLTP--设置为0
对OLTP，应避免使用散列（hash）连接，因为该连接方式将大量消耗处理器资源。
DSS--设置为2
对DSS，散列连接可以提供很好的连接性能。
LRUS
最少4，否则等于NUMCPUVPS
缓冲区
对OLTP而言，需要较大的缓冲区池和较高的缓存命中率
对典型的OLTP工作负载，当大量访问在系统的缓存中的页时可以获得最好的性能，为了实现这点需要有大小合适的缓存区。
通过onstat-p可以显示出缓存的效率，它将显示读、写的快速缓存命中百分率。
在可用内存的限制下，要尽可能地提高该数字，通常获得95％以上的读命中率和85以上的写命中率是可能的。
对DSS而言，通常一个较小的缓冲区就够了
对典型的DSS工作负载，多数数据访问是有lightscan扫描完成的，而不通过缓冲池，造成不需要使用大量缓冲池的后果。
内存应由DS_TOTAL_MEMORY分配，以便有足够的内存用于扫描缓冲区，散列连接等。
BufferdLogging与UnBufferedLogging
BufferdLogging比UnBufferedLogging性能好
使用BufferedLogging,逻辑日志缓冲区只在充满时才向磁盘刷新。
使用UnBufferedLogging,每次事务提交都会强制一次日志缓冲区刷新。
注意由于逻辑日志缓冲区对所有的数据库是公用的，所以即使仅有一个活动频繁的数据库使用非缓冲日志也会大大降低使用缓冲日志带来的益处。
BufferedLogging不如UnBufferdLogging安全
由于日志缓冲区充满时才进行刷新，如果系统出现故障，则日志缓冲区的内容会被丢失。
当然发生故障时，缓冲区会良好保存未写向磁盘的提交记录，Online快速恢复将回滚这些事务。
所有数据库自身保持一致，而从应用程序的观点看，应用程序认为已经成功提交的事务实际上却回滚了，这是不一致的。
CKPTINTVL参数
正常操作期间，两个主要的事件会导致检查点发生：
超过检查点时间间隔或是物理日志75％已充满。
在检查点间的工作量决定了系统故障后快速恢复所需要的时间长短。
如果恢复时间十分重要，则应设置检查点间隔以使恢复时间可以接受，否则可以加长时间间隔，从而让系统根据物理日志充满度（75％）来决定何时生成检查点。
连接
客户机－－－－数据库服务器和数据库服务器－－－－客户机
选择最佳的通讯机制
本地客户机：
共享内存和管道
对于与服务器运行在同一主机上的客户机，应选择共享内存（ipcshm）或管道（ipcstr）方式。
管道通常要比共享内存方式更快、更灵活、更安全、避免让本地客户使用网络连接（TCP/IP），因为那样比ipcshm或ipcstr连接性能要大大降低。
远端客户机：
TCP/IP
远端客户要使用TCP/IP,只有在特殊情况下才使用NetwareIPX/SPX进行连接。
改变缓冲区大小（FET_BUF_SIZE环境变量）以及socket缓冲区大小（在sqlhosts文件中设置）观察其效果。
使用前面叙述的方法减少客户机/服务器间的交换量。
轮询线索和网络VP
轮询线索处理从客户机送来的数据
从服务器到客户机的数据是由各自的sqlexec线索来发送的。
一个轮询线索可以处理大约200个典型客户机
数目要依许多因素而定，此处只是个原则数字。
对大量非常活跃的客户，或大量的数据输入可能需要更多的轮询线索。
轮询线索在CPU虚处理器（内联轮询）（inlinepoll）或NETVP中。
任意时刻，只有一个“内联”的协议
内联轮询线索
客户数目越少，性能越好
可以帮助TCP/IP
增加CPUVP额外开支
不可有多于CPUVP数目的轮询线索
用于轮询线索的NETVP
对大量的客户而言，可能会提供更好的性能
想要多少就可以有多少轮询线索
可减轻CPUVP的工作负荷
监视会话
可以用onstat及SMI监视会话
应检查有问题的会话过程
线索的数目
磁盘I/O
内存的使用情况
SQL语句
SINGLE_CPU_VP标志
让系统消除一些互斥操作
如只使用一个CPU虚处理器，并确信不需要再动态增加，一定要设置ONCONFIG文件中的SINGLE_CPU_VP标志为ON。
这样可以让Online消除许多用于内部CPUVP同步的互斥调用。
监视OnLine性能
onstat
命令行工具，显示系统操作不同方面的有关信息
SMI
是通过SQL接口的输出与onstat相近的信息，它可以开发用户的监视工具。
Onperf/Xtree
基于图形（X/Motif）的性能监视工具，可以运行在许多平台上，可以显示OnLine服务器的查询树。
Oncheck
检查表，索引的完整性等等。
操作系统与硬件
---摘自互联网
1.从全局着眼
通常只观察系统性能量度是不够的，需要考虑整个系统如磁盘/控制器、CPU、网络的使用情况。
2.操作系统工具：
sar,vmstat,iostat等
UNIX供应商提供了多种系统监视实用程序，其中包括sar,vmstat,iostat等普通的命令行实用程序，许多供应商还提供了图形化的工具（如HP的GlancePlus）。
使用这些实用程序时应该非常仔细，它们经常报告看似相似的度量，但是不同的供应商甚至同一些供应商不同版本之间的特定量度的确切定义是不完全相同的。
3.磁盘与控制器
使用操作系统监视工具来调整磁盘I/O
试图均衡所有磁盘和控制器I/O，并非只对系统磁盘
RAID5没有大的硬件缓存，则写磁盘的性能就会很低
尽量让磁盘繁密比率低于30％，如果达到60％，会发现性能将受到破坏。
不要过分担心iowait时间，这一量度不易解释其内在含义，常使人误入歧途。
仔细定位原始分区，尽量减少每个磁盘上磁头的移动。
操作系统/硬件镜像可以帮助提高性能，一般比INFORMIX镜像更有助于性能的提高，，但这要依供应商的支持程度而定。
4.网络
不同UNIX供应商对TCP/IP参数提供不同级别的控制，以下是可以对性能起作用的参数
流式缓冲区的数目
对于客户机/服务器的高负载，多一些缓冲区可以改善性能
数据包（Packet）的大小
对客户机经常发送/接收大量数据的可靠网络而言，增加数据包大小可能会提高吞吐量。
对客户机经常发送/接收少量数据的不太可靠的网络，数据包的尺寸小一些可能会好些。
数据包的大小与sqlhosts文件中指定的缓冲区大小相互关联。
5.连接队列长度
同时有许多连接请求时，它会影响连接请求的性能，增加该值可以减少用户连接的重试次数。
6.CPU使用情况
CPU使用情况监测是系统性能调整的重要方面
系统或INFORMIX的CPU使用率高不一定表示出现性能问题
应尽可能利用可利用的资源，这在缺少资源而造成处理延误时可能会成为问题。
可以通过onstat-p监测CPU使用的总体情况
每个VP的CPU使用情况可以通过onstat-gglo来监测
系统总体CPU使用情况可以通过sar,vmstat来监测
应该查找什么：
线索就绪队列（onstat-grea）
如果一直显示有大量线索等待运行（多于10到15个）则表示严重缺少CPUVP资源。
大量闲置的CPU（sar,vmstat等）
如果发现就绪队列缺少CPUVP资源，并且有多余的系统CPU，试着增加另一个CPUVP。
一般不应有多于物理CPU数目的CPUVP，但这一点并非永远成立。
若没有空余的处理器，不要再增加CPUVP。
CPU利用率接近100％时再增加CPUVP，只会使情况变得更糟。
大量使用CPU其它进程
也许这些进程应当在其它主机或者系统闲时运行
CPUVP数小于等于物理CPU数
动态可伸缩结构（DSA）设计目标是让CPU数小于物理CPU数以获得优化的性能，但有的环境下，增加CPUVP使其大于物理CPU数可能会改善性能，真是这样的话，这可能是系统的BUG。
维护应该记录问题，从技术支持处寻求帮助。
升级会员 