sqlserver大数据归档

昨天做了个日常大数据归档,归档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和统计信息更新!

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值