How to check if a particular table is being used right now?

  • VoldemarG

    Hall of Fame

    Points: 3672

    I am completing an SP that will do massive deletes based on certain criteria from a dozen of tables.

    On average each table is tens of millions of rows, no permanent or tuned indexes on it (it is a staging-swap kind of tables,  temporary transactional data holders) but occasionally heavy selects can be run against them , especially by some confidential, highly covert and moody Ad-hoc query writers.  I want to be able to first check via T-SQL at the beginning of the SP if each table is currently being used (either selected from, locked, or written to -- doesn't matter), and only if it is not to run my further massive deletes.

    How can I do it? In earlier versions I was not able to I believe, but can I do it in 2017?


    Thank you.

    Voldemar likes to play CHESS (and IS good at it!)

  • pietlinden

    SSC Guru

    Points: 62804

    Why not schedule a job and have it run in the middle of the night when nobody's using the database?

  • Ken McKelvey


    Points: 18296

  • Jeffrey Williams

    SSC Guru

    Points: 88545

    If your deletes are setup in smaller batches - with a wait in between each iteration, then it shouldn't impact the users to any large degree.  SQL Server will manage the locks and access to the table(s) as needed.

    The key is to insure your 'massive' delete operation is performed in a batch size that optimizes the delete so it happens quickly enough that it does not block users for too long a time.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • BTylerWhite

    Default port

    Points: 1430

    While you can use the sys.dm_db_index_usage_stats view to identify when an index was last accessed by a user, this also requires that you make assumptions regarding the last time it was accessed to whether or not it will be accessed again. It's possible one of the report writers could have just finished up typing their query and preparing to hit F5, and the programmatic checking of this DMO told you it hasn't been accessed in awhile, the deletes begin, and now the user is waiting.

    There are other ways to identify what's going on in a user's session using sys.dm_exec_sessions in conjunction with sys.dm_exec_requests, but this could potentially create the same aforementioned issue.

    From what it sounds like, there isn't a major urgency to perform these deletes.

    pietlinden wrote:

    Why not schedule a job and have it run in the middle of the night when nobody's using the database?

    I think this would be a great idea, that way you're not having to perform any complex checks within your stored procedure. Would you also be able to lower the batch size of your deletes on these tables to reduce contention? If not everything needs to be deleted all at once, you could possible run the procedure more frequently only deleting TOP (X) rows meeting your predicate criteria.

  • VoldemarG

    Hall of Fame

    Points: 3672

    Thank you!

    dm_db_index_usage_stats  helps.

    Voldemar likes to play CHESS (and IS good at it!)

  • Jeff Moden

    SSC Guru

    Points: 996655

    Finding the last access date and time doesn't tell you if it's currently being accessed.  Why not just check sp_lock results for objects that have locks on them?

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719935

    I'm with Jeff. sp_lock or sys.dm_tran_locks should tell you if anyone has a lock on the table, meaning they're using it.

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

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