论坛里经常有人问“我的数据库很慢，有什么办法提高速度呢?”。这是个古老的话题，又是常见的问题，也是DBA们最想解决的问题之一。我想就SQLServer调优大家一起论一论，如果可以的话尽量发表自己观点，如果有转帖就只要地址就可以了。调优可以从多个方法可面调优，一般可以调优的多个部分。下面我先就存储方面起个头。
在建立一个新的实例后，mssql会先生成master,model,msdb,tempdb 四个系统数据库,就master，model，msdb这三个系统数据库建议最后与用户数据库分开存储，一般系统数据库应该特别小心。主数据库保护着自己及其他数据库的分类信息、配置信息、用户数据库信息、作业信息、还有数据库模版。一旦系统数据库受到破坏，整个实例就有可能崩溃。
而tempdb这个临时数据库，它对性能的影响较大。tempdb和其他数据库一样可以增大，可以缩小。当数据文件需要增长的时候，通常不能保持剩余部分的连续性。这时文件就会产生碎片，这种碎片会造成性能下降。这种碎片属于外来性碎片。要阻止在tempdb中产生外来性碎片，必须保证有足够的硬盘空间。一般将tempdb的容量放到平均使用容量。而你也应该允许tempdb自动增长，比如你有个一个超大的join操作，它建立了一个超过tempdb容量的时候，该查询将失败。你还要设置一个合理的单位增长量。因为如果你设得太小，将会产生许多外来性碎片，反而会占用更多资源。sqlserver调优最有效的做法之一，就是把争夺资源的操作独立出去。tempdb就是一个需要独立出去的部分而tempdb和其他系统库一样是公用的，是存取最可能频繁的库，所有处理临时表、子查询、GROUP BY、排序、DISTINCT、连接等等。它最适合放到一个具有快速读写能力的设备上。比如RAID0卷或RAID0+1卷上。移动tempdb的方法：
1、用Enterprise Manager或sp_helpdb查看tempdb现在存放的位置 2、用 alter database tempdb modify file(name='tempdev',filename='newpath\newfilename',size=20mb) alter database tempdb modify file(name='templog',filename='newpath\newfilename',size=20mb) 3、关闭sqlserver重起 4、删掉旧的tempdb文件 存储还有一点就是最后使用NTFS格式，NTFS格式的读取速度比FAT32快。其实这是属于SQLServer调优中，系统优化的部分。
说到这补充一点，一般影响SQLServer调优的有4个部分：系统调优占2.5%，数据库调优占17.5%，设计调优占20%，程序可调优的空间最大占60%。呵呵，但是一般DBA拿到一个系统的时候，设计调优和程序可调优是不可控的，应为他不可能去改买来的软件。 下面先看看索引步骤，这些有利于大家对索引的认识。 一、堆 1、sqlserver在sysindexes表中查到对应的数据行 2、读取indid值(应为堆没有索引所以是0)后，sqlserver开始读取firstIAM值，获取堆的IAM的第一页(8KB)的位置。(IAM将堆的各个区域联接在一起) 3、sqlserver根据IAM提供的区域地址，一个区域一个区域的查找，一个数据页一个数据页的查找，直到获取所需的数据为止。
二、簇索引 1、sqlserver在sysindexes表中查到对应的数据行，找到indid为1后，sqlserver开始读取root列的值。(列值是根页面的地址) 2、找到根页面后开始搜索，比如要搜索的表是10条记录一页，这里是找“1981”这个值，将索引的值“1981”与根页面的索引比较。由于“1981”是在1900到2000之间。所以sqlserver开始搜索1900所在的中间页。 3、找到“1900”所在的中间页后，将索引值“1981”继续与中间页的索引比较，由于“1981”是在1980到1990之间。所以sqlserver开始搜索1980所在的数据页。(注意这一步是根据中间页找数据页) 4、找到“1980”所在的数据页后，将索引值“1981”继续与数据页的索引比较，很快就可以在这个数据页上找到“1981”的数据行了。
三、非簇索引 1、sqlserver在sysindexes表中查到对应的数据行，找到indid为后，值为2到251后，sqlserver开始读取root列值。 2、找到根页面后，将“1981”与根页面的索引比较，由于“1981”是在1900到2000之间。所以sqlserver开始搜索1900所在的中间页。(注意这一步是根据中间页找叶页面) 3、找到中间页后，将索引值“1981”继续与中间页的索引比较，由于“1981”是在1980到1990之间。所以sqlserver开始搜索1980所在的叶页面。 4、找到“1980”所在的叶页面后，继续叫索引值“1981”与叶页面上的关键字比较，在叶页面上找到关键字为“1981”的数据行ID。 5、根据数据行ID提供的数据页和数据行信息，定位到指定的数据页和数据行，找到“1981”这条记录是“晶”的。 堆在数据表小于8K的时候访问速度最快，它不需要去找索引，应为当你的数据本来就在一个页里也没有必要用索引。簇索引，在使用簇索引查询的时候，区块查询是最快的，如用between，应为他是物理连续的，你应该尽量减少对它的updaet,应为这可以使它物理不连续。非簇索引与物理顺序无关，设计它时必须有高度的可选择性，可以提高查询速度，但对表update的时候这些非簇索引会影响速度，且占用空间大，如果你愿意用空间和修改时间换取速度可以考虑。如果在视图上建立索引,那视图的结果集就会被存储起来，对与特定的查询性能可以提高很多，但同样对update语句时它也会严重减低性能，一般用在数据相对稳定的数据仓库中。好，为什么update会影响索引，打个比方:1 2 3 4 5 6 7 8 |1 2 ..它们在insert后是物理连续的，每个数字代表一条数据，一条数据1K，分割符前正好填满一页，在做查询时由于不需要指针跳转，所以效率是最佳的，而这是update了3把它的数据量改为了1.2K，超过原来的一页(8K)的大小，这时sqlserver会： 1 2 4 5 6 7 8|1 2.....| 3(1.2k) 将3放到最后面可以插入的空间去。而2的指针还是指向3,如果这时你再检索，当检索到2的时候，物理指针将跳转到3上，然后再跳转回4。如果看懂的话，应该明白了为什么不提倡update索引过的列，不提倡使用varchar类型的列当索引。应为varchar是变长的，如果你频繁的update它，你的索引会事得其反。而sqlserver里也提供了填充因子来减少来自这方面的影响，比如你的因子为20%，当插入数据时,发现这个页底于20%的可用空间，sqlserver不会再继续插入这一页而是申请新的一页存储如：1 2 3 4 5 6 | 7 8 1 2 .. 当你再update 3为1.2K的时候将不会将3分到其他页上。那是不是填充因子越大越好呢，不是的，如果太大，浪费空间不算什么，主要是会影响查询效率，应为在查询过程中最大消耗是来自于读取新页。所以你必须根据你的实际情况，适当设置。 维护索引也是很重要的，update是一个破坏索引的方式，它不但使指针跳转，而且使数据冗余，产生了许多碎片。你就需要用DBCC INDEXDEFRAG 整理指定的表或视图的聚集索引和辅助索引碎片。另外我们知道索引一般会有一个根比如有1 2 3 4 5，那建立索引的时候根是3，取中间的。当我们开始向这个表填加数据，比如这个列是一个顺序增长的如1 2 3 4 5...10000，这时发生了根节点偏移，应为根还是3而，就好象这个树变成了单边树，只往一个方向长。而这个现象是很常见的。而维护索引也很简单，最有效的办法是用DBCC DBREINDEX重建索引。
SQL Server在每次重启服务时会重建tempdb数据库
如果系统运行过程中 tempdb因需要自动增长了，SQL Serve不会记住增长后的大小，重启服务后仍然恢复到初始大小， 但如果用户使用了手工调整tempdb的大小，重启服务SQL Server会把tempdb重建为用户指定大小
测试示例
tempdb初始化大小为8MB
1)使tempdb自动增长 select b.* into #t from sysprocesses a,sysobjects b 重启后使用sp_helpdb 'tempdb' 可以看到tempdb又恢复到8MB 2) 用户使用Alter Database调整为100MB,
USE master GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev,SIZE = 100MB)
重启服务后使用查看tempdb大小就为100MB
以下查询可以看到tempdb的变化 select a.filename,a.name,a.size*8.0/1024.0 as originalsize_MB, f.size*8.0/1024.0 as currentsize_MB from master..sysaltfiles a join tempdb..sysfiles f on a.fileid=f.fileid where dbid=db_id('tempdb') and a.size<>f.size
总结: 当系统自动调整tempdb大小时，对文件的读写将暂时的阻塞 所以如果我们预知tempdb将会增加到某个大小时，可以自行调整，从而避免性能下降
减少执行过程中的重新编译
对有的查询而言，由编译过程产生查询计划所付出的代价占是执行整个查询所付出代价的一部分，所以使用事先编译好的计划可以节省时间，避免重新编译的情况
存储过程recompile的原因
referenced objects, running the sp_recompile system stored procedure against a table referenced by the stored procedure, restoring the database containing the stored procedure or any object referenced by the stored procedure, or the stored procedures plan dropping from the cache.
删除或者重建过程 在过程里使用with recomplie语句，或者在执行时使用 使用sp_recomlile 使存储过程在下次运行时重新编译 恢复数据库时 或者存储过程计划从高速缓存中移出 如果过程引用表的有足够的数据发生变化 如果用户在DDL语句中插入DML语句 SET CONCAT_NULL_YIELDS_NULL
While these recompilations are normal and cannot be helped, DBAs and developers should not assume that all stored procedure recompiles are for normal reasons and should take a proactive approach to determine if they have a recompile problem.
可以使用profile跟踪过程的重新编译 新建一跟踪 事件删除全部，选择存储过程下的 SP:Recompile, SP:Starting, and SP:Completed under Stored Procedure events sP:StmtStarting and SP:StmtCompleted 可以查看哪些语句引起recompile
Microsoft关于最小化应用程序的重新编译问题 Troubleshoting Stored Procedure Recompilaion http://support.microsoft.com/support/kb/articies/q243/5/86.asp
在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。
在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。
二、SQL语句编写注意问题
下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。
1. IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2. 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。
下面是一个采用联接查询的SQL语句，
select * from employss where first_name||''||last_name ='Beill Cliton';
上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
Select * from employee where first_name ='Beill' and last_name ='Cliton';
遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本：
select * from employee where first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1) and last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)
3. 带通配符（%）的like语句
同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句：
select * from employee where last_name like '%cliton%';
这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：
select * from employee where last_name like 'c%';
4. Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
5. NOT
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：
... where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：
... where status <>'INVALID';
再看下面这个例子：
select * from employee where salary<>3000;
对这个查询，可以改写为不使用NOT：
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
6. IN和EXISTS
有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。
第一种格式是使用IN操作符：
... where column in(select * from ... where ...);
第二种格式是使用EXIST操作符：
... where exists (select 'X' from ...where ...);
我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
第二种格式中，子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。
通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。
同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。
