Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

All,
MS SQL Server 2000
Win 2K SP2
I'm trying to get a better handle on why the performance on a
particular DELETE statement is so poor.
Query: DELETE FROM FAXNET WHERE DataSetID = ?
Here is some basic information on my table:
FAXNET table Size: 3.1M (million) rows
Clustered index on DataSetID
Distinct values of DataSetID in table: 4305
7 other indices on the table including the built-in one for the primary
key, as well as a UNIQUE index on 5 columns
Unusual symptoms:
If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
quickly as possible, do my DELETE, it takes under 5 seconds. However,
if there is any significant delay between the UPDATE STATISTICS command
and the execution of the DELETE, the statement takes minutes (at least
5 - I haven't actually waited for it to complete).
Is the fact that I'm deleting based on the clustered index column value
significant (I'm pretty sure it's significant, but not sure how)?
Does anything about this scenario seem like expected results based on
the table and query setup?
How can I get better feedback on how this is being processed - what is
the best way to look into it? I attempted to look at execution plans
etc. but I haven't done that stuff on MS SQL Server ever (although I
used to be quite good at it 10 years ago on Informix :]).
We also see brutal lock contention for this query with SELECTs being
blocked after about two minutes into the execution. I've definitely
seen an exclusive table lock acquired due by this statement - can
anyone explain why?
Is there any standard maintenance that should be done - does the
clustered index need to be rebuilt periodically? I noticed that both
the "auto create statistics" and "auto update statistics" DB options
were set to ON for this database, so I don't feel like I need to
recommend UPDATE STATISTICS (am I dating myself yet?).
Any suggestions would be appreciated.
Thanks,
Wes Gamble> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
Yes , it is a good practice to rebuild CI periodically
Do you have a CI UNIQUE on DataSetId column? If you don't SQL Server will
rebuild all NCI ( because it has CI Keys as pointers to the actual data)
and it takes time
You may want to INSERT the data ( you do not want to delete) INTO a new
table and then delete the old table and rename a new tabe as an old one
"weyus" <wesgamble@.gmail.com> wrote in message
news:1162943286.659963.188050@.e3g2000cwe.googlegroups.com...
> All,
> MS SQL Server 2000
> Win 2K SP2
> I'm trying to get a better handle on why the performance on a
> particular DELETE statement is so poor.
> Query: DELETE FROM FAXNET WHERE DataSetID = ?
> Here is some basic information on my table:
> FAXNET table Size: 3.1M (million) rows
> Clustered index on DataSetID
> Distinct values of DataSetID in table: 4305
> 7 other indices on the table including the built-in one for the primary
> key, as well as a UNIQUE index on 5 columns
> Unusual symptoms:
> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
> Is the fact that I'm deleting based on the clustered index column value
> significant (I'm pretty sure it's significant, but not sure how)?
> Does anything about this scenario seem like expected results based on
> the table and query setup?
> How can I get better feedback on how this is being processed - what is
> the best way to look into it? I attempted to look at execution plans
> etc. but I haven't done that stuff on MS SQL Server ever (although I
> used to be quite good at it 10 years ago on Informix :]).
> We also see brutal lock contention for this query with SELECTs being
> blocked after about two minutes into the execution. I've definitely
> seen an exclusive table lock acquired due by this statement - can
> anyone explain why?
> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
> Any suggestions would be appreciated.
> Thanks,
> Wes Gamble
>|||> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
This suggests that you get different execution plans for the delete operations. However, the DELETE
you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
100% certainty that the update of statistics does change things and also check the execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"weyus" <wesgamble@.gmail.com> wrote in message
news:1162943286.659963.188050@.e3g2000cwe.googlegroups.com...
> All,
> MS SQL Server 2000
> Win 2K SP2
> I'm trying to get a better handle on why the performance on a
> particular DELETE statement is so poor.
> Query: DELETE FROM FAXNET WHERE DataSetID = ?
> Here is some basic information on my table:
> FAXNET table Size: 3.1M (million) rows
> Clustered index on DataSetID
> Distinct values of DataSetID in table: 4305
> 7 other indices on the table including the built-in one for the primary
> key, as well as a UNIQUE index on 5 columns
> Unusual symptoms:
> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
> Is the fact that I'm deleting based on the clustered index column value
> significant (I'm pretty sure it's significant, but not sure how)?
> Does anything about this scenario seem like expected results based on
> the table and query setup?
> How can I get better feedback on how this is being processed - what is
> the best way to look into it? I attempted to look at execution plans
> etc. but I haven't done that stuff on MS SQL Server ever (although I
> used to be quite good at it 10 years ago on Informix :]).
> We also see brutal lock contention for this query with SELECTs being
> blocked after about two minutes into the execution. I've definitely
> seen an exclusive table lock acquired due by this statement - can
> anyone explain why?
> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
> Any suggestions would be appreciated.
> Thanks,
> Wes Gamble
>|||Tibor Karaszi wrote:
> This suggests that you get different execution plans for the delete operations. However, the DELETE
> you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
> 100% certainty that the update of statistics does change things and also check the execution plans.
What is the best way to check execution plans for DELETE statements?
Wes|||> What is the best way to check execution plans for DELETE statements?
Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"weyus" <wesgamble@.gmail.com> wrote in message
news:1163010846.268748.290020@.f16g2000cwb.googlegroups.com...
> Tibor Karaszi wrote:
>> This suggests that you get different execution plans for the delete operations. However, the
>> DELETE
>> you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
>> 100% certainty that the update of statistics does change things and also check the execution
>> plans.
> What is the best way to check execution plans for DELETE statements?
> Wes
>|||Tibor Karaszi wrote:
> > What is the best way to check execution plans for DELETE statements?
> Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
> or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
I see the same execution plan regardless of whether I run UPDATE
STATISTICS on the FAXNET table or not.
The execution plan is below - can anyone help me decipher it - does it
look prohibitively expensive assuming 3M+ rows in the FAXNET table?
Thanks,
Wes
=========================================
|--Sequence
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet0]))
| |--Table Spool
| |--Clustered Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet00]),
WHERE:([FAXNET].[DataSetID]=57242))
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet000]))
| |--Table Spool
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
| |--Table Spool
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IDXZIP]))
| |--Table Spool
|--Assert(WHERE:(If NOT(([Expr1006] IS NULL)) then 0 else NULL))
| |--Nested Loops(Left Semi Join, OUTER
REFERENCES:([FAXNET].[UniqueID]), DEFINE:([Expr1006] = [PROBE VALUE]))
| |--Sort(ORDER BY:([FAXNET].[UniqueID] ASC))
| | |--Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[PK_FAXNET]))
| | |--Table Spool
| |--Row Count Spool
| |--Index
Spool(SEEK:([MailData].[FaxNetID]=[FAXNET].[UniqueID]))
| |--Clustered Index
Scan(OBJECT:([ABSMain].[dbo].[MailData].[PK_MailData]))
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[NODUPE]))
| |--Table Spool
|--Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
|--Table Spool|||Does the execution plan not show until the query/statement is
completed?
I'm trying to run this DELETE and it is taking forever, but also, I
don't see an execution plan in the Execution Plan pane - I would think
that the execution plan would be completed before the query actually
begins.
Thanks,
Wes|||Wes,
There is a foreign key constraint from MailData.FaxNetID to
FAXNET.UniqueID (or vice versa). It seems, this column
MailData(FaxNetID) is not indexed. It could speed up your query
significantly (as could a reduction in the amount of indexes on FAXNET).
The query plan in the poorly performing situation must be different. The
estimated query plan might be enough. Wrap the statement in SET
SHOWPLAN_TEXT ON and SET SHOWPLAN_TEXT OFF and you should get it.
HTH,
Gert-Jan
weyus wrote:
> Tibor Karaszi wrote:
> > > What is the best way to check execution plans for DELETE statements?
> >
> > Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
> > or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
> I see the same execution plan regardless of whether I run UPDATE
> STATISTICS on the FAXNET table or not.
> The execution plan is below - can anyone help me decipher it - does it
> look prohibitively expensive assuming 3M+ rows in the FAXNET table?
> Thanks,
> Wes
> =========================================> |--Sequence
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet0]))
> | |--Table Spool
> | |--Clustered Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet00]),
> WHERE:([FAXNET].[DataSetID]=57242))
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet000]))
> | |--Table Spool
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
> | |--Table Spool
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IDXZIP]))
> | |--Table Spool
> |--Assert(WHERE:(If NOT(([Expr1006] IS NULL)) then 0 else NULL))
> | |--Nested Loops(Left Semi Join, OUTER
> REFERENCES:([FAXNET].[UniqueID]), DEFINE:([Expr1006] = [PROBE VALUE]))
> | |--Sort(ORDER BY:([FAXNET].[UniqueID] ASC))
> | | |--Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[PK_FAXNET]))
> | | |--Table Spool
> | |--Row Count Spool
> | |--Index
> Spool(SEEK:([MailData].[FaxNetID]=[FAXNET].[UniqueID]))
> | |--Clustered Index
> Scan(OBJECT:([ABSMain].[dbo].[MailData].[PK_MailData]))
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[NODUPE]))
> | |--Table Spool
> |--Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
> |--Table Spool|||Gert,
Thanks. That makes perfect sense. Putting an index on the MailData
column sped things up dramatically (there were over 268000 records in
MailData, so scanning them was taking a while).
Thanks again,
Wes