Delete large number of records without locking table

I have a sql agent task which deletes anything older than 6 months
However its locking the table, and everything is breaking until it completes the delete

Is there a way to delete with no lock or any other way?

my query is something like (its a simple delete right now, but i need to add the section where it also leaves the row which is referenced by Devices.LastRecordID)

delete from tblTrackingData c
Where GpsDateTime < (now - 6 months)
and c.id not in (select lastRecordId From Devices d  where c.id != d.lastRecordId)

Open in new window



I was considering writing to a temp table all the ID's  and then calling the delete on these ID's however, I thought I would ask first

The current DB size is 100gb, and I would expect the initial run to get rid of 50gb of data, and the app still needs to run while its deleting
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
Tty this:

DECLARE @continue INT
DECLARE @rowcount INT
 
SET @continue = 1

WHILE @continue = 1
BEGIN
    PRINT GETDATE()
    SET ROWCOUNT 10000
    BEGIN TRANSACTION
    DELETE from tblTrackingData c
Where GpsDateTime < (now - 6 months)
and c.id not in (select lastRecordId From Devices d  where c.id != d.lastRecordId)
    SET @rowcount = @@rowcount 
    COMMIT
    PRINT GETDATE()
    IF @rowcount = 0
    BEGIN
        SET @continue = 0
    END
END

Open in new window

0
websssAuthor Commented:
ok thanks, can you explain how this handles the locking issue?
0
MishaProgrammerCommented:
Locking issue:

The first row:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

Open in new window

Last row:
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

websssAuthor Commented:
ok thanks, does this isolation level apply to the whole db or just this running query?
0
MishaProgrammerCommented:
Supposed to use it in you query

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on
DECLARE @continue INT
DECLARE @rowcount INT
 
SET @continue = 1

WHILE @continue = 1
BEGIN
    PRINT GETDATE()
    SET ROWCOUNT 10000
    BEGIN TRANSACTION
    DELETE from tblTrackingData c
Where GpsDateTime < (now - 6 months)
and c.id not in (select lastRecordId From Devices d  where c.id != d.lastRecordId)
    SET @rowcount = @@rowcount 
    COMMIT
    PRINT GETDATE()
    IF @rowcount = 0
    BEGIN
        SET @continue = 0
    END
END
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off

Open in new window

0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
DELETE will *ALWAYS* need a lock. It has to because the operation is supposed to make changes to your table. You cannot make any change (be it an Insert or an Update or a Delete) without a lock. What you can do, however is to reduce the duration of the locks or change the nature of the lock.

What I would suggest is to stage the IDs all records that need to be deleted into a staging table. Then, using the lookup table, break the deletes down into smaller chunks (maybe 10,000 rows each) and wait for a couple of seconds between each chunk. I have outlined this approach in one of my articles: http://www.sqlservercentral.com/articles/T-SQL/72606/

The READ COMMITTED/UNCOMMITTED isolation levels only apply to reads/selects as the name suggests.
2
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If you can afford to have no data in that table for the time of the operation, and have to delete more than about one third of content:
rename the table, recreate empty with the oriignal name - that allows other applications to insert rows without being locked;
then perform your deletion in the renamed table
rename the original table again to something else, rename the table with deleted rows back to the original, and append the rows of the "something else" to it.

if you need to have access to the rows all the time, deleting in batches as described by Nakul is your way to go. Batches can be built by date or ID or just by number of rows, whatever fits best to primary key and other indexes. Whatsoever - delete and insert are the operations with the most cost for the database, so don't expect anything done with ease and low impact for other SQL sessions.
0
Olaf DoschkeSoftware DeveloperCommented:
I second Namul

And in regard to how Mishas first suggestion with SET ROWCOUNT works: You can learn and forget it if you read the discussion at https://www.experts-exchange.com/questions/28944158/When-to-use-a-While-loop.html

I just tested on SQL2017 Express and SET ROWCOUNT still is in effect to limit the number of affected rows, but it's long-announced to deprecate. In detail see https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017

SET ROWCOUNT moved from "a future version" to "next version". So 2017 is the last version supporting SET ROWCOUNT.

So rather change it to the same loop with DELETE TOP 10000:

DECLARE @continue INT
DECLARE @rowcount INT
 
SET @continue = 1

WHILE @continue = 1
BEGIN
    PRINT GETDATE()
    BEGIN TRANSACTION
    DELETE TOP 10000 FROM tblTrackingData c
Where GpsDateTime < (now - 6 months)
and c.id not in (select lastRecordId From Devices d  where c.id != d.lastRecordId)
ORDER BY GpsDateTime ASC
    SET @rowcount = @@rowcount 
    COMMIT
    PRINT GETDATE()
    IF @rowcount < 10000
    BEGIN
        SET @continue = 0
    END
END

Open in new window


Bye, Olaf.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
One good way to avoid that the necessary locks will affect all table, go for an strategy of table partitioning. If you partition your table by date, then you just need to delete the partitions that you don't need anymore. In your case, the last 6 months.
1
websssAuthor Commented:
thanks all
The DB is always on, we have people all around the world using it, so there is no "overnight" for us.

Table partitioning does look interesting but that might be more of a long game as we would need time to implement this and i need something quicker for an interim solution

so as i've understood, the sql code should first get a list of ID's (top 10000)
Then put in a temp table

Then delete these ID's, and pause for 5 seconds before running the query again
?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The DELETE TOP 10000 approach looks better.
0
websssAuthor Commented:
ok thanks

I've hit another snag, the sub query which checks LATESTRECORD is very heavy, without its a lot quicker

Is there a more efficient way to do this:

DECLARE @continue INT
DECLARE @rowcount INT

----Create A Temporary Table to hold IDS to Delete
IF OBJECT_ID('tempdb.dbo.#DataToDeleteTable', 'U') IS NOT NULL
            DROP TABLE  #DataToDeleteTable; 

Create Table #DataToDeleteTable ( RowID int IDENTITY(1, 1), ifkCommanTrackingID int, dGPSDateTime datetime)

-----Insert IDs to delete to the Temporary Table (Data older than 12months)
insert into #DataToDeleteTable(ifkCommanTrackingID, dGPSDateTime)
    select ipkCommanTrackingID, dGPSDateTime from tblCommonTrackingData
    Where dGpsDateTime <  DATEADD(month, -12, GETDATE())
    and ipkCommanTrackingID not in (select LastRecord From wlt_tblDevices where ipkCommanTrackingID != LastRecord) 
    order by dGPSDateTime asc
 

 select * from #DataToDeleteTable
 go
 DROP TABLE  #DataToDeleteTable
 go

Open in new window


line in question is
    and ipkCommanTrackingID not in (select LastRecord From wlt_tblDevices where ipkCommanTrackingID != LastRecord) 

Open in new window

0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
I changed the sub-query to a LEFT OUTER JOIN. Can you please see if it helps?

insert into #DataToDeleteTable(ifkCommanTrackingID, dGPSDateTime)
    select ctd.ipkCommanTrackingID, ctd.dGPSDateTime 
    from tblCommonTrackingData AS ctd
    LEFT OUTER JOIN wlt_tblDevices td ON ctd.ipkCommanTrackingID = td.LastRecord
    Where ctd.dGpsDateTime <  DATEADD(month, -12, GETDATE())
    --and ctd.ipkCommanTrackingID not in (select LastRecord From wlt_tblDevices where ipkCommanTrackingID != LastRecord) 
    AND ctd.ipkCommanTrackingID IS NOT NULL
    AND td.LastRecord IS NULL
    order by ctd.dGPSDateTime asc

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need the ORDER BY. Sorting is a performance killer process so remove it from the SELECT and it will run faster.
Also, transform the NOT IN clause in a NOT EXISTS clause. It is expected to improve the performance as you will join the tables like this.
I don't understand the SELECT before the DELETE operation but if you want to print the results then you can avoid a SELECT operation by simple using the OUTPUT clause in the INSERT command, so it's one less table operation.
With that said, your new query will looks like this:
DECLARE @continue INT
DECLARE @rowcount INT

----Create A Temporary Table to hold IDS to Delete
IF OBJECT_ID('tempdb.dbo.#DataToDeleteTable', 'U') IS NOT NULL
            DROP TABLE  #DataToDeleteTable; 

Create Table #DataToDeleteTable ( RowID int IDENTITY(1, 1), ifkCommanTrackingID int, dGPSDateTime datetime)

-----Insert IDs to delete to the Temporary Table (Data older than 12months)
insert into #DataToDeleteTable(ifkCommanTrackingID, dGPSDateTime)
OUTPUT inserted.*
    select ipkCommanTrackingID, dGPSDateTime 
    from tblCommonTrackingData
    Where dGpsDateTime <  DATEADD(month, -12, GETDATE())
    and NOT EXISTS (select 1 
           From wlt_tblDevices 
           where tblCommonTrackingData.ipkCommanTrackingID = wlt_tblDevices.LastRecord) 
 
DROP TABLE  #DataToDeleteTable
 go

Open in new window

Now, the only way you can improve more is to have indexes on tblCommonTrackingData.ipkCommanTrackingID and wlt_tblDevices.LastRecord columns.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.