Deleting/updating thousands of records at once 3

[Soiboy]

I keep running into the problem of my sql server database not being able to delete large numbers of records. It just sits and spins so to speak, and I have to eventually just stop it.
It is pretty good hardware, dual xeon, 2 gigs ram, etc. How can I do large deletes and updates in SQL Server? Sorry if this is a very basic question, I have searched and cannot find the answer.
thanks,

Ed

 

[gmmastros]

If you have a ton of indexes on your database, then updates, deletes, and inserts will be slow.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Or if you have no indexes at all you will have the same problem
How many indexes do you have, how wide are the tables, what's the level of fragmentation?

http://sqlservercode.blogspot.com/

 

[Soiboy]

Thank you very much for the replies.

That particular table has 40 fields, two primary keys and one index (I think).
7500 updates took over an hour to complete (just finished now).

Anyway to see at a glance how many indexes in the database?

 

[SQLDenis]

select * from sysindexes`

http://sqlservercode.blogspot.com/

 

[Soiboy]

boy, maybe I should take a class or something
I have had to learn everything as I go
thanks, SQLDenis
There are a lot of records in that table

 

[vongrunt]

sysindexes also contains heap table entries and rows for all statistics... first have indid=0, stats names usually begin with '_WA'.

To see list of indexes on a particular table, run sp_helpindex 'tablename'.

About how many records in table are we talking? What about foreign keys on a table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

you can use this to get all usertables only

SELECT OBJECT_NAME ( si.id ) AS TableName ,
CASE indid WHEN 1 THEN 'Clustered'
WHEN 255 THEN 'HasTextOrImageData'
ELSE 'NonClustered'
END TypeOfIndex,
si.[name] AS IndexName
FROM sysindexes si
JOIN sysobjects so ON si.id =so.id
WHERE xtype ='U'
ORDER BY TableName,indid

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

Sorry for nitpicking, but this will... argh damnit, I posted my previous reply 10 seconds before you.

Here is slightly improved version - no fake entries for heap tables and no stats:

SELECT SO.name AS TableName, SI.indid, SI.name AS IndexName,
CASE indid WHEN 1 THEN 1 ELSE 0 END AS IsClustered
FROM sysobjects SO
INNER JOIN sysindexes SI ON SI.id=SO.ID
WHERE SO.XTYPE = 'U'
	AND SI.indid BETWEEN 1 AND 254
	AND SI.status & 64 = 0 -- can be also done w/ INDEXPROPERTY()
ORDER BY TableName, SI.indid

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

When you are doing your filtering in the updates and deletes are the columns that you are filtering on (the where clause) indexes? How many total records in the table?

If you are using an index for your filtering is it a clustered index or a non-clustered index?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hmckillop]

One thing to consider is splitting your deletes up into batches, have a look at

http://www.tek-tips.com/faqs.cfm?fid=3141

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Soiboy]

hmckillop thank you!
That is exactly what the doctor ordered!
doing it in batches was in the back of my head, I should have included it in my question.

Thanks everyone for your posts, I know alot more than I did when I first posted my question.

Great forum, I will definitely be haning out here a lot more in the future.

Ed