Performance - PAGEIOLATCH_SH - 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

General SQL Server Forums

New to SQL Server Programming

Performance - PAGEIOLATCH_SH

Author

Topic

SQLJunior
Starting Member

34 Posts

Posted - 2007-03-30 : 06:31:50

Hi,

I need to delete data which is older than a year from a table which contains 54 million rows (yes i know, ridiculous isn't it).
I am using the below query:

DELETE tblLineCard
WHERE CreateDT <= dateadd(yy,-1,getdate())

It worked fine for the smaller tables, but shortly after starting up the above query it starts blocking itself (ie. it shows: SPID 55(Blocked by 55), under current activity in EM), goes into sleeping status and and has a wait type of PAGEIOLATCH_SH.
There are NO other queries taking place.
The transaction log keeps on growing and Query analyzer shows the "Executing Query Batch" message.

Any ideas? I have killed the process and restarted but same scenario each time. Your help would be wonderful.
Yours sincerely
SQLJunior...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-30 : 10:04:25

You need to delete fewer rows in each transaction, so change the WHERE clause to make tyhe number of rows to delete less. You'll have to run multiple deletes but each one will complete in a reasonable time without getting out of hand.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-30 : 12:39:20

You could change your WHERE clause as snSQL suggested. Another option is to use the ROWCOUNT with a loop. For Example:

DECLARE @RowCount INT
DECLARE @Error INT	
SET ROWCOUNT 10000

SET @RowCount = 1
SET @Error = 0

WHILE @RowCount > 0 AND @Error = 0
BEGIN
	DELETE ....
	
	SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
END

-Ryan

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-03-30 : 12:45:01

The process is not actually blocking itself. You have SP4 installed on your machine. Microsoft introduced this as a way of better tracing performance problems. Your delete is simply disk bound. Depending on how much data you are deleting, I would worry more about the transaction log blowing out.

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-30 : 13:05:16

I think I would rename the table...

Then do SELECT * INTO OldTableName FROM RenameTable WHERE What I want to keep

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-30 : 14:32:07

Renaming sounds easy but then you have to worry about re-creating all the indexes/FKeys etc.

************************
Life is short. Enjoy it.
************************

SQLJunior
Starting Member

34 Posts

Posted - 2007-04-02 : 03:16:43

hi all, thnx for the help.

I think i should rename the table as per Ryan's suggestion, there are not many indexes and i can create them after on the smaller data set. Hopefully all goes well.

Thanking you and Kind Regards,

******************************
*******!!!Carpe Diem!!!*******
******************************

SQLJunior
Starting Member

34 Posts

Posted - 2007-04-02 : 03:24:28

ok sorry... Brett's suggestion i mean... it's early and i'm still asleep... lol;o)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources