程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> sql server大數據歸檔

sql server大數據歸檔

編輯:關於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和統計信息更新!

查看本欄目

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved