none
alter table 添加一个列速度非常慢 RRS feed

  • 问题

  • 该表为分区表,有20亿以上的数据,添加一个列允许NULL值,但是速度非常慢,经过监控,发现如下情况:

    1:该语句没有被其他语句阻塞

    2:由于该语句的执行,磁盘在狂读数据,感觉要一行一行的把该表的数据全部读取出来

    3:监控sys.dm_tran_locks发现,resource_type除了有object hobt外,还有大量的key,即:存在大量的对行的x锁

    也许,慢的原因是3的出现,添加一个列,不应该把行再读出来赛!

    2015年9月7日 12:59

答案

全部回复

  • 1. what's sql version and sp level?

    2. did you assign any value to new column?

    3. how long did it take?

    We do schema changes on partitioned tables with multi billion rows constantly and didn't take too long.

    2015年9月7日 15:51
  • alter table table_name

    add test datetime

    没有为其赋默认值,而且允许为null

    sqlserver2014,还有不同的就是使用了alwayson,分区数量大概有1000个分区

    2015年9月8日 0:49
  • That may slow down process especially you set av group in sync mode with than two nodes.
    2015年9月8日 1:15
  • alwayson一个主副本 两个辅助副本

    两个辅助副本都是异步提交模式 ,没有设置为同步模式

    2015年9月8日 1:40
  • 与统计信息的自动更新是否可能有关系
    2015年9月8日 2:07
  • 跟统计信息无关系,可以看一下errorlog,是否有一些相关信息

    Love SQL

    2015年9月8日 2:10
  • alwayson一个主副本 两个辅助副本

    两个辅助副本都是异步提交模式 ,没有设置为同步模式

    Try set to async for all 3 nodes during the process, may speed up.
    2015年9月8日 2:31
  • errorlog也没有相关警告信息
    2015年9月8日 2:33
  • 主副本设置为异步模式是什么意思呀,可以这样设置么?
    2015年9月8日 2:45
  • You can set all nodes to async, similar to high performance mirror mode.
    2015年9月8日 3:35
  • 监控sys.dm_tran_locks发现 key lock,是这个语句请求的吗?是什么类型的key lock?

    想不想时已是想,不如不想都不想。

    2015年9月8日 4:00
    版主
  • 已测试 都为异步模式也不行 始终很慢 ,在大量读取硬盘数据
    2015年9月8日 4:01
  • 你加的是datetime column,雖然是允许为null,但還是會用到8 byte,因為MSSQL 會給它 default 為 1753-01-01,就算你pass NULL 去給它,它還是需要 8 byte的,所以當你alter table Table_name add Column1 datetime,所有的 paging都要重新收拾一篇。

    如果要快一點的話,可以建立一個新table,包含了這個新增的datetime column,再把data copy into,再改table名,這個可能會快一些。實際所需時間,要試試才能知道。哪一個方法比較快。


    大家一齊探討、學習和研究,謝謝!
    MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD,
    MCT, Microsoft Community Star(TW & HK),
    Microsoft MVP for VB.NET since 2003
    My MSMVP Blog

    請記得將對您有幫助的回覆標示為解答以幫助其他尋找解答及參與社群討論的朋友們。
    Please remember to clickMark as Answer on the post that helps you. This can be beneficial to other community members reading the thread.

    2015年9月8日 4:03
  • 不应该呀,单机环境下,加个DateTime,值为NULL,只是改了下元数据结构,是瞬间的事儿

    ALWAYSON环境没法测试


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2015年9月8日 5:02
  • 既然是异步副本,那就是允许延迟,你把 DB 从 always on 中拿掉出来后个性表结构试试
    2015年9月8日 5:18
  • 建立一个新表,是不得已时最后会采用的一个方案

    如果按照你这种说法,那就没辙了,我试了一下,就算添加的是int类型,同样如此,很慢很慢

    2015年9月8日 5:41
  • 大量的X LOCK 的KEY,就是感觉他要去锁行,所以与另外一个现象(从操作系统监控看来,磁盘达到了60MB左右的读)也是吻合的
    2015年9月8日 6:09
  • 在该alwayson上的该数据库的其他表也测试了,对于没有分区的表,有个3000万的表,有个5亿的表,都是瞬间完成

    所以,我怀疑是否与分区有关,是否与统计信息有关

    2015年9月8日 6:11
  • 2014+ always on 没条件测试

    2008R2 + 镜像,非分区表时不时有做这种加 null 字段的操作,都是没有问题的, always on 在同步副本的处理上,也 传递日志,底层的东西应该和镜像差不多,所以理论上应该跟 always on 没有太大关系

    你把出问题的库,还原成另一个名称的单库测试一下吧,如果操作快,再把这个单库加 always on 来测试

    2015年9月8日 6:12
  • 通过对sys.dm_tran_locks的观察

    我发现一直在大量读取sysrscols sysrowsets两个表,视乎与统计信息有关,所以 我怀疑添加列时,SQLSERVER为了去更新统计信息,造成长时间等待

    2015年9月8日 7:47
  • Shouldn't update stats if new column is not part of index. You may try this, update stats for the table first then add column.
    2015年9月8日 13:05