Post reply

Deleting 1 record is taking 4:30 minutes

  • June 29, 2009 at 7:14 am

    #139212

    Hi all,

    I am experiencing an issue when deleting a record.

    use MyDatabase

    go

    delete from dbo.MyTable

    where id = 174

    It is taking a very long time. I can see that it is creating several locks. any ideas?

    Thank you so much!

  • June 29, 2009 at 7:17 am

    #1016804

    first thing, is it blocked by any other processes?

    ---------------------------------------------------------------------

  • June 29, 2009 at 7:18 am

    #1016806

    No it is not block by anyother process.

  • June 29, 2009 at 7:34 am

    #1016814

    then please post more info, such as table schema, size of table, execution plan.

    you say delete A record, are you sure only one record meets that criteria?

    ---------------------------------------------------------------------

  • June 29, 2009 at 7:46 am

    #1016822

    I attached the execution plan and the table schema. This table is less than 200 records. Yes I am sure there is only one record with that criteria. The database is also in simple recovery model.

    Thank you!

  • June 29, 2009 at 8:06 am

    #1016844

    Take a look at the other tables referred to in the execution plan.

    I suspect that these have a foreign key reference to the table you trying to delete from, and not all of these tables have an index to support the foreign key. SQL Server is therefore having to resort to clustered index scans (and some of those tables will be large) to make sure there is nothing referencing the row you are trying to delete.

  • June 29, 2009 at 8:29 am

    #1016860

    Ian Scarlett

    Thank you for the reply.

    I already check all those tables and they do have a foreign key in reference to that table. All the tables have indexes to support the foreing key, however, the indexex are compose index with one being that foreing key plus another field.

  • June 29, 2009 at 8:56 am

    #1016885

    Run the delete from SSMS with "SET STATISTICS IO ON"

    Look at the statistics, and that should tell you which table(s) are causing excessive IO

  • June 29, 2009 at 9:16 am

    #1016900

    Ian Scarlett

    these are the two tables with excessive IO:

    Table 'MyValueR'. Scan count 1, logical reads 27179, physical reads 5, read-ahead reads 27173, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'MyValue'. Scan count 1, logical reads 818211, physical reads 0, read-ahead reads 698578, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Both of these tables contains conposite indexes including the reference of the FK.

    Thanks

  • June 29, 2009 at 9:21 am

    #1016907

    If the FK column isn't the first column of the composite index, then SQL Server will revert to clustered index or table scans, which is what you are getting.

    I wouldn't re-order the existing index(es), as they are probably there to satisfy other queries, so add another index, with just the FK column.

  • June 29, 2009 at 12:57 pm

    #1017137

    That worked! Thank you so much! 😛

  • June 29, 2009 at 2:48 pm

    #1017203

    JohnDBA (6/29/2009)

    That worked! Thank you so much! 😛

    You're welcome:-)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply