记一次大批量更新导致的问题

问题背景
1. 数据库版本信息：Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) ；
2. 架构：AlwaysON ,DB1读写，DB2只读，最迟半个小时会同步日志。

问题起因：

数据需求方需要对千万级别的数据进行大批量的更新，当晚设置的10000行COMMIT一次，早上过来数据已经更新结束。

问题一 主从不一致（DATAOPS-9502）

因为采用的是alwaysON，下午有开发人员反馈DB2数据和DB1存在差异，影响使用。
预警邮件也进行了报警。

Description: Log backup for database "test1_ag2" on a secondary replica failed because the last backup LSN (0x00000020:000000ad:0001) from the primary database is greater than the current local redo LSN (0x00000020:000000ac:0001). No log records need to be backed up at this time. Retry the log-backup operation later. 
BACKUP LOG is terminating abnormally.

查看主从库LSN,发现主从库lsn不一致：

select * from sys.dm_hadr_database_replica_states
--select * from sys.fn_hadr_backup_is_preferred_replica
SELECT TOP 1 b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn 
FROM msdb..restorehistory a 
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id 
WHERE a.destination_database_name = 'datayesdb' 
ORDER BY restore_date DESC
--第二句语句发现主从库lsn不一致

查找具体原因后发现从昨晚8：30开始数据日志的增长量异常增大，应该是昨天批量更新数据所造成。
alwayson的日志传输对网络的带宽和数据日志的大小都是有限制的，如果主库的新的日志备已完成，主库的checkpointlsn会改变（比如, 之前主库的lsn为00001, 现在可能变成00002），此时如果从库并未完成之前日志备的传输和应用，从库lsn就还是之前主库的lsn（00001），此时alwayson将会报warning，一直等到从库端将主库的日志备完成接收并应用在从库上(此时从库lsnr将变为00002) ，alwayson将会显示正常。
而当累积的日志备过多，从库恢复跟不上过快的主库lsnr备更新的时候，alwayson就会报错了，此时alwayson将不再同步数据。另外早上8点半-10：30也有日志异常增长。

小结：大批量数据更新原则：
（1）领导核实，确定要更新；
（2）确定要更新的数据量（GB），大于30GB/1H的更新需要知会运维DBA修改策略，避免上述问题；
（3）读写频率高的表建议业务低谷进行变更；
（4）完善大批量数据更新最佳实践。

问题二 数据库事务日志已满，原因为“ACTIVE_TRANSACTION”

DB2只能晚上进行修复，为了维持业务的正常进行，所以临时将只读库的业务切到DB1上执行。
核查磁盘空间已满，收缩释放空间后发现tempdb竟然占用超发过了200GB的空间。
监控发现当时存在大量的查询使用到了CTE，还有长事务的操作。
优化：
1. 库表数据清理；
2. 规范查询操作；
3. 及时清理tempdb的数据，监控tempdb和总磁盘空间；
4. 在只读机上操作。

问题三 元数据表被锁，表信息/字段信息的读取线程被堵塞

查询发现堵塞的线程很多都是在打开数据库表树，而堵塞源是一个长事务，在对表进行变更。
解决方案：
连接DB时采用连接lsnr的方式，lsnr负责分发任务，将只读任务分发到DB2上面。