long delete

Hi
I have person table that is taking about 90 seconds to delete a person. The
person table is related to about 20 other tables on the personid with the
option exforce relationship for replication and enforce relationship for
inserts and updates. I checked the indexes and they're all indexed. Does
anyone know what could cause such a long delete
thanks
p
Do you have indexes on all the foreign key columns (the referencing tables)? Any blocking going on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:00C44F73-7BFA-4CBA-9720-A7BAA5188998@.microsoft.com...
> Hi
> I have person table that is taking about 90 seconds to delete a person. The
> person table is related to about 20 other tables on the personid with the
> option exforce relationship for replication and enforce relationship for
> inserts and updates. I checked the indexes and they're all indexed. Does
> anyone know what could cause such a long delete
> thanks
> p
|||Hi
Whne it is running, check for any blocking by another process.
Affecting 20 tables does take some time, especially if there are multiple
rows on child tables for each parent row.
Have a look at SQL Profile whilst it is running and check the IO statistics.
Regards
Mike
"alfred" wrote:

> Hi
> I have person table that is taking about 90 seconds to delete a person. The
> person table is related to about 20 other tables on the personid with the
> option exforce relationship for replication and enforce relationship for
> inserts and updates. I checked the indexes and they're all indexed. Does
> anyone know what could cause such a long delete
> thanks
> p
|||Hi
there are not alot of child in the relationship from the number of rows
affected, usually around 2 or 3 rows affected. I checked the sql profiler
trace file. What is taking up the time are these cacheremove events that
execute sql statements that are not really being called by the delperson
procedure. How could they be called?
thanks
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Whne it is running, check for any blocking by another process.
> Affecting 20 tables does take some time, especially if there are multiple
> rows on child tables for each parent row.
> Have a look at SQL Profile whilst it is running and check the IO statistics.
> Regards
> Mike
>
> "alfred" wrote:
|||hi,
I also run the delete person where personid = 123 on index tuning wizard,
but I keep getting this error
"there is insufficent memory to perform index analysis."
what could cause that
thanks
P
"Tibor Karaszi" wrote:

> Do you have indexes on all the foreign key columns (the referencing tables)? Any blocking going on?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alfred" <alfred@.discussions.microsoft.com> wrote in message
> news:00C44F73-7BFA-4CBA-9720-A7BAA5188998@.microsoft.com...
>
>
|||I don't know, only reference for that error I found is:
http://groups.google.com/groups?hl=s...eta%3Dgroup%25
3Dmicrosoft.public.sqlserver.*
Did you verify that the referencing table has indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:286C0462-19C4-4C5B-95D7-F16F3112890B@.microsoft.com...[vbcol=seagreen]
> hi,
> I also run the delete person where personid = 123 on index tuning wizard,
> but I keep getting this error
> "there is insufficent memory to perform index analysis."
> what could cause that
> thanks
> P
> "Tibor Karaszi" wrote: