SQL SERVER 优化心得

数据库的优化列表项目大致可以分为如下几点：

1.数据库设计

2.查询设计

3.配置设置

4.数据库管理

下面详细的说一下：

数据库设计：

可以说，数据库的逻辑设计（包括各种表和表间关系）是优化关系数据库的核心。设计好逻辑数据库，可以为优化数据库和应用程序性能打下基础。那么在设计数据库的时候，

我们该关注哪些方面呢？

一 规范化即数据库设计的三范式，简单的说就就是属性值不可再分，所有的非主属性都必须函数依赖于主属性，一个table中列不依赖以另一个table中的非主键的列。但是过度的规范化会对查询不利。二 保持实体完整性和参照完整性可以帮助优化器生成高效的执行计划。三 索引的选择最常见的优化建议，通常也是良好性能的最大贡献者，就是为数据库工作负载实现正确的索引。建立一个正确高效的聚集索引尤其重要。聚集索引决定了表的物理顺序，而所有的非聚集索引在其索引行上保存聚集索引键。这也告诉我们创建非聚集索引前创建聚集索引。保持窄索引，大的聚集索引键尺寸不仅影响自身的宽度，而且还扩大了表上所有的非聚集索引。重建聚集索引的话，不要使用 drop index然后再create。 而是在create index的时候drop_existing 可以避免非聚集索引也被建立两次。不要再频繁更新的列上创建聚集索引，会影响到数据库并行性。索引的建立的关注点如下：·尽量使用窄索引·确保候选列中的数据重复率很低·避免在字符串数据类型入varchar上建立索引·多列索引考虑用高选择性的列作为前沿索引·对于范围查询，聚集索引的效率高；对于点查询，非聚集索引通常更好可以使用SQL Server 2008 提供的动态管理视图，来跟踪确定所创建的索引。检查sys.dm_db_index_usage_stats 中的数据，可以确定哪些索引经常被使用，哪些又是冗余的。查询 sys.dm_db_missing_indexs_details 将显示系统认为缺失的索引。四 存储过程不要以sp_作为前缀SQL Server 会先从master数据库中查找该存储过程

查询设计：

一  使用set nocount on 命令

在每个查询执行完之后，服务器报告所影响的行数。 <number> row(s) affected。这个信息返回给数据库应用程序并增加了网络开销。

二 显示限定数据库对象

由于SQL Server 允许在不同的架构下创建多个相同名称的数据库对象。所以查询的时候，会首先尝试查询用户默认框架下是否存在表。

三 避免不可参数化的搜索条件

会阻止优化器使用where子句中引用索引列上的索引。比如 <>,!=,not exists, not in, like'%abc'. 尝试用 between 代替in。

四 避免where子句上的的算术运算符和函数

也会阻止优化器使用索引。尝试把算术运算符或者函数提到该查询语句的前面。

五 避免优化器提示

使用 with(index =?)明确索引提示， option(loop join)明确链接提示。

六 远离嵌套视图

尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

七 确保没有隐含的函数类型转换

隐含转换姜阻止索引的使用

八 最小化日志开销

对于小的结果集，首选表变量来来代替临时表。表变量没有事物日志开销，不执行事务日志活动，而常规和临时表都执行该活动；没有锁开销，因为表变量被看做局部联想，而不是数据库对象，不存在与常规表和临时表相关的锁开销；没有回滚开销，因为表变量不执行事务日志活动，也就不适用回滚开销。

在一个事物中批量进行一些操作查询。

九 采用重用执行计划的最佳实践

执行计划生成开销的最佳实践可以大致分为两类：

1.高效地缓存执行计划

避免执行非参数化的即席查询。将查询的可变部分参数化并且使用存储过程或者sp_executesql系统存储过程提交参数化的查询。如果已经明确地参数化查询，将它放置在存储过程中可能带来最好的可重用性。因为只需要发送参数和存储过程名称，所以网络流量变小了。而是用sp_executeal将查询作为一个明确参数化的查询来执行，为该查询生成一个参数化的执行计划，从而增加了执行计划可重用性。

2.最小化执行计划的重编译

为了最小化存储过程生成执行计划的开销，必须确保缓冲中的计划不会失效，或者在控制下重编译。为了最小化存储过程计划的重编译，我们可以：

·不要再存储过程中交错使用DDL 和DML 语句，必须将所有DDL 语句放在存储过程的开始

·在存储过程中，避免使用在存储过程之外创建的临时表

·句尾加option（keepfixed plan)可以有效的避免重编译

·避免在存储过程中修改set选项。

·使用optimize for 查询提示。尽管不总是能减少或者消除重编译，但是使用optimize for 查询姜帮助确保你获得发生重编译时锁希望得到的计划。optimize for 查询提示所提供的参数值来编译计划，而不管调用的应用程序传入的参数值。具体用法为 option(optimize for(@参数=？))

 ·使用计划指南 with recompile.使你的查询提示或其他执行计划不需要修改查询或者过程文本。

十 采用数据库事务的最佳实践

查询并发性设计越有效，查询就能更快地完成而不会阻塞另一个查询。在查询中设计事务时需要考虑：

·保持事务范围尽可能的小，在一个事务中，只包含了数据一致性必须被遗弃提交的语句。

·使用 set xact_abort on 确保事务出现错误时终止或者回滚

·从客户代码执行包含事务的存储过程或币查询之后，都要检查打开的事务，并使用  if @@trancout>0 rollback 来回滚sql语句打开的事务

十一 消除或减少数据库游标开销

因为sql server 被设计为使用数据集工作，使用DML 语句 处理多行一般来说比使用数据库游标逐行处理要快得多，如果发现自己使用了游标，那么重新检查一下逻辑以了解是否有办法消除这些游标。如果必须使用游标。则使用 fast_forward 游标类型（快速前向），或者使用 ado.net 中等效的datareader对象。

 

配置设置：

一 affinity mask

use master

exec wp_configure 'show  advanced option','1'

reconfigure

exec sp_configure 'affinity mask'',15

reconfigure

 建议该项保持默认值0， 这允许sql server 使用机器上的所有cpu

二 内存配置选项

建议保持sql server内存配置的默认动态设置。

通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。

启用3GB进程空间：

标准的32位地址可以映射嘴大4GB 内存，因此，32 位windoes 操作系统的标准地址空间被限制为4GB。如果在32位OS 的boot.ini 文件制定 3GB 开关，操作系统只保留1GB 的地址空间，应用程序可以访问到3GB 。这也被称为4GT ， 对此不需要新的API。

在32位 SQL SERVER  中使用4GB 以上内存：

在boot.ini文件中添加

[boot loader]

timeout=30

default = multi(0)disk(0)rdisk(0)partition(1)\winnt

[operating systems]

multi(0)disk(0)rdisk(0)partition(1)\winnt=

"Microsoft Windows server 2008 advanced server"

三 处理器瓶颈

1. DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC 是以特权模式执行的，DPC 时间的百分比为特权时间百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部分。这个总数显示了作为实例时间百分比的平均忙时。

2。%Processor Time计数器　如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

3。% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。特权时间的 % 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。

4。% User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器该值越高越好。如果持续低于80%，应考虑增加内存。注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

四 数据库文件布局

1.将用户数据库的数据和事务日志文件放在不同磁盘上，使事务日志磁盘的磁头顺序前进，不需要被数据文件常用的非顺序I/O随即移动

2.在专用的磁盘上放置事务日志，这可以改进数据保护，如果数据库磁盘失败，将可以进行事务日志的备份。

3 创建一个驱动器池来服务于事务日志的所有sql server 数据库文件。这种驱动器池可以使一个RAID阵列。避免事务日志使用RAID5，因为对于每个写请求，raid5磁盘整列将导致raid1 或者raid 10 两倍的磁盘I/O

4.可以为数据文件选择raid5 ，因为读请求通常是写请求的很多倍

 

数据库管理：

一 保持统计更新

使用配置参数 auto_create statistics ,auto_update_statistics 允许自动维护数据分布统计

二 保持最小数量的索引碎片

在非高峰时期进行定期的数据库碎片整理。rebuild index 去除索引碎片

三 循环使用sql 错误日志文件

exec master.dob.sp_cycle_errorlog 。可以使用一个sqlserver 任务来定期循环使用日志

四 避免自动化数据库功能

auto_close  设置为off  auto_shrink 定期压缩数据库尺寸 。