I am trying to delete a record.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I am not able to post to exact schema... but here is a pseudo schema.. there are also too many indexes to post.

CREATE TABLE [dbo].[test1](
      [ID] [int] IDENTITY(17235698,1) NOT FOR REPLICATION NOT NULL,
      [Text] [nvarchar](200) NULL,
      Area INT

CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_Standard]
) ON [FG_Standard]

GO

ALTER TABLE [dbo].[test1] WITH CHECK ADD CONSTRAINT [FK_test1_Area] FOREIGN KEY([Area])
REFERENCES [dbo].[Area] ([ID])
GO

Have you checked the estimated query plan of the delete statement for any anomalies?

ASKER

Hi Kobe_Lenjou:,

there is no wait state:

I am using spotlight and nothing is showing up as blocking or locking,

ASKER

Hi Kobe_Lenjou,

I just checked estimated query plan and there are about 20 index scans.

I am not sure what to look for with regards to delete statement anomalies.

SOLUTION

Umesh_Madap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

what is the "delete" statement?
can you show the estimated explain plan?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi angelIII,

the delete is a standar:

delete from table1
where ID =123.

I can estimate the plan......It seems massive.. with loads of index scan.

ASKER

Hi Kobe_Lenjou,

Which part of the plan should I look at?

The final delete operator cost is 0..176462.

I just ran it again I finally deleted one record after 39 seconds.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

yes there are foreign keys to the table.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

thanks