SQL Server Performance

How many insert, update, delete commands

Discussion in 'SQL Server 2005 Replication' started by cj2610, Apr 18, 2007.

  1. cj2610 New Member

    How can know know how many insert, update and delete command have been applied to each subscriber table for given day in sql transactional replciation. I want to know the growth of each tables (rows) every day. pls help. Thanks in advance.
  2. Akthar New Member

    You can try using Profiler!!!

    AKTHAR
  3. satya Moderator

    Growth in terms of MB then refer to the BOL about calculating the row size, if not you can simply take a rowcount from the table. FOllow Akthar's suggestion to see the activities you required, fyi this will have bit problem with performance if the replication process is resource extensive.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. cj2610 New Member

    Thanks Akthar and satya. I understand both of your ideas. I am looking for some system table stored information to get it quickly. If there is no information like that, then I can workout some idea to get it.

    My situation is this:

    There are 350 tables in the Transactional Replication. I want to know the actual transaction rate of each table every day. Is it something wrong in what I want? Is it NOT an important peace of information to know?

    Profiler idea is OK. I will consider it if no other options as it will take resources.. plus I have to clean it up to get what I want.

    The row count option is also OK, but it will not give me the UPDATE information. More over it may not be more accurate even in case of INSERT and DELETE. Am I right?

    There are some performance counters available but still they give information for the entire replication (total command/sec) etc.. They do not address individual tables.

    -----------------------------------------------------------------------------------
    I am looking for some system table stored information to get it quickly. Some where stored in MSDB or distribution DB or in user DB etc…
    -----------------------------------------------------------------------------------

    Pls let me know your inputs, so that I can go ahead and use with your ideas.


    Thanks
  5. MohammedU New Member

    Check sp_MSenum_logreader procedure and MSlogreader_history table about transaction info...but it will give insert, update and delete info...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. cj2610 New Member

    Thanks MohammedU for your reply. I will make use of your idea for now.

Share This Page