Delete data in a table

Question

Delete data in a table

MTY-1082557

SSCarpal Tunnel

Points: 4540
More actions
November 22, 2010 at 11:49 am

#226913

What is the best performance in Sql2005 to delete some data in a physical table without blocking the table while another process is trying to insert some sort of data into the same table?
thank you

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

in general, it depends how many rows at a time you are deleting.

SQL server will automatically create row-level locks for single row deletions, so there would not be contention with others who are inserting,updating or deleting, unless someone tried to hit the same row.

as you start deleting more rows in the same delete statement (ie DELETE FROM YOURTABLE WHERE STATE='FL') SQL will look at the statistics for the table's column that is being filtered int he WHERE statment to decide if there data is granular enough that it can add a few more row locks, or if it is cheaper to start using page locks or even table locks if it's going to affect a lot of rows. A table lock would block activity until the transaction is complete,w hich you want to try to avoid.

if you are doing a massive delete, you can delete in batch sizes of say, 10K rows to try and minimize the impact.

so, the devil is in the details....what does your typical delete statement look like on the table? about how many rows are being deleted.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

A lot of it also depends on your indexing architecture. Are you deleting based on the clustered index and, will those deletes take place in a different part of the cluster than the inserts? If you can be reasonably sure you're dealing with seperate pieces of the structure, you shouldn't have any issues. If, on the other hand, your deletes are based on indexes other than the cluster, or no indexes at all, you can't be sure that you won't have contention for resources.

"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

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 3

This table will be work as a Bulk table, it will contain data that will be used in the next step of a big process. As soon as the data is inserted into the database tables it has to be deleted from the bulk table as WHERE ID = 200, it always delete by ID, so it will keep the other ID’s. I have to delete between 800,000 to 3millions records, then when it’s time to process the next ID it will do the same.. But while the 3million records are being deleted more records (with different Id) can be inserted into this bulk table. I was planning to have an index by the ID.

Am I making sense?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

3 million rows out of how many?

And again, an index alone won't necessarily do much for you, and could hurt you. What's the clustered index?

"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

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 5

It can be 3 million rows of 6 or 9 million rows (no more).. my cluster index can be the ID

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

Ah, well, you might be in trouble then. 1/3 to 1/2 of the table being deleted is likely to lead to table locks.

You might want to look at partitiioned views or something else. You just won't be able to delete a third of the table without a scan, which will prevent other operations from inserting.

"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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Grant, what about the option of using PAGELOCK as a table hint during the delete? If it's clustered on the ID, that'll keep it out of the way of other inserts for other IDs.

I know that will slow down the delete itself, but possibly worth it for contention avoidance?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 8

thank you all.

i will try every option to evaluate business requirements.. these deletions has to be done through a UI where it cant take more than 10minutes to process..

thank you again

Brigadur SSCrazy Points: 2097 More actions · Answer 9

Hi,

I would suggest to consider using the TOP clause to set number of rows deleted in one statement:

delete top (<nbr>) from <table>

where ID = <value>

The nbr should depent on size of one row.

Cheers

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 10

Check isolation for snapshot isolation level which stores data in tempdb while retrieving data so that actual table can be updated with out lock any row locks will be there

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 11

Craig Farrell (11/22/2010)
Grant, what about the option of using PAGELOCK as a table hint during the delete? If it's clustered on the ID, that'll keep it out of the way of other inserts for other IDs.
I know that will slow down the delete itself, but possibly worth it for contention avoidance?

Yeah, you can try. It might work. I'm just not sure. Deleting 1/3 of the table... I suspect you're looking at contention no matter what you do. It'll only take the right page that has data from two different values to stop the whole thing up.

"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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 12

pavan_srirangam (11/22/2010)
Check isolation for snapshot isolation level which stores data in tempdb while retrieving data so that actual table can be updated with out lock any row locks will be there

I thought about snapshot isolation, but the main purpose of it is to allow reads while changes are made to the data. I could be wrong, but I don't think it will help you with a delete and an insert on the same page. I'm pretty sure you're going to be looking at blocking there.

"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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Grant Fritchey (11/22/2010)
pavan_srirangam (11/22/2010)
Check isolation for snapshot isolation level which stores data in tempdb while retrieving data so that actual table can be updated with out lock any row locks will be there
I thought about snapshot isolation, but the main purpose of it is to allow reads while changes are made to the data. I could be wrong, but I don't think it will help you with a delete and an insert on the same page. I'm pretty sure you're going to be looking at blocking there.

I believe you are correct Grant. Snapshot is to allow you to read while changes are occurring. A downside is the bigger risk of running into a deadlock.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events