sql server添加列为什么很快？sql server中的online 添加非空字段（sql server2012新增），立刻加列
原文：https://www.cnblogs.com/shanksgao/p/3877674.html
转载只是为了方便查阅，添加到自己的笔记知识库。
【1】需求情况
我们都知道,Sql Server在一个数据量巨大的表中添加一个非空栏位是比较费心的,
缺乏经验的DBA或是开发人员甚至可能鲁莽地直接添加导致阻塞相应业务,甚至可能因为资源欠缺造成实例的全局问题.
当然这都是Sql 2008R2及以前版本的情况.在SQL2012中采用了新的实现方式.这里我将对比相应的实现方式给大家做个介绍.并简单说明Sql Server早期版本添加非空列的方法.
【2】添加非空栏位的实现方式
早期版本(Sql Server2008R2及以前)添加非空栏位(要求有默认值)是对表中的所有数据行依次修改调整
我们通过一个简单的实例来看下
（2.1）Sql 2008R2 SP2 Code
Create database tadnull go use tadnull go create table t2(id int not null identity (1,1),dystr varchar(20),fixstr char(30)); go set nocount on declare @batchSize int set @batchSize=1000 declare @i int set @i=0 while(@i<20000) begin if (@i%@batchSize=0) begin if (@@TRANCOUNT>0)COMMIT TRAN BEGIN TRAN end insert into t2(dystr,fixstr)values('aaa'+str(RAND()*100000000),'bbb'+str(RAND()*100000000)) set @i=@i+1 end if (@@TRANCOUNT>0)COMMIT TRAN dbcc ind(tadnull,t2,1) -----find a datapage pageid 21 dbcc traceon(3604) dbcc page('tadnull',1,21,3)-----view the datapage 21
通过DBCC PAGE我们打印其中的一个数据页进行分析.
可以看到图1-1当前数据页的最后修改的日志记录为m_lsn = (28:69:279)
数据页中第一行数据(slot 0)页偏移量0x60,行长度为58
图1-1
添加非空栏位code
alter table t2 add tt int not null default '10' dbcc page('tadnull',1,21,3)-----view the datapage 21
select b.* from sys.system_internals_partitions a join sys.system_internals_partition_columns b on a.partition_id = b.partition_id where a.object_id = object_id('t2');-----查看行数修改情况
可以看到图1-2添加非空栏位后,字节数据页最后修改的LSN号由m_lsn = (28:69:279)变为了
m_lsn = (43:432:186),数据页中首行长度也由58变为了62正好是一个int数据类型长度.
当然我们也可以看到添加过程中会有大量的key锁出现.图1-3
(可以通过DMV查看,或者Xevents,trace profiler等探究)
从特定的DMV中,我们看到新加列的修改行数为200000,为表的数据行数,这也反应的数据修改为逐行修改.图1-4
图1-2
图1-3
图1-4
由此我们可以看到,在sql2008R2及以前版本中添加非空栏位会对表中数据逐行操作.操作执行成本很高.
接下来我们再看看sql2012采用的新的实现方式.
（2.2）Sql 2012 SP1 code
Create database tadnull go use tadnull create table t2(id int not null identity (1,1),dystr varchar(20),fixstr char(30)); go set nocount on declare @batchSize int set @batchSize=1000 declare @i int set @i=0 while(@i<200000) begin if (@i%@batchSize=0) begin if (@@TRANCOUNT>0)COMMIT TRAN BEGIN TRAN end insert into t2(dystr,fixstr)values('aaa'+str(RAND()*100000000),'bbb'+str(RAND()*100000000)) set @i=@i+1 end if (@@TRANCOUNT>0)COMMIT TRAN dbcc ind('tadnull','t2',1)--------find a data page (pageid 120) dbcc traceon(3604) dbcc page('tadnull',1,120,3)
通过DBCC PAGE我们打印其中的一个数据页进行分析.
可以看到图2-1当前数据页的最后修改的日志记录为 m_lsn = (33:155:157)
数据页中第一行数据(slot 0)页偏移量0x60,行长度为58
图2-1
添加非空栏位code
alter table t2 add tt int not null default '10' dbcc page('tadnull',1, 120,3)-----view the datapage 120 select b.* from sys.system_internals_partitions a join sys.system_internals_partition_columns b on a.partition_id = b.partition_id where a.object_id = object_id('t2');-----查看行数修改情况
通过图2-2可以看出,页的最后修改的LSN记录m_lsn = (33:155:157)行长度 Length 58与添加栏位前一致！这与sql2008R2截然不同.
通过图2-3可以看出新增栏位的修改行数为0,这反应了添加新行的修改非逐行修改.
由以上两个图我们可以推断出添加新的非空栏位并非在新数据页上逐一操作,而是元数据级的操作.
感兴趣读者可以通过Xevent,Trace Profiler的锁捕捉来进一步验证.
图2-2
图2-3
在添加完后的打印数据页120的内容中我们可以看到图2-4中栏位号4名称dd已经被添加,但其物理长度为0,及行中栏位值未添加到此数据页上
图2-4
问题来了,那么这个栏位就永远不会去填充数据页而只是标识吗?
我们简单修改一下120页中的一条数据然后再打印此页内容
修改CODE
update t2 set dystr='shanksgao1111' where id=1 checkpoint dbcc page('tadnull',1,120,3)
由于我得屏幕不能完全打印单页中我们所需的内容,此页没截图,直接输出.截取其中需要信息
可以从红色的字体中看到,此页上的LSN变化为m_lsn = (157:336:2),第一行长度（id=1）变为了62(58+4)虽然我只是修改了变长栏位dystr但变长长度和以前一样.
但新添加的非空栏位的值也添加到此页上来. Slot 0 Column 4 Offset 0x26 Length 4 Length (physical) 4.
******************************************打印数据页内容****************************************** m_freeData = 7640 m_reservedCnt = 0 m_lsn = (157:336:2) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -1896544491 DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x1d9a Length 62 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 62 Memory Dump @0x000000002F5EBD9A 0000000000000000: 30002a00 01000000 62626220 20203838 39373839 0.*.....bbb 889789 0000000000000014: 30202020 20202020 20202020 20202020 20200a00 0 .. 0000000000000028: 00000400 0001003e 00736861 6e6b7367 616f3131 .......>.shanksgao11 000000000000003C: 3131 11 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 id = 1 Slot 0 Column 2 Offset 0x31 Length 13 Length (physical) 13 dystr = shanksgao1111 Slot 0 Column 3 Offset 0x8 Length 30 Length (physical) 30 fixstr = bbb 8897890 Slot 0 Column 4 Offset 0x26 Length 4 Length (physical) 4 dd = 10 ******************************************打印数据页内容******************************************
（2.3）总结
通过上面的实例我们可以看到,在sql2012中
当我们添加一个非空栏位时,实际对相应数据页上的操作并非逐一修改添加.因此执行速度非常快,对线上业务影响很低.
但由于其并非实际填充数据页使的Sql Server在应用,维护数据页时进行额外的工作.
对此Sql Server采用了较为平滑的处理方式.当更新某(些)行时,再进行页中数据填充,降低了影响粒度.更友好地满足了线上业务的需求.
注:此方式添加新的非空栏位时不包括Blob数据类型.
添加行版本戳(rowversion/ timestamp)不支持.
【3】早期版本调整方式
（3.0）一般情况
我们可以看到sql2012的改进,但早期版本如果维护该如何操作?
早期版本中添加非空栏位针对数据量较小的表还好,但是针对大表由于其添加非空栏位的实现特性,致使阻塞发生的几率明显加大.因此针对大表我们必须谨慎进行.
a 如果有能调整的时间窗口则可以先预估下执行时间(相似环境中测试)然后时间窗口中操作.
b 如果没有可供调整的时间窗口而需求又看似急茬,这样我们可以采用如下步骤循序渐进的调整降低其影响的粒度.
（3.1）添加非空栏位
1 添加一个带默认值得可为空栏位
alter table t2 add col5 int default 100
2 添加一个约束使其col5不可为空,但不检查现有数据
ALTER TABLE t2 WITH NOCHECK ADD CONSTRAINT CK_NOTNULL CHECK (col5 IS NOT NULL)
3 轮询批量修改现有数据,更改现有col5为默认值
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE TOP(2000) t2 SET col5 = 100 WHERE col5 IS NULL
IF @@rowcount < 2000
BEGIN
COMMIT TRAN
BREAK;
END
COMMIT TRAN
END-------------限制数据量修改,尽量避免锁升级
（3.2）无法使用新特性时，如何添加行版本戳
由上面的内容我们已经知道添加行版本戳(rowversion)时无法使用新特性的,那面对大表DBA该如何操作呢？如果有相应时间窗口那是最好,如果没有,DBA需要和研发沟通利用sql server相应知识变向调整.
这里我借鉴与sql 2012 online添加非空栏位时类似的实现方式但需要研发同事配合
大表中添加行版本戳
1 创建一个与原表SCHEMA完全一致的表
2 创建一个视图为原表union all 新表
3 需研发同事配合调整访问实现方式
a 读访问从视图
b 插入操作插入到新表
c 修改操作原表移动到新表
4 采用批量轮询修改移动
5 移动完毕后调整表名称(原表改随意名,新表调整为原表名,告知研发调整,删除视图与原表)
（3.3）如何快捷的给大表添加列
如果研发无法配合调整,如此情况下就比较麻烦了.此时DBA需要一个时间窗口.
为了更迅捷地将相应栏位加上减小阻塞时间我们可以/尽量采取如下一系列的方式来缩短窗口时间.
1 选择实例中相关对象负载最低的时间段进行
2 为执行操作提供尽量多的资源来确保其尽快完成.
如 调整加大buffer pool的大小
操作前腾出足够的内存供其使用
Checkpoint Dbcc dropcleanbuffers -----注意这些操作需保证实例应用在可接受的情况下进行
3 执行的会话中提前获取表级排他锁.(由于添加行版本戳本身也是逐行添加,会话中提前获取表的排他锁可以减小总共获取锁的次数进而加快执行速度)
begin tran ttt update t2 with(tablockx) set dystr='aaa' where 1=0 /*调整代码*/ alter table t2 add rv rowversion commit tran ttt
