- 论坛徽章:
- 0
|
本帖最后由 2BeSybPro 于 2012-08-16 02:43 编辑
命令:
reorg rebuild <tblname>
go
注意事项:
1、执行命令前先确认tempdb空间要比目标表的数据空间大。(可以用sp_spaceused <tblname>查看目标表的数据空间大小)。
>> user DB instead of tempdb
>> reorg rebuild requires additional disk space equal to the size of the table and its indexes.
2、此命令运行时占用大量I/O资源,因此必须在系统空闲时再操作。
>> reorg rebuild table holds exclusive table lock, it will block other processes accessing the same table, so it must be done during maintenance window
>> reorg rebuild requires that you set the database option select into/bulkcopy/pllsort to true and run checkpoint in the database, which breaks the transaction log dump
3、若表上有cluster index,则reorg rebuild是按照表的cluster index的排列顺序对数据整理,所以在运行reorg rebuild前一定要保证cluster index没有错误,否则会造成数据丢失(虽然ASE12.5的cluster index损坏几率现在降到了最低)。最保险的办法是在reorg rebuild之前,对表进行DBCC检查。
4、reorg rebuild原则:
1)、只要对DataRows和DataPages的表进行reorg rebuild,AllPages表由于其锁的特性,可以不要进行此操作。
>> you can run reorg against APL tables in v15.0.3 and v15.7
1> select @@version_number
2> go
-----------
15700
1> select lockscheme('test')
2> go
------------------------------
allpages
1> reorg rebuild test
2> go
Beginning REORG REBUILD of table 'test'.
REORG REBUILD of table 'test' completed.
2)、只需对频繁进行CURD操作的表进行reorg rebuild。
3)、对于满足上述两点特性的表,如果表空间量增长超过30%,则应该做reorg rebuild。
>> It depends on the fragementation ratio, use optdiag or following query to figure it out
>> select left(object_name(id),30),forwrowcnt,delrowcnt from sysstatistics where ....
5、在命令执行中,不要使用sp_who,sp_helpdb等系统命令,否则会锁操作进程。
>> "reorg rebuild table" will block other processes, not vice versa
>> It's recommended to run "reorg rebuild index" which only holds intent lock, and it does not cause severe blocking
6、在命令执行中,如果要查询数据库进程,可用select * from master.dbo.sysprocesses
7、如果有锁,查看一下锁进程的操作语句,命令是:
dbcc traceon(3604)
go
dbcc sqltext(pid)
go
再根据实际情况决定是否kill该进程.
8、对表完成reorg rebuild后,再进行update statistics操作。
>> no need
9. turn off db options and take full dump to resume log dump if needed
|
|