一、 背景
drop了sqlserver一个大表image类型的列,感觉作为一个ddl语句应该在drop之后会释放空间,但drop后查询表发现并没有缩小,也不像oracle删除表空间中的表后可以复用原空间,插入数据之后数据文件还在不断增大。
二、 如何让sqlserver drop列后释放空间
法一:重建聚集索引(推荐)
1)重建聚集索引
ALTER INDEX PK_Testtab on Testtab REBUILD With (FillFactor = 90 , Online= On);
测试1T表重建聚集索引约15分钟,可以在线REBUILD,期间不阻塞业务。重建完聚集索引后,表变小了,数据文件使用率变低了,但文件大小不变还是1T,需要手动收缩一下。
2)shrink数据文件
测试此过程数据库可写,收缩时间大概为2分钟
USE [Testdb]
GO
DBCC SHRINKFILE (N'Testdb' , 40000)
GO
但是要注意一下,Shrink后碎片可能会很高
3)再次重建聚集索引
这次是为了避免Shrink后碎片率过高
ALTER INDEX PK_Testtab on Testtab REBUILD With (FillFactor = 90 , Online= On);
法二:将数据插入新表后drop原表
适用于删除的列非常大而剩余数据量少的情况,但是会影响业务,需要停机。
法三:DBCC CLEANTABLE(不推荐)
注意该命令全程锁表,大表执行时间非常长(1T表执行25小时还未完成)。感觉比较鸡肋,大表长时间锁表影响业务;小表没啥必要非要回收空间;而且还有类型限制。
- 对于变长列:支持 varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, xml 类型
- 对于定长列:DBCC CLEANTABLE命令不会释放空间
1)创建测试表
CREATE TABLE [dbo].[TestTable](
[ID] [int] NULL,
[Name] [varchar](100) NULL,
[Address] [varchar](1000) NULL
) ON [PRIMARY]
insert一些测试数据
2)drop列前后分别记录page和record数
SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed');
ALTER TABLE [TestTable] DROP COLUMN [Address];
SELECT page_count,avg_page_space_used_in_percent,record_count
FROM sys.dm_db_index_physical_stats(db_id('dbname'), object_id('TestTable'), NULL, NULL , 'Detailed');
结果如下,可以看到删除列后并没有变化
3)执行DBCC CLEANTABLE
DBCC CLEANTABLE ('dbname', 'TestTable', 0)
再次执行前面的查询,可以看到page变小了非常多。
三、drop列发生了什么
drop列的操作不影响现有数据,这只是一个元数据操作。可以使用视图sys.system_internals_partition_columns查看,可以看到is_dropped=1表示列没有消失,只是被标记为已删除:
CREATE TABLE NAME (
id INT IDENTITY PRIMARY KEY,
first CHAR(1000) NOT NULL,
middle CHAR(1000) NOT NULL,
last CHAR(1000) NOT NULL
);
INSERT NAME (first, middle, last) VALUES ('Michael', 'J', 'Swart');
INSERT NAME (first, middle, last) VALUES ('Lester', 'B', 'Pearson');
INSERT NAME (first, middle, last) VALUES ('Mack', 'D', 'Knife');
INSERT NAME (first, middle, last) VALUES ('Homer', 'J', 'Simpson');
ALTER TABLE NAME DROP COLUMN Middle;
INSERT NAME (First, Last) VALUES ('Bartholomew', 'Simpson');
INSERT NAME (First, Last) VALUES ('Lisa', 'Simpson');
查看被删除列信息
select t.name as tablename,
c.name as columnname,
ipc.*
from sys.system_internals_partition_columns ipc
join sys.partitions p
on ipc.partition_id = p.partition_id
join sys.tables t
on t.object_id = p.object_id
left join sys.columns c
on c.object_id = t.object_id
and c.column_id = ipc.partition_column_id
where t.name = 'NAME'
order by c.column_id
查看日志记录
SELECT MAX ([Current LSN]) FROM fn_dblog (null, null); -- 00000599:0000783a:000a
ALTER TABLE NAME DROP COLUMN Last;
SELECT [Page ID],[Current LSN],Operation,Context,AllocUnitName,[Lock Information],Description FROM fn_dblog (null, null) WHERE [Current LSN] > '00000599:0000783a:000a';
输出如下:
Page ID | Current LSN | Operation | Context | AllocUnitName | Lock Information | Description |
NULL | 00000599:00007842:0001 | LOP_BEGIN_XACT | LCX_NULL | NULL | NULL | 2019/08/14 11:50:08:203;ALTER TABLE;0x01 |
NULL | 00000599:00007842:0002 | LOP_LOCK_XACT | LCX_NULL | NULL | HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 6:18099105:0 | |
0001:00000039 | 00000599:00007842:0003 | LOP_MODIFY_ROW | LCX_SCHEMA_VERSION | sys.syscolpars.clst | ||
0001:000000e8 | 00000599:00007842:0004 | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust | NULL | Action 0 (HOBTCOUNT) on rowset 72057594044547072. Leaf page count: 3, Reserved page count: 9, Used page count: 5 |
0001:000000a1 | 00000599:00007842:0005 | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust | NULL | Action 1 (ROWSETCOUNT) on rowset 72057594044547072. Row count: 6. |
0001:00000045 | 00000599:00007842:0006 | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst | NULL | Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 1, mod count: 6 |
0001:00000045 | 00000599:00007842:0007 | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst | NULL | Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 2, mod count: 6 |
0001:00000045 | 00000599:00007842:0008 | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst | NULL | Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 3, mod count: 6 |
0001:00000045 | 00000599:00007842:0009 | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst | NULL | Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594044547072. Column Id: 4, mod count: 6 |
NULL | 00000599:00007842:000a | LOP_HOBT_DDL | LCX_NULL | NULL | NULL | Action 2 (ALTER_HOBT) on HoBt 0x65:100, partition 0x0, rowset 72057594044547072. |
0001:00000045 | 00000599:00007842:000b | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysrscols.clst | HoBt 196608:ACQUIRE_LOCK_IX OBJECT: 6:3:0 ;ACQUIRE_LOCK_X KEY: 6:196608 (02af0b2cf9fe) | |
0001:00000001 | 00000599:00007842:000c | LOP_MODIFY_HEADER | LCX_PFS | Unknown Alloc Unit | NULL | Field m_typeFlagBits |
0001:00000001 | 00000599:00007842:000d | LOP_SET_BITS | LCX_PFS | sys.sysrscols.clst | ||
NULL | 00000599:00007842:000e | LOP_HOBT_DDL | LCX_NULL | NULL | NULL | Action 2 (ALTER_HOBT) on HoBt 0x65:100, partition 0x0, rowset 72057594044547072. |
0001:00000045 | 00000599:00007842:000f | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysrscols.clst | HoBt 196608:ACQUIRE_LOCK_IX OBJECT: 6:3:0 ;ACQUIRE_LOCK_X KEY: 6:196608 (02af0b2cf9fe) | |
0001:00000001 | 00000599:00007842:0010 | LOP_SET_BITS | LCX_PFS | sys.sysrscols.clst | ||
0001:0000012b | 00000599:00007842:0011 | LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst | HoBt 281474978938880:ACQUIRE_LOCK_IX OBJECT: 6:34:0 ;ACQUIRE_LOCK_X KEY: 6:281474978938880 (bc1f56567263) | |
0001:000000b1 | 00000599:00007842:0012 | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.nc | HoBt 562949956108288:ACQUIRE_LOCK_IX OBJECT: 6:41:0 ;ACQUIRE_LOCK_X KEY: 6:562949956108288 (5db225c69ef8) | |
0001:00000001 | 00000599:00007842:0013 | LOP_SET_BITS | LCX_PFS | sys.syscolpars.nc | ||
0001:00000039 | 00000599:00007842:0014 | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.clst | HoBt 281474979397632:ACQUIRE_LOCK_IX OBJECT: 6:41:0 ;ACQUIRE_LOCK_X KEY: 6:281474979397632 (6e51f765b719) | |
0001:00000001 | 00000599:00007842:0015 | LOP_SET_BITS | LCX_PFS | sys.syscolpars.clst | ||
NULL | 00000599:00007842:0016 | LOP_LOCK_XACT | LCX_NULL | NULL | HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 6 PERMISSIONS(class = 1, major_id = 18099105), lockPartitionId = 0 | |
NULL | 00000599:00007842:0017 | LOP_COMMIT_XACT | LCX_NULL | NULL | NULL | 2019/08/14 11:50:08:210 |
0001:00000039 | 00000599:00007848:0001 | LOP_EXPUNGE_ROWS | LCX_CLUSTERED | sys.syscolpars.clst | NULL | |
0001:00000001 | 00000599:00007848:0002 | LOP_SET_BITS | LCX_PFS | sys.syscolpars.clst | ||
0001:000000b1 | 00000599:00007848:0003 | LOP_EXPUNGE_ROWS | LCX_INDEX_LEAF | sys.syscolpars.nc | NULL | |
0001:00000001 | 00000599:00007848:0004 | LOP_SET_BITS | LCX_PFS | sys.syscolpars.nc | ||
0001:00000001 | 00000599:00007848:0005 | LOP_MODIFY_HEADER | LCX_PFS | Unknown Alloc Unit | NULL | Field m_typeFlagBits |
各Operation含义
操作 | 含义 |
LOP_ABORT_XACT | Indicates that a transaction was aborted and rolled back. |
LOP_BEGIN_CKPT | A checkpoint has begun. |
LOP_BEGIN_XACT | Indicates the start of a transaction. |
LOP_BUF_WRITE | Writing to Buffer. |
LOP_COMMIT_XACT | Indicates that a transaction has committed. |
LOP_COUNT_DELTA |
|
LOP_CREATE_ALLOCCHAIN | New Allocation chain |
LOP_CREATE_INDEX | Creating an index. |
LOP_DELETE_ROWS | Rows were deleted from a table. |
LOP_DELETE_SPLIT | A page split has occurred. Rows have moved physically. |
LOP_DELTA_SYSIND | SYSINDEXES table has been modified. |
LOP_DROP_INDEX | Dropping an index. |
LOP_END_CKPT | Checkpoint has finished. |
LOP_EXPUNGE_ROWS | Row physically expunged from a page, now free for new rows. |
LOP_FILE_HDR_MODIF | SQL Server has grown a database file. |
LOP_FORGET_XACT | Shows that a 2-phase commit transaction was rolled back. |
LOP_FORMAT_PAGE | Write a header of a newly allocated database page. |
LOP_HOBT_DDL |
|
LOP_HOBT_DELTA |
|
LOP_IDENT_NEWVAL | Identity’s New reseed values |
LOP_INSERT_ROWS | Insert a row into a user or system table. |
LOP_LOCK_XACT |
|
LOP_MARK_DDL | Data Definition Language change – table schema was modified. |
LOP_MARK_SAVEPOINT | Designate that an application has issued a ‘SAVE TRANSACTION’ command. |
LOP_MIGRATE_LOCKS |
|
LOP_MODIFY_COLUMNS | Designates that a row was modified as the result of an Update command. |
LOP_MODIFY_HEADER | A new data page created and has initialized the header of that page. |
LOP_MODIFY_ROW | Row modification as a result of an Update command. |
LOP_PREP_XACT | Transaction is in a 2-phase commit protocol. |
LOP_SET_BITS |
|
LOP_SET_BITS | Designates that the DBMS modified space allocation bits as the result of allocating a new extent. |
LOP_SET_FREE_SPACE | Designates that a previously allocated extent has been returned to the free pool. |
LOP_SORT_BEGIN | A sort begins with index creation. – SORT_END end of the sorting while creating an index. |
LOP_SORT_EXTENT | Sorting extents as part of building an index. |
LOP_UNDO_DELETE_SPLIT | The page split process has been dumped. |
LOP_XACT_CKPT | During the Checkpoint, open transactions were detected. |
参考
https://wikidba.net/tag/fn_dblog-operations-details/
https://michaeljswart.com/2010/02/removing-columns/
https://www.sentryone.com/blog/aaronbertrand/drop-column-fix
http://www.sqldbadiaries.com/2011/03/03/space-used-does-not-get-changed-after-dropping-a-column/