sqlserver大数据归档

概述昨天做了个日常大数据归档，归档700W数据，表字段130左右，字段比较多，分享下！ ----先禁用表的index 1.先获取需要禁用的索引 declare @tname varchar(100) set @tname='orders' select  'alter index '+' '+c.indexname+' '+'on'+' '+@tname+' '+'disable' from ( se

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

总结

以上是内存溢出为你收集整理的sqlserver大数据归档全部内容，希望文章能够帮你解决sqlserver大数据归档所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。