SQL优化：重新编译存储过程和表

最近发现原来执行很快的存储过程，突然慢了下来，而很多存储过程每天就运行一次，所以打算把存储过程重新编译，另外，考虑到数据在不断变化，所以也要更新表的统计信息，这样能生成比较好的执行计划。

下面是具体的方法：

1、把存储过程标记为需重新编译,存储过程将在下次运行时重新编译

 

DECLARE @sql VARCHAR(max)

SET @sql = '';

select @sql = @sql + 'exec sp_recompile ' + name+';' + CHAR(10)
from sys.objects
where type_desc in ('SQL_STORED_PROCEDURE')
      and is_ms_shipped = 0


--select @sql
EXEC(@sql)

2、强制当前存储过程执行重编译计划  

exec procedurename with recompile  

3、清除所有的在执行计划

dbcc freeproccache
go

只在当前数据库中寻找对象,存储过程和触发器所用的查询只在编译时进行优化。

A. 当前数据库中存储过程、触发器、表或视图的限定或未限定名称。object 的数据类型为 nvarchar(776)，没有默认值 
B. 对数据库进行了索引或其他会影响数据库统计的更改后，已编译的存储过程和触发器可能会失去效率。
   通过对作用于表上的存储过程和触发器进行重新编译，可以重新优化查询
C. 如果 object 是存储过程或触发器的名称，则该存储过程或触发器将在下次运行时重新编译。
   如果 object 是表或视图的名称，则所有引用该表或视图的存储过程或触发器都将在下次运行时重新编译。

sp_recompile procedurename|tablename|functionname |triggername |view  

4、更新统计信息

 

declare @v varchar(max)

select @v = ''

select @v = @v +'update statistics ' +name+';'
from sys.tables

select @v 

exec(@v)