March 20, 2020 at 12:08 am
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.
Likes to play Chess
March 20, 2020 at 1:48 am
Why not schedule a job and have it run in the middle of the night when nobody's using the database?
March 20, 2020 at 2:34 pm
March 20, 2020 at 5:11 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 20, 2020 at 5:12 pm
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.
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.
March 20, 2020 at 5:21 pm
Thank you!
dm_db_index_usage_stats helps.
Likes to play Chess
March 20, 2020 at 6:33 pm
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
Change is inevitable... Change for the better is not.
March 23, 2020 at 3:12 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply