ITPub博客

首页 > 数据库 > SQL Server > Sqlserver2016启用了日志并行,但是实际上某些数据库日志并行并没有生效的问题

Sqlserver2016启用了日志并行,但是实际上某些数据库日志并行并没有生效的问题

原创 SQL Server 作者:lusklusklusk 时间:2021-02-10 14:28:07 0 删除 编辑

https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/sql-server-20162017-availability-group-secondary-replica-redo-model-and-performance

sqlserver2016默认启动并行


禁用并行2种方法
1、DBCC TRACEON (3459, -1),不用重启sqlserver服务
2、-T 3459参数加入sqlserver启动参数


禁用串行再启用并行,必须重启sqlserver
如果有-T 3459参数,则移除该参数,再重启sqlserver
如果是DBCC TRACEON (3459, -1), 则直接重启sqlserver



现象:wondadb3上有20来个数据库做了AG到dbprod126,dbprod126只有24个CPU,发现wondadb3.wondb到dbprod126.wondb的AG传输很慢,把所有数据库移出AG,再重新加入,加入的过程中第一个先加入wondb,在加入其它所有数据库,发现wondadb3.wondb到dbprod126.wondb的AG传输快了很多,发现sqlserver2016的并行设计上有缺陷,数据库数量一多,排后面的几个数据库就只能默认使用串行


官方文档的解释:
Parallel redo thread usage is well covered in "Thread usage by Availability Groups" here.

A SQL Server instance uses up to 100 threads for parallel redo for secondary replicas. Each database uses up to one-half of the total number of CPU cores, but not more than 16 threads per database. If the total number of required threads for a single instance exceeds 100, SQL Server uses a single redo thread for every remaining database.

When the host server has 32 or more CPU cores, each database will occupy 16 parallel redo worker threads and one helper worker thread. It means that all databases starting with the 7th database (ordered by database id ascending) that has joined availability group it will be in single thread redo or serial redo irrespective which database has actual redo workload. If a SQL Server Instance has a number of databases, and it is desired for a particular database to run under parallel redo model, the database creation order needs to be considered. Same idea can be
applied to force a database always runs in serial redo model as well. Again, in SQL Server instance level, the way to switch between parallel redo and serial redo is the TF 3459. All databases in the same SQL Server instance will be switched together. Also, to switch from serial redo to parallel redo by disable TF 3459, a SQL Server service restart is required

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2757107/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    438
  • 访问量
    791960