Post reply

how to check the table is being used(selecting/inserting/updating/deleting/locki

  • December 16, 2022 at 3:17 am

    #4126301

    how to check the table is being used(selecting/inserting/updating/deleting/locking) by some sql statement? many thanks!

  • December 16, 2022 at 1:18 pm

    #4126535

    Nothing marks a table as being "used" by queries. You can look at sys.dm_db_index_usage_stats, assuming the table in question has indexes. That will tell you something of the access of the index, however it doesn't differentiate between system queries and user queries. Also, the counters there get reset as described in the link.

    The only way I know to ensure you capture everything would be to use Extended Events to capture all batches that contain the table name, and also all remote procedure calls that contain the table name. It's a messy way to capture query metrics, but it can be done.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 16, 2022 at 4:47 pm

    #4126628

    I'll also tell you to never just up and delete a table.  Instead, rename it and see if the proverbial phone rings.  The job you save will be your own.

    After a full 13 months goes by, you might be able to safely delete it.

     

     

    --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)

Viewing 3 posts - 1 through 2 (of 2 total)

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