Long running delete query

I have a table with roughly 11 million rows, defined as:

CREATE TABLE [sko].[stage_närvaro]( 
    [datum_fakta] [datetime] NULL, 
    [person_id] nvarchar NULL, 
    [läsår_fakta] nvarchar NULL, 
    [termin_fakta] nvarchar NULL, 
    [period_fakta] nvarchar NULL, 
    [vecka_fakta] nvarchar NULL, 
    [veckodag_fakta] nvarchar NULL, 
    [ämne_id] nvarchar NULL, 
    [ämne] nvarchar NULL, 
    [frånvaro_min] [float] NULL,
    [närvaro_min] [float] NULL, 
    [frånvaroorsak_id] nvarchar NULL,
    [frånvaroorsak] nvarchar NULL, 
    [beskrivning] nvarchar NULL, 
    [personal_id] nvarchar NULL, 
    [försystem] nvarchar NULL 
)

With the following non-clustered index:

CREATE NONCLUSTERED INDEX [stage_skola_närvaro_ix1] ON [sko].[stage_närvaro] 
(
    [person_id] ASC,
    [termin_fakta] ASC,
    [läsår_fakta] ASC
)

When I run the following delete query, it takes atleast 2+ hours to complete.

DELETE  sko.stage_närvaro
FROM    sko.stage_närvaro e
WHERE   försystem = 'Extens'
AND EXISTS (
    SELECT  *
    FROM    ext.v_imp_närvaro v
    WHERE   e.person_id = v.person_id
    AND     e.termin_fakta = v.termin_fakta
    AND     e.läsår_fakta = v.läsår_fakta
)

Is my delete-query using my index? Would it help to disable the index before deleting, and enable it afterwards?

Edit:
The view ext.v_imp_närvaro has the same amount of rows as the table sko.stage_närvaro.

Edit2:
I suspected that it was an I/O issue, so I ran the following query as suggested by DaniSQL here:

```
SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_res

I don't know fully details of your environment, i.e. if the tables in question are mostly used for writing or reading.

How often you do this delete?

what is the primary key and clustered index of [sko].[stage_närvaro]?

If I wanted to optimise this delete there are a few things I would consider:

1) an index on the underlying tables of the view ext.v_imp_närvaro with the columns used in the select (person_id, termin_fakta,[läsår_fakta]) you want an index seek there most likely (no need to include any columns because you are just going there for the EXISTS)

2) I have been using a lot filtered indexes and I would consider the following:

CREATE NONCLUSTERED INDEX IDXF_STAGE_NARVARO_FORSYSTEMS_EXTENS
ON [sko].[stage_närvaro] ([försystem])
INCLUDE ([person_id],[termin_fakta],[läsår_fakta])
WHERE [försystem] = 'Extens'

3) I can see some LCK_M_S going on there, not sure if related to this query in particular but nevertheless I try to always use ROWCOUNT and do big deletes and updates in batches, something like the following:

USE DATABASENAME
    GO

    DECLARE @RC INT
    SELECT @RC = 0
    SET ROWCOUNT  5000

    WHILE (1 = 1)
      BEGIN
        BEGIN TRANSACTION

                        DELETE  sko.stage_närvaro
                        FROM    sko.stage_närvaro e
                        WHERE   försystem = 'Extens'
                        AND EXISTS (
                            SELECT  *
                            FROM    ext.v_imp_närvaro v
                            WHERE   e.person_id = v.person_id
                            AND     e.termin_fakta = v.termin_fakta
                            AND     e.läsår_fakta = v.läsår_fakta
                        )

                     SELECT @RC = @@ROWCOUNT

                     print CAST ( DB_NAME()  AS VARCHAR(500) ) + 
' -- ' + CAST ( @RC  AS VARCHAR(10) ) + ' -->  ' + 
CAST( GETDATE() AS VARCHAR(25))

                     WAITFOR DELAY '00:00:01';

        IF @RC = 0
          BEGIN
            COMMIT TRANSACTION

            BREAK
          END

        COMMIT TRANSACTION
      END

    SET ROWCOUNT  0

 --==============================================================
 --SET ROWCOUNT 10000 -- define maximum updated rows at once

-- DO THE UPDATE

-- don't forget about bellow 
-- after everything is updated
--SET ROWCOUNT 0

-- Setting ROWCOUNT to 0 turn off limits - don't forget about it.
--===============================================================

This may not be a comprehensive solution, because there are bits missing on the question too, however, it will surely give you some ideas as possible ways to improve big delete operations.

StackExchange Database Administrators Q#144172, answer score: 5