Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Server 2008 CDC?

Answered SQL Server 2008 CDC?

  • Tuesday, December 11, 2012 11:24 AM
     
     
    Sign In to Vote
    0

    Hello:

    I have CDC (Change Data Capture) enabled for 3 tables. due to heavy workload\INSERT, my CDC Capture Job is set to continous.when i run my DMVs i see that column waittype & last waittype is "WAITFOR" for column TEXT = sp_CDC_scan and this store proc is used by CDC Capture Job. As my DMVs also has join with sys.dm_os_tasks where pending_io_count = 25718193 is showing high number.

    i don't see any blocking and task_state (sys.dm_os_tasks) is SUSPENDED and total_elapsed_time is 732784540.

    i want to know if it is by design or i am missing something over here.

    As per Sanjay Mishra whitepaper "WAITFOR is executed when a scan cycle drains the log completely or when maxscans scan cycles are completed."

    http://msdn.microsoft.com/en-us/library/dd266396(v=sql.100).aspx

    in that case my pending_io_count should have less count or 0.

    The DMV query that i ran is shown below.

    Any help or pointer is greatly appreciated.

    Thanks

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    DMV Query:-

    SELECT
    REQ.session_id,SQLT.text,REQ.start_time,REQ.status AS REQ_STATE,REQ.total_elapsed_time, REQ.wait_type,REQ.command,REQ.blocking_session_id,
    REQ.last_wait_type,REQ.wait_resource,

        OT.task_state,
        OT.pending_io_count,
        OT.pending_io_byte_count,
        OT.pending_io_byte_average,
        QP.query_plan
      FROM sys.dm_exec_requests REQ
      CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) as SQLT
      OUTER APPLY sys.dm_exec_query_plan(REQ.plan_handle) as qp
      JOIN sys.dm_os_tasks OT
      ON REQ.task_address = OT.task_address
      ORDER BY REQ.session_id DESC
    GO

     

All Replies

  • Tuesday, December 11, 2012 3:04 PM
     
     Proposed Answer
    Sign In to Vote
    1

    Hi,

    Read the below link to know details:

    http://www.codeproject.com/Articles/166250/Microsoft-SQL-Server-2008-Change-Data-Capture-CDC

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Proposed As Answer by David Gutter Friday, December 21, 2012 8:33 AM
    •  
     
  • Tuesday, December 11, 2012 3:51 PM
     
     
    Sign In to Vote
    0

    that link is not relevant to this questions.

    Thanks

     
  • Tuesday, December 11, 2012 4:04 PM
     
     
    Sign In to Vote
    0

    I am not sure I understand exectly what you are asking. When the CDC Capture Job is set to continous (it is still polling), the stored procedure manage its own wait loop using a WAITFOR between scans, so I would expect it to show up as supended until the WAITFOR operation times out and then it will begin another scan.

    Bill

    William F. Kinsley

     
  • Tuesday, December 11, 2012 4:14 PM
     
     Answered
    Sign In to Vote
    1

    The last wait type of "sp_cdc_scan" is in "WAITFOR" status only becuase the capture job will run continiously becuase to scan the log files and shiping changes to CDC tables.

    The pending_io_count defines --> the Number of physical I/Os that are performed by this task. So in this case the number should not be zero.

    total_elapsed_time defines here, the start time of your CDC Job. As per my knowlede what you are reporting here is an expected behaviour on CDC operation.

    Please refer the below links:

    http://technet.microsoft.com/en-us/library/bb522547(v=sql.100).aspx

    http://msdn.microsoft.com/en-us/library/cc645591(v=sql.100).aspx

    If you really want to check that your operation is completed or not plz run Select statement on CDC tables.

    Thanks,

    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    • Marked As Answer by aaditya2000 Tuesday, December 11, 2012 4:45 PM
    •  
     
  • Tuesday, December 11, 2012 4:36 PM
     
     
    Sign In to Vote
    0

    i am aware of WAITFOR & its behavior when CDC Capture job is continous. my question is in regards to pending_io_count which is increasing and SUSPENDED state that i can relate to WAITFOR.

    Thanks

     
  • Tuesday, December 11, 2012 4:45 PM
     
     
    Sign In to Vote
    0

    Thank you Satish, thats explain lot.

    Thanks