在建立一个新的实例后，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拿到一个系统的时候，设计调优和程序可调优是不可控的，应为他不可能去改买来的软件。
面先看看索引步骤，这些有利于大家对索引的认识。
一、堆
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