Post reply

Anyway to find the total number of records inserted/deleted/updated in tables using sys.stats or sys.indexes views

  • July 8, 2017 at 9:26 am

    #324386

    Hi,

    Is there anyway to find the total number of records inserted/deleted/updated in tables using sys.stats or sys.indexes views to understand which tables are highly transnational?

    Wanted to formulate a plan to check when the SQL server plan changes against procedures and how to prevent this. This is one of the options to explore further.

    Kindly suggest

    Thanks.

  • July 8, 2017 at 12:07 pm

    #1950073

    SQL-DBA-01 - Saturday, July 8, 2017 9:26 AM

    Hi,

    Is there anyway to find the total number of records inserted/deleted/updated in tables using sys.stats or sys.indexes views to understand which tables are highly transnational?

    Wanted to formulate a plan to check when the SQL server plan changes against procedures and how to prevent this. This is one of the options to explore further.

    Kindly suggest

    If you want to find row counts involved in your statistics, check out sys.dm_db_stats_properties.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • July 10, 2017 at 7:21 am

    #1950160

    SQL-DBA-01 - Saturday, July 8, 2017 9:26 AM

    Wanted to formulate a plan to check when the SQL server plan changes against procedures and how to prevent this.

    Kindly suggest

    I'll kindly suggest that, except in very rare cases, preventing plan changes is a form of "death by SQL" especially in the face of any scalability.  There are times when you may want to optimize for a certain condition or force the use of a saved plan (usually because of the bad form of "SQL Injectio") but that's frequently akin to putting a Band-Aid on a stab wound.  It's far better to analyze the code and figure out the best way to write the code to accommodate such eventually needed changes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 12, 2017 at 9:12 am

    #1950571

    For insert and delete you will need to take snapshots of sys.sysindexes before and after insert or delete. Look for rowcnt or rows columns. Alternatively you can also check sys.indexes-sys.partitions-sys.allocation_units or sys.dm_db_partition_stats.

    For updates look for rowmodctr (it counts down) in sys.sysindexes

    But this all not related to trans-nationality of table usages.

  • July 12, 2017 at 11:05 am

    #1950597

    SQL Guy 1 - Wednesday, July 12, 2017 9:12 AM

    But this all not related to trans-nationality of table usages.

    Agreed - I don't think transnationality has any impact on much of anything here. 😉

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply