SQL Server高级性能调优策略

核心提示：SQL Server在每次重启服务时会重建tempdb数据库如果系统运行过程中 tempdb因需要自动增长了，SQL Serve不会记住增长后的大小，SQL Server高级性能调优策略(3)，重启服务后仍然恢复到初始大小，但如果用户使用了手工调整tempdb的大小，所以使用事先编译好的计划可以节省时间，避免重新编译的

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

-------------上面引自happydreamer()----------------------