|
昨天做了个日常大数据归档，归档700W数据，表字段130左右，字段比较多，分享下！
----先禁用表的index
1.先获取需要禁用的索引
declare @tname varchar(100)
set @tname='orders'
select? 'alter index '+' '+c.indexname+' '+'on'+' '+@tname+' '+'disable'
from
(
select * from
(
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages)/1024 AS 'Indexsize(MB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
)a
where a.tablename=@tname
--order by [Indexsize(MB)] desc
)c
go
?
--2.禁止上面语句获得索引，但是主键和clustered index别禁用，切记！
?
----删除数据
DBCC DROPCLEANBUFFERS?
DBCC FREEPROCCACHE
go
SET NOCOUNT ON
BEGIN TRANSACTION
while 1=1?
begin
?delete top(20000) from dbo.orders?? with(TABLOCK)
?where ordertime <'2010-1-1'
if @@rowcount<20000
?break?
end
commit
go
?
----索引重建
alter index all on orders rebuild
go
?
基本上很短时间搞定，为了性能，需要完成索引rebuild和统计信息更新！
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
