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和统计信息更新！