Taking long time to commit for DELETE stats

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 2000 Forums

SQL Server Administration (2000)

Taking long time to commit for DELETE stats

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-10 : 17:38:28

Hi,
From last 3 weeks, it has been taking a long time to run some routine processes. After doing reindexing and update stats (changed sampling from 85 to 100%), we put a trace on. All the delete statements are taking a long time to commit. Ofcourse the tables are huge with millions of records but that was not a problem before!
The commit takes anything from 7 to 10 mins.
Any thoughts?
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-10 : 18:01:10

Is it a massive DELETE? COMMITs should take just a few seconds. Have additional indexes been added? What has changed in the environment? Has performance on the server gotten worse?

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-09-10 : 18:28:33

How many rows at a time are you trying to delete?

Is the table related to any other table (Are there any FK's in this table)?

Are you trying to delete a select few records, or do you want to kill all records in the table?

What you might want to do is something like this:


SET ROWCOUNT 1000

--DELETE STATMENT HERE

SET ROWCOUNT 0

This will allow you to delete jsut 1000 rows at a time. This way, you can do your delete in smaller batchs. This might help you out a bit. Answer those questions that Tara and I posted, and we should be able ot help you further.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-10 : 18:33:26

Yes, they are massive deletes which are a part of every week's (Monday + Tuesday) payroll. The only thing that really changed is that we applied SP3a. ;-). I don't think performance has degraded overall because the users haven't complained anything being slow the rest of the week and these commits take have been taking same time for the last 3 weeks. BTW i misstated, I changed the update stats to 100% today so I need to see what happens coming Monday/Tuesday. also update stats job runs every wednesday so now I changed it to sunday night. If this doesn't help, adding indexes is my next plan.
-Sarat

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-10 : 18:33:47

For the controlled DELETE (deleting batches of records like Michael suggested), here is how I have done it:



SET NOCOUNT ON

DECLARE @Count INT

SELECT @Count = COUNT(*)
FROM SomeTable
WHERE SomeColumn IS NULL

SET ROWCOUNT 10000

WHILE @Count > 0
BEGIN
	BEGIN TRAN
	
	DELETE FROM SomeTable
	WHERE SomeColumn IS NULL
	
	IF @@ERROR = 0
		COMMIT TRAN

	ELSE
		ROLLBACK TRAN		

	CHECKPOINT

	SELECT @Count = COUNT(*)
	FROM SomeTable
	WHERE SomeColumn IS NULL

END

SET ROWCOUNT 0
SET NOCOUNT OFF

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-10 : 18:35:08

Adding indexes could slow it down if the index is affected by the DELETE.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-10 : 19:14:07

Actually the sql delete statements are part of cobol. But the log shows that commit occurs after every few deletes so its not like we wait until the last delete to occur and then commit.
For ex: We have ee population divided into 5 groups. The commit occurs after each group is processed. Only 2 of these groups have about 1000-7000 ee; still not that much. If sql in cobol is the problem, we would have gotten a fix (considering lot of ppl would have complained PSoft by now) so i think it is something i need to fix at the db side.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-10 : 19:18:01

So each COMMIT takes 7 to 10 minutes? Or all together, they take 7 to 10 minutes. Have you investigated the problem using SQL Profiler yet?

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-09-10 : 19:54:56

Each commit takes about 7 mins. I haven't used profiler. I set cobol trace on the process server file. I have perfmon running to get entire week's performance so by sunday morning i will have the entire log. Are you suggesting to run profiler to trace again?

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-11 : 11:55:50

Profiler would help you determine if there is anything else occurring on SQL Server which is causing this problem. The process could be blocked for periods of times causing the problem. Or maybe there is just a lot of activity.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources