如何删除优化顾问引擎中断遗留的统计信息

    问题描述：前一段时间在使用数据库引擎优化顾问进行优化时，设定无限制优化时间，后来发现服务器CPU和内存资源被高度占用，导致正常的访问无法进行，网站访问变慢，远程连接太卡，因此强制中断数据库引擎优化顾问的进程。但是这样导致了一个困扰我多时的问题。正常情况下，数据库引擎优化顾问会在优化过程中产生假设索引和统计信息，在优化完毕后会自动删除这些假设索引和统计信息。在进程被强迫中断后，这些以_dta_index的假设索引和以_dta_stat_的统计信息被驻留在数据库中，并且通过SQL server manage studio 管理工具的图形界面和drop tableName.STATISTICS 无法删除统计信息。 

     后来在http://msdn.microsoft.com/zh-cn/library/ms190172%28v=sql.90%29.aspx中看到一句话"如果数据库引擎优化顾问进程退出并留下应用程序生成的对象，您可以使用下表中列出的对象名前缀创建搜索这些对象的查询。"并根据"区分假设的和真实的应用程序生成的对象“方法，执行以下语句

 

select is_hypothetical ,* from  sys.indexes
where is_hypothetical  = 1 and name like '_dta%'

 

select A.Name,B.name from sys.stats A,sys.objects B
where A.name like '_dta%' and A.object_id =B.object_id
order by B.name 

      查询出要删除的索引和统计信息。接下来通过拼接删除索引语句方法生成drop语句

 

 

SELECT B.name tableName ,'DROP INDEX '+ B.name +'.' + A.name dropIndexSql
FROM    sys.indexes A, sys.objects B
WHERE  is_hypothetical  = 1 and A.name LIKE '_dta%'  

 

SELECT B.name tableName ,'DROP STATISTICS '+ B.name +'.' + A.name dropSTATISTICSSql
FROM    sys.stats A, sys.objects B
WHERE  A.name LIKE '_dta%'  and A.object_id =B.object_id

     将查询结果拷贝到查询分析器中执行，解决了半个月以来一直无法搞定的问题。以下是一些参考资料：

 

---

默认对象名前缀

---

 

优化数据库的结果是，数据库引擎优化顾问可以创建带有下表中列出的前缀的对象：

对象类型	默认对象名前缀	示例
索引	_dta_index_	_dta_index_dta_mv_1_7_1150627142_K2
统计信息	_dta_stat_	_dta_stat_2041058307_2_5
视图	_dta_mv_	_dta_mv_3
分区函数	_dta_pf_	_dta_pf_1043
分区方案	_dta_ps_	_dta_ps_1040

 

sys.object 表中type字段存储的值表示的含义 

xtype 的表示参数类型，通常包括如下这些 
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
L = 日志 
FN = 标量函数 
IF = 内嵌表函数 
P = 存储过程 
PK = PRIMARY KEY 约束（类型是 K） 
RF = 复制筛选存储过程 
S = 系统表 
TF = 表函数 
TR = 触发器 
U = 用户表 
UQ = UNIQUE 约束（类型是 K） 
V = 视图 
X = 扩展存储过程