Delete from takes a very, very long time

#11

I ran sp_updatestatistics and the DBCC SHOWCONTIG defrag example in BOL.

running DBCC SHOWCONTIG('mytable') returns:
Extent scan fragmentation 26.32%

I take it this isn't good. How can I clean this up?

"pdxJaxon" <GregoryAJackson (AT) Hotmail (DOT) com> wrote

Quote:

and your where clause is ON That Keyed column ?

If your delete is taking a long time and the where on that you could have
a
few problems:

1. Fragmented indexes
2. Out of date Statistics
3. Missing Statistics
4. Poorly performing IO Subsystem (Disk Queueing)
5. blocking

Greg Jackson
PDX, Oregon

#12

I rebuilt in index on the table and all is much better.

"Joe" <J_no_spam (AT) _no_spam_Fishinbrain (DOT) com> wrote

Quote:

I have a table with around 38000 records in it. I'm trying to delete
records
(about 3000) from it but it takes forever!

There has been several million rows added and removed from this table
before
but now it's taking way too long (currently 7 min and running).

I'm doing delete from my table where mycol = 1. If I change this to delete
from my table where id in (select id from mytable where mycol = 1) It is
much quicker but still 2 minutes or so.

#13

I was wrong this didn't fix my problem. It did for the select but not the
delete.

The table has a total of 4 columns. 1 PK and 3 FK. None of these table are
extremely large.

"Joe" <J_no_spam (AT) _no_spam_Fishinbrain (DOT) com> wrote

Quote:

I rebuilt in index on the table and all is much better.

"Joe" <J_no_spam (AT) _no_spam_Fishinbrain (DOT) com> wrote in message
news:#iE9pJ5EFHA.4052 (AT) TK2MSFTNGP14 (DOT) phx.gbl...
I have a table with around 38000 records in it. I'm trying to delete
records
(about 3000) from it but it takes forever!

There has been several million rows added and removed from this table
before
but now it's taking way too long (currently 7 min and running).

I'm doing delete from my table where mycol = 1. If I change this to
delete
from my table where id in (select id from mytable where mycol = 1) It is
much quicker but still 2 minutes or so.

#14

what is "Scan Density" at ?

If Scan Density is > 80% youre fine.

If Extent Scan Fragmentation is 26% I guess that means scan density is 74%.
(Jaxon aint no math major)

that is a little on the bad side but not horrific.

you defrag indexes to move scan density back to 100% (no fragmentation)

use DBCC DBReindex or DBCC IndexDefrag to accomplish

GAJ

#15

Joe wrote:

Quote:

I was wrong this didn't fix my problem. It did for the select but not
the delete.

The table has a total of 4 columns. 1 PK and 3 FK. None of these
table are extremely large.

What does the execution plan show for the query?

--
David Gugick
Imceda Software
www.imceda.com

#16

Scan Density show 0.0%.

It seems to show the same for all my tables.

"pdxJaxon" <GregoryAJackson (AT) Hotmail (DOT) com> wrote

Quote:

what is "Scan Density" at ?

If Scan Density is > 80% youre fine.

If Extent Scan Fragmentation is 26% I guess that means scan density is
74%.
(Jaxon aint no math major)

that is a little on the bad side but not horrific.

you defrag indexes to move scan density back to 100% (no fragmentation)

use DBCC DBReindex or DBCC IndexDefrag to accomplish

GAJ

#17

????

no dude.......

can you run this for me in your database of Question:

DBCC SHOWCONTIG (XXX)
GO

Where "XXX" is your table that you are deleting from

then paste the entire output back to me

GAJ

#18

DBCC SHOWCONTIG scanning 'mytable' table...
Table: 'research' (306100131); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 132
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 0.00% [0:0]
- Logical Scan Fragmentation ..................: 0.76%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 99.55%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

"pdxJaxon" <GregoryAJackson (AT) Hotmail (DOT) com> wrote

Quote:

????

no dude.......

can you run this for me in your database of Question:

DBCC SHOWCONTIG (XXX)
GO

Where "XXX" is your table that you are deleting from

then paste the entire output back to me

GAJ

#19

I dont think this table has a clustered index on it

the Scan Density doesnt make sense

GAJ

#20

CONSTRAINT [PK_research] PRIMARY KEY CLUSTERED

This is from the create script.

Could it matter that I added 5 - 7 million records in one section and
removed them in another 10-12 times?

"pdxJaxon" <GregoryAJackson (AT) Hotmail (DOT) com> wrote

Quote:

I dont think this table has a clustered index on it

the Scan Density doesnt make sense

GAJ