如何删除优化顾问引擎中断遗留的统计信息
问题描述:前一段时间在使用数据库引擎优化顾问进行优化时,设定无限制优化时间,后来发现服务器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 = 扩展存储过程