none
DELETE FROM Table WHERE PKID=1 runs for over 5 minutes - no idea why

    Question

  • We have a table that has around 500 rows.

    I ran DELETE FROM Table WHERE PKID = 1 and the query ran for over 5 minutes before I finally killed it.

    I backed up the database and restored to another server, same issue.

    After I dropped all the FK constraints and dropped the table itself, I created an exact duplicate an reloaded with the same data and I was then able to delete the row.

    I checked the FK constraints and verified they did not have cascade update or deletes (they were set to NO ACTION).

    When the DELETE query ran in Activity Monitor I would see wait types of PAGEIOLATCH_SH and CXPacket which swap around for suspended.

    The CPU spikes to around 40%.

    As for parallelism, I tried setting it to 1 (vs the default of 0) for Max Degree of Parallelism.

    After setting it to 1 and trying again, no change (still ran for a while and I killed it).

     I am uncertain as to what is causing this. 

    As I have a backup of the database and I can replicate this issue by restoring it and trying again to delete a row I'd really like to investigate this and understand the root cause.

    Thoughts/suggestions/ideas?

    This is SQL Server 2008 (not R2)

     

    Wednesday, July 24, 2013 11:21 PM

Answers

All replies

  • Is there any indexes behind this table?

    DELETE FROM Table WHERE PKID=1


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 12:09 AM
  • Ah good point.

    Yes it does, clustered index on PK ID.

    But I wouldn't expect that to cause a problem, I could drop the index and turn the table into a heap and test the DELETE?

    Thursday, July 25, 2013 1:02 AM
  • How many tables are referring this table via foreign keys to PKID? I s there any big tables that refer PKID?Are those columns Indexed?

    When deleting rows, though no records exists in the related (child table) SQL has to verify that no related records exists and this sometimes result in table scans or index scans 

    The Benefits of Indexing Foreign Keys


    Satheesh


    Thursday, July 25, 2013 2:00 AM
  • Ah of course, that makes good sense.

    Is there anyway for me to see what SQL Server is doing behind the scenes while it just "runs" for 5+ minutes?

    I can't get the actual execution plan until it finishes, but other options or ways to verify it is checking FK constraints?

    (Granted I can just drop the FK and see if that fixes it, but I am wondering if there is some way to "see" the work happening or know what it is doing?)

    Thursday, July 25, 2013 2:13 AM
  • As Satheesh says, if there are one or more large tables that refer to table, and they do not have indexes on the referring field, this can happen.

    If you can run in SSMS, turn on "set statistics io ON" and then run the query and see which table is getting hammered.

    Josh

    Thursday, July 25, 2013 3:08 AM
  • Statistics IO is one option, but like execution plan you may get it at the end of executing the delete query. I am not sure if this would reflect in the estimated execution plan. You could have a look at it.


    Satheesh

    Thursday, July 25, 2013 3:16 AM
  • See what the wait stats tell you on another connection during the delete:

    http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/


    www.minidba.com | Sql Server & Azure Db Performance Tuning & Monitoring Software | Free Lite Edition

    Thursday, July 25, 2013 4:21 AM
  • Try running the query below while your problem query is executing....this is a variation of a query I found on internet (can't remember who the original author(s) were), but this is what I initially use to find the cause of production problems.  Post the results, including the execution plan, and that should be able to shed some more light on the problem.

    SELECT
         spid               = er.session_id 
        ,[status]           = ses.[status] 
        ,[login]            = ses.login_name 
    ,host = ses.[host_name]
        ,blkby              = er.blocking_session_id 
        ,dbname             = DB_NAME(er.database_id) 
        ,commandtype        = er.command
        ,er.wait_type
        ,ses.cpu_time
    ,ses.logical_reads
    ,ses.writes
    ,querytext =
    SUBSTRING

    qt.text, 
    er.statement_start_offset/2, 
    (CASE WHEN er.statement_end_offset = -1 
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
    ELSE er.statement_end_offset
    END - er.statement_start_offset)/2 
    )
    ,executionplan = eqp.query_plan
        ,starttime          = er.start_time 
        ,duration = DATEDIFF(ss,er.start_time,GETDATE())
    FROM sys.dm_exec_requests er (NOLOCK)
    LEFT 
    JOIN sys.dm_exec_sessions ses (NOLOCK)
    ON ses.session_id = er.session_id 
    LEFT 
    JOIN sys.dm_exec_connections con (NOLOCK)
    ON con.session_id = ses.session_id 
    OUTER 
    APPLY sys.dm_exec_sql_text(er.[sql_handle]) qt 
    OUTER 
    APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
    WHERE ses.is_user_process = 1
    AND er.session_id <> @@SPID
    AND er.command NOT IN ('BACKUP DATABASE','UPDATE STATISTIC','ALTER INDEX','DBCC TABLE CHECK','DBCC','WAITFOR','CREATE INDEX')
    AND ISNULL(er.wait_type,'') <> 'BROKER_RECEIVE_WAITFOR'

    Thursday, July 25, 2013 5:12 AM
  • Statistics IO is one option, but like execution plan you may get it at the end of executing the delete query. 

    Well you're right if you have to kill it, you don't get the report.

    Looking at dm_exec_query_plan during or after execution may get you a report on which FK is causing the problem, as seems likely, but I'll bet you can just examine the FKs to figure out the problem - find the one(s) to the largest table, and see if they have indexes associated.

    Hey will this also show up in the missing index report/dmv, or as a suggested index in the execution plan?

    Josh


    • Edited by JRStern Thursday, July 25, 2013 5:55 AM missing index report
    Thursday, July 25, 2013 5:55 AM
  • You may get this as missing index sometimes, but not all the time.

    One option would be to get the cached execution plan and find out whats happening. Though your query get killed, I think the execution plan could be pulled from the plan cache.


    Satheesh

    Thursday, July 25, 2013 7:14 AM
  • Here is a query which would help you to pull the execution plan when a session is running.
    select er.command, qp.query_plan                               -- XML query execution plan
    from sys.dm_exec_requests er                             -- base DMV
    cross apply sys.dm_exec_query_plan(er.plan_handle) qp    -- link to the DMF
    where session_id > 50
    and session_id = <<SESSION_ID>>;


    Satheesh

    Thursday, July 25, 2013 8:34 AM
  • Sounds like a statistics problem perhaps ?

    when you created a exact duplicate of the table , did you enable the foreign key constraints and then do the delete ?..or did you delete with out the f-keys ?

    So perhaps also update the statistics and then see if the delete works.  I think the posts about the foreign key's are very valid though.  

    Use this script to see when last the statistics were updated

    select
    schemas.name as table_schema,
    tbls.name as table_name,
    i.name as index_name,
    i.id as table_id,
    i.indid as index_id,
    i.rowmodctr as modifiedRows,
    (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
    convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
    stats_date( i.id, i.indid ) as lastStatsUpdate
    from sysindexes i
    inner join sysobjects tbls on i.id = tbls.id
    inner join sysusers schemas on tbls.uid = schemas.uid
    inner join information_schema.tables tl
    on tbls.name = tl.table_name
    and schemas.name = tl.table_schema
    and tl.table_type='BASE TABLE'
    where 0 < i.indid and i.indid < 255
    and table_schema <> 'sys'
    and i.rowmodctr <> 0
    and i.status not in (8388704,8388672)
    and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

    Thursday, July 25, 2013 9:19 AM
  • Thanks for all the info.

    It was FK constraints causing all the issues.

    I'm going to take the above advice and try looking into details about active processes when I run it again on my sandbox environment.

    Would be useful to be able to determine those sorts of issues "on the fly" if it were to ever happen in a prod environment, etc...

    Thursday, July 25, 2013 9:16 PM