Rreplication 性能差（转储200万门诊处方zjysb012）
ETLDB性能差（HIS转储200万门诊处方zjysb012）
解决方法：
1.禁用cdc.Hismz_capture
2.停止cdc.Hismz_capture
3.关闭zjysb012，zjysb012 CDC功能
4.启用cdc.Hismz_capture
5.开始：cdc.Hismz_capture
6.开启zjysb012，zjysb012 CDC功能
7.手工执行zjysb012 表的更新操作产生新的cdc，进行自动补录三天数据
（命令：Update zjysb012 set softbbh=' ' where rq>'2016-8-22' and rq<'2016-8-25 18:00'）
2016-8-24 HISMZ 性能严重，8月23日早上6点后的数据未到ETLDB
在发布服务器到代理服务器的参数中增加红色部分参数，调用多线程 CPU处理当前作业遇到的问题，2个小时内性能问题得到解决（同时停掉了CDRDB抽取ETLDB的业务）
-Publisher DSCNX -PublisherDB [Hismz] -Publication [Pub_HISMZ] -Distributor [ETLDB] -SubscriptionType 1 -Subscriber [ETLDB] -SubscriberSecurityMode 1 -SubscriberDB [Hismz] -Continuous -SubscriptionStreams 16 -CommitBatchSize 1000 -CommitBatchThreshold 1000
select * from sys.databases where name='hismz'
--log reuse wait replication
sp_readerrorlog
sp_cdc_help_jobs
SELECT s.name AS Schema_Name, tb.name AS Table_Name
, tb.object_id, tb.type, tb.type_desc, tb.is_tracked_by_cdc
FROM sys.tables tb
INNER JOIN sys.schemas s on s.schema_id = tb.schema_id
WHERE tb.is_tracked_by_cdc = 1 and tb.name='zjysb012'
Select * from sys.dm_cdc_log_scan_sessions
select * from sys.sysprocesses where spid = 340
select * from sys.sysprocesses where spid = 359
SELECT * FROM msdb.dbo.cdc_jobs
select db_id('hismz')
use hismz
dbcc opentran
EXECUTE sys.sp_cdc_change_job
@job_type = N'capture',
@maxscans = 10,
@maxtrans = 1000;
GO
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [start_lsn]
,[tran_begin_time]
,[tran_end_time]
,[tran_id]
,[tran_begin_lsn]
FROM [Hismz].[cdc].[lsn_time_mapping]
order by [tran_begin_time] desc
select * from sys.sysprocesses where spid = 328
use hismz
Select * from sys.dm_cdc_log_scan_sessions
select * from sys.sysprocesses where spid > 50 and dbid = 5 and program_name like '%sqlagent%'
select * from sys.dm_exec_requests where session_id = 153
Declare @cc varchar(max)
select @cc = text from sys.dm_exec_sql_text (0x030005005401BF3C1422FF003BA5000001000000)
select substring (@cc, 78718/2 + 1,125150/2 +1 ),len(@cc)
可查询日志中执行的具体脚本。
sp_repltrans
--zjysb012ls
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'zjysb012ls',
@capture_instance = N'dbo_zjysb012ls'
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'zjysb012',
@capture_instance = N'dbo_zjysb012'
GO
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [start_lsn]
,[tran_begin_time]
,[tran_end_time]
,[tran_id]
,[tran_begin_lsn]
FROM [Hismz].[cdc].[lsn_time_mapping]
order by [tran_begin_time] desc
select * from sys.sysprocesses where spid = 328
use hismz
Select * from sys.dm_cdc_log_scan_sessions
select * from sys.sysprocesses where spid > 50 and dbid = 5 and program_name like '%sqlagent%'
select * from sys.dm_exec_requests where session_id = 153
Declare @cc varchar(max)
select @cc = text from sys.dm_exec_sql_text (0x030005005401BF3C1422FF003BA5000001000000)
select substring (@cc, 78718/2 + 1,125150/2 +1 ),len(@cc)
sp_repltrans
--zjysb012ls
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'zjysb012ls',
@capture_instance = N'dbo_zjysb012ls'
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'zjysb012',
@capture_instance = N'dbo_zjysb012'
GO
EXEC sys.sp_cdc_enable_table
@source_schema= 'dbo' --架构名
,@source_name = 'zjysb012' --启用CDC的表名
,@role_name = NULL --必须字段,默认值
,@supports_net_changes = 1 --启用净变更
,@index_name = N'pk_ZJYSB012_IDKEYGUID'--主键名[]
GO
最新文章
- Django 静态文件配置(static files)
- 微软四十周年 Microsoft’s 40th anniversary
- sublime text3 输入中文的解决方法及注册
- Android JNI学习之javah命令的正确使用（找了好半天才找到的，汉，网上好多说法都没用）
- 【转】终于解决了Apache乱码问题
- JVM 性能调优实战之：使用阿里开源工具 TProfiler 在海量业务代码中精确定位性能代码
- HeadFirst设计模式之组合模式
- js实现小数点后保留N位并可以四舍五入——js对float数据的处理
- Unreal Engine4 蓝图入门
- 关于 "Context" 模式（基于COM思想IUnknown思想）
- ThinkPHP第十七天(隐藏index.php和简短路径配置)
- Java内存回收 - 落日之心的日志 - 网易博客
- 【English】十二、英语句子种类,陈述句、疑问句、祈使句、感叹句
- Powershell-获取MAC地址对应IP信息
- python3字符串
- spring的定时器
- JS笔记汇总
- [HDFS_add_1] HDFS 启动过程分析
- 12-关于DOM操作的相关案例
- Docker的简单介绍及使用