deleting rows, halting server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

deleting rows, halting server

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 05:22:43

Hi,

I've deleted a bunch of rows from my table.. So far about 500k from a 9 million row table.. This table is hit frequently

I think maybe my indexes are a mess? Its so slow every page is timing out .. im now urgently trying to figure something out to resolve ..

ive done UPDATE STATISTICS on the table .. now its probably best I run some command on the indexes directly?

Any help very much appreciated.. !!!

mike123

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-11 : 05:27:38

Try to delete small number of records per batch to avoid locking and contention.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 05:33:37

Hi,

Actually I have been deleting them in a loop 2000 rows at a time ..... I'm not actually deleting anything anymore, but everything has slowed to a CRAWL ..

I'm running

UPDATE STATISTICS tblinstantmessage WITH FULLSCAN

but no complete yet.. after I will rebuild the index .. =
i HOPE this works ... really not sure I know what im doin !!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 05:56:14

You can also drop indexes before delete and create them afterwards.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 06:02:09

quote:
Originally posted by Peso

You can also drop indexes before delete and create them afterwards.

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

It's looking like I might have to ? Previously my problem was the server slowing WHILE I was deleting the rows.. now that I have implemented a good solution for breaking the deletes into smaller deletes, I'm having this problem ..

Are there any other options really?

much appreciated..
thanks again!
mike123

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-11 : 06:04:10

Have you considered populating a new table with only the rows that you want to keep then dropping the original table and renaming the new table to the original tables name?

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 06:05:40

Could be a problem due to foreign keys.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 06:06:27

You can also try to use the ROWLOCK hint when deleting so that not entire page is locked for others.

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 07:04:02

quote:
Originally posted by ditch

Have you considered populating a new table with only the rows that you want to keep then dropping the original table and renaming the new table to the original tables name?

Duane.

I have but I would be moving over 90% of rows.. say 9 out of 10 million ... I'm guessing this would cause some problems while the copying was happening? Also I am doing these deletes every couple months (bad i know!) I want to move to a nightly maintenance so I can delete less records, more often

thanks
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 07:15:03

quote:
Originally posted by Peso

You can also try to use the ROWLOCK hint when deleting so that not entire page is locked for others.

E 12°55'05.25"
N 56°04'39.16"

will readup on ROWLOCK , but primarily I am having problems after the deletes have actually occured

thx again
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 07:25:40

quote:
Originally posted by Peso

Could be a problem due to foreign keys.

E 12°55'05.25"
N 56°04'39.16"

Right clicking on the table, then going "view dependencies" brings back only a list of stored procedures...

it would bring back tables if their were any foreign keys that could be causing a problem right? my tables posted below incase in offers any insight

right now I am waiting for this command to complete, and hope it fixes everything... we are 1 hour into it so far .. dont know what my next step will be so I am really praying!!

EXEC isp_ALTER_INDEX 
                            @dbName = 'Dbname', 
                            @statsMode = 'DETAILED', 
                            @defragType = 'REBUILD', 
                            @minFragPercent = 10,
                            @maxFragPercent = 100,
                            @minRowCount = 1000

thanks very much once again

mike123


CREATE TABLE [dbo].[tblInstantMessage](
	[InstantMessageID] [int] IDENTITY(1,1) NOT NULL,
	[MessageToID] [int] NULL,
	[MessageFromID] [int] NULL,
	[Message] [varchar](1000) NULL,
	[Date] [smalldatetime] NOT NULL,
	[Checked] [tinyint] NULL,
	[Mobile] [tinyint] NULL,
	[deletedbySender] [tinyint] NULL,
	[deletedbyRecipient] [tinyint] NULL,
	[IP] [varchar](15) NULL,
 CONSTRAINT [PK_tblInstantMessage] PRIMARY KEY CLUSTERED 
(
	[InstantMessageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources