sql server大数据归档 - MSSQL教程
昨天做了个日常大数据归档,归档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和统计信息更新! 查看本栏目更多精彩内容:http://www.bianceng.cn/database/SQLServer/ (编辑:应用网_镇江站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |