allocate / deallocate table

Question

allocate / deallocate table

kaj

SSCommitted

Points: 1600
More actions
September 2, 2020 at 2:18 pm

#3785617
I need to delete a lot of rows from some tables. There are millions of rows and only a few thousands should be left afterwards.
Based on experience this takes a lot of time and impacts other jobs that use the table. So what I would like to do instead is to:
1. Copy the rows that should be left to a temp table
2. Truncate the original table
3. Copy the rows from the temp table back into the original table.
That should make the process much faster than deleting that many rows.
But obviously I need to be able to ensure that other jobs don't add rows to the table while these things happen. So how can this be done?
I'm looking for someting like
ALLOCATE TABLE xxx WITH(EXCLUSIVE_LOCK)
do my things....
DEALLOCATE TABLE xxx WITH(EXCLUSIVE_LOCK)

Obviously that is not possible, but how can one do something like that in other ways?

Viewing 11 posts - 1 through 10 (of 10 total)

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

ScottPletcher SSC Guru Points: 101084 More actions · Answer 1

Something along these lines:

SELECT TOP (0) *
INTO dbo.maintable_backup
FROM dbo.maintable
--Edit: Added UNION ALL to cancel IDENTITY property, if present
UNION ALL
SELECT TOP (0) *
FROM dbo.maintable

BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.maintable_backup
SELECT *
FROM dbo.maintable WITH (TABLOCKX)
WHERE <whatever>
TRUNCATE TABLE dbo.maintable
INSERT INTO dbo.maintable WITH (TABLOCKX)
SELECT *
FROM dbo.maintable_backup WITH (TABLOCKX)
END TRY
BEGIN CATCH
...
END CATCH
END TRANSACTION

This reply was modified 4 years, 2 months ago by ScottPletcher. Reason: Added code to table create, using INTO, to cancel IDENTITY property if there is an IDENTITY column in the table

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kaj SSCommitted Points: 1600 More actions · Answer 2

Thank you, looks promising. Not quite the session scoped lock I was thinking of, but using a transaction scoped lock is probably okay.

But, hmm...

Looking at the description of table hints in the MS documentation, it is not completely clear to me if the TABLOCKX lock is held only for the statement or if it is held until the end of the transaction like XLOCK and TABLOCK in combination?

This reply was modified 4 years, 2 months ago by kaj.

ScottPletcher SSC Guru Points: 101084 More actions · Answer 3

Yes, technically TABLOCKX is only for that statement. I guess in theory someone could INSERT a row between the SELECT and the TRUNCATE.

I should have added HOLDLOCK to the first query:

SELECT *

FROM dbo.maintable WITH (TABLOCKX, HOLDLOCK)

WHERE <whatever>

Or you can set the trans isolation level to SERIALIZABLE for the duration of that trans.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

Include the TRUNCATE in the transaction. That'll lock the table until a COMMIT is done.

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

Steve Jones - SSC Editor SSC Guru Points: 736118 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 736118

September 3, 2020 at 4:09 pm

#3786066

As Jeff noted, include TRUNCATE in here.

Cebisa SSC Enthusiast Points: 151 More actions · Answer 6

How critical is your application?

You will have an outage between steps 2 and 3 and if the table has foreign key constraints you cannot use truncate table

If you are with SQL 2016 enterprise you could:

Partition the table, then use merge/split to drop a large number of rows.

or

Delete records in chunks

Don't forget to run update statistics

ScottPletcher SSC Guru Points: 101084 More actions · Answer 7

Steve Jones - SSC Editor wrote:

As Jeff noted, include TRUNCATE in here.

I did include TRUNCATE in the trans, from the start. Not exactly sure how else you want the transaction structured.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 8

ScottPletcher wrote:

Steve Jones - SSC Editor wrote:
As Jeff noted, include TRUNCATE in here.
I did include TRUNCATE in the trans, from the start. Not exactly sure how else you want the transaction structured.

Agreed... didn't see your post until after I posted. I was responding to the OP's original post.

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

ScottPletcher SSC Guru Points: 101084 More actions · Answer 9

Cebisa wrote:

How critical is your application?
You will have an outage between steps 2 and 3 and if the table has foreign key constraints you cannot use truncate table
Don't forget to run update statistics

With FKs, true, but you could drop and recreate the FKs. I'd do that rather than have to DELETE that many rows from the table rather than just TRUNCATE it. The logging time alone will kill you DELETEing that many rows.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Cebisa SSC Enthusiast Points: 151 More actions · Answer 10

If the table has foreign key constraints, chances are you will also need to look at other tables too when deleting such a large number of data

If there are no constraints, and if you are allowed to, you could:

Copy the rows you want to keep into a new physical table, drop the original table, rename the physical table

That has its pitfalls too with permissions, constraints, but it could save you downtime