经常有人问“我的数据库很慢，有什么办法提高速度呢?”。这是个古老的话题，又是常见的问题，也是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文件

而我们在建立一个用户数据库的时候，也要考虑数据文件放哪、日志文件放哪、用几个数据文件。数据文件访问当然希望尽可能快。但是通常数据文件对于用户具有极其重要，所以一般考虑数据保护和高可用性，将它们放到RAID5中。日志文件呢，它记录了对数库所做的历史记录，它用于校验数据库中的数据完整性。它也应该和你的数据文件分开存储，和数据文件一样事务日志也是会自动增长的，当空间够大的时候事务记录操作得很快。而这时你觉得它太大了，去缩减它，，而你再一操作数据库，它要花费时间去分配空间，如果你不让写入，数据库将被置疑。所以这不是个好办法。你应该通过文件截断或日志备份，这样可以从事务日志中释放空间。这里说的空间是指日志文件内部可用空间，而不是日志文件的大小。日志管理器每秒传输操作可以顺序写入60KB。对调整事务日志最好的建议是放到一个最快的阵列上，一个办法是RAID0+1。增加一个文件组中的文件数，往往会改善性能，因为会创建更多的线程来扫描数据文件。又比如你有两个上百万的表，分别建立索引。如果你放在一个文件组中性能会受很大影响。而比如你建立4个数据文件每个物理磁盘上建立一个，而且把数据文件放到单独的数据文件组,而把每个索引也放到它自己的文件组中。查询性能会显著提高。
   
   
   
    使用DBCC命令来优化
    D B C C命令就像名字的含义一样，本来是为了检查数据库的一致性。但后来D B C C命令变
    成了一些有用的函数。D B C C命令的使用有一些限制。以下就是所支持的命令列表。



    D B C C {
    CHECKALLOC[(database_name [, NOINDEX])] |
    C H E C K C ATALOG [(database_name)] |
    C H E C K TABLE (table_name[,NOINDEX | index_id]) |
    CHECKDB [(database_name [, NOINDEX])] |

    dllname (FREE) |
    INPUTBUFFER (spid) |CHECKIDENT [(table_name)] |
    D B R E PAIR (database_name,DROPDB [,NOINDEX])] |
    M E M U S A G E |
    N E WALLOC[|(database_name[,NOINDEX])] |
    OPENTRAN ({database_name} | {database_id})
    [WITH TA B L E R E S U LTS] |
    OUTPUTBUFFER(spid) |
    PERFMON |
    P I N TABLE (database_id,table_id) |
    S H O W _ S TATISTICS (table_name,index_name) |
    SHOWCONTIG(table_id,[index_id]) |
    SHRINKDB (database_name[,new_size[,'MASTEROVERRIDE')]]) |
    SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS[,CLEAR]} | {THREADS} |
    { L O G S PACE}) |
    T E X TALL [({database_name | database_id} [,FULL | FAST])] |
    T E X TALLOC [({table_name | table_id}[,FULL | FAST])] |
    TRACEOFF(trace#) |
    TRACEON(trace#) |
    T R A C E S TATUS (trace#[,trace#]) |
    U N P I N TABLE(database_id,table_id) |
    U P D ATEUSAGE({0 | database_name} [,table_name [,index_id]]) |
    [WITH NO_INFOMSGS]

    有些D B C C命令需要数据库名，而有些需要数据库I D。你可以观看SQL Server的错误日志
    或运行如下的查询并在S e l e c t语句中用数据库名代替d b n a m e就可得到数据库的I D。
    下载
   
    存储还有一点就是最后使用NTFS格式，NTFS格式的读取速度比FAT32快。其实这是属于SQLServer调优中，系统优化的部分。
   
    说到这补充一点，一般影响SQLServer调优的有4个部分：系统调优占2.5%，数据库调优占17.5%，设计调优占20%，程序可调优的空间最大占60%。呵呵，但是一般DBA拿到一个系统的时候，设计调优和程序可调优是不可控的，应为他不可能去改买来的软件。

2

面先看看索引步骤，这些有利于大家对索引的认识。
    一、堆
    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将会增加到某个大小时，可以自行调整，从而避免性能下降

6