5 added 985 characters in body
source | link

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

Take this very simple example:

IF OBJECT_ID(N'dbo.DeleteTest', N'U') IS NOT NULL
DROP TABLE dbo.DeleteTest;
GO
CREATE TABLE dbo.DeleteTest
(
    table_id int NOT NULL
        CONSTRAINT PK_DeleteTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(2000) NOT NULL
        CONSTRAINT DF_DeleteTest_someVal
        DEFAULT ((CRYPT_GEN_RANDOM(2000)))
);
GO

--insert 500 rows
INSERT INTO dbo.DeleteTest DEFAULT VALUES;
GO 500

--turn on "Actual" execution plans
DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 90;

The actual execution plan for the above DELETE:

enter image description here You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Now, if we add an additional non-clustered index, and run another delete:

CREATE INDEX IX_DeleteTest
ON dbo.DeleteTest (table_id);

DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 91;

We see the following plan:

enter image description here

As you can see in the two images, the second delete costs nearly twice as much; 0.0132841 for the first delete, and 0.0232851 for the second delete.

If you use SentryOne Plan Explorer (free!) to look at execution plans, you can see the number of additional non-clustered index-delete operations taking place:

enter image description here

Looking at your deadlock graph, in combination with the list of non-clustered indexes in your question, it looks like the deadlock is likely caused by simultaneous deletes of rows contained on the same page. Adding a ROWLOCK hint to your delete may prevent this particular deadlock. You could also test using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; prior to the delete operation. Be aware that changes the isolation level for all statements occuring afterwards within the same batch. Ensure you reset the transaction isolation level if you execute other statements after the delete operation.

The deadlock shows the wait resource is the same for both statements, except the row number: waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)" The row number is the number inside brackets. @Kin shows a great way to look at the details of the waitresource in his answer here

ROWLOCK
Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

Take this very simple example:

IF OBJECT_ID(N'dbo.DeleteTest', N'U') IS NOT NULL
DROP TABLE dbo.DeleteTest;
GO
CREATE TABLE dbo.DeleteTest
(
    table_id int NOT NULL
        CONSTRAINT PK_DeleteTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(2000) NOT NULL
        CONSTRAINT DF_DeleteTest_someVal
        DEFAULT ((CRYPT_GEN_RANDOM(2000)))
);
GO

--insert 500 rows
INSERT INTO dbo.DeleteTest DEFAULT VALUES;
GO 500

--turn on "Actual" execution plans
DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 90;

The actual execution plan for the above DELETE:

enter image description here You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Now, if we add an additional non-clustered index, and run another delete:

CREATE INDEX IX_DeleteTest
ON dbo.DeleteTest (table_id);

DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 91;

We see the following plan:

enter image description here

As you can see in the two images, the second delete costs nearly twice as much; 0.0132841 for the first delete, and 0.0232851 for the second delete.

If you use SentryOne Plan Explorer (free!) to look at execution plans, you can see the number of additional non-clustered index-delete operations taking place:

enter image description here

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

Take this very simple example:

IF OBJECT_ID(N'dbo.DeleteTest', N'U') IS NOT NULL
DROP TABLE dbo.DeleteTest;
GO
CREATE TABLE dbo.DeleteTest
(
    table_id int NOT NULL
        CONSTRAINT PK_DeleteTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(2000) NOT NULL
        CONSTRAINT DF_DeleteTest_someVal
        DEFAULT ((CRYPT_GEN_RANDOM(2000)))
);
GO

--insert 500 rows
INSERT INTO dbo.DeleteTest DEFAULT VALUES;
GO 500

--turn on "Actual" execution plans
DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 90;

The actual execution plan for the above DELETE:

enter image description here You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Now, if we add an additional non-clustered index, and run another delete:

CREATE INDEX IX_DeleteTest
ON dbo.DeleteTest (table_id);

DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 91;

We see the following plan:

enter image description here

As you can see in the two images, the second delete costs nearly twice as much; 0.0132841 for the first delete, and 0.0232851 for the second delete.

If you use SentryOne Plan Explorer (free!) to look at execution plans, you can see the number of additional non-clustered index-delete operations taking place:

enter image description here

Looking at your deadlock graph, in combination with the list of non-clustered indexes in your question, it looks like the deadlock is likely caused by simultaneous deletes of rows contained on the same page. Adding a ROWLOCK hint to your delete may prevent this particular deadlock. You could also test using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; prior to the delete operation. Be aware that changes the isolation level for all statements occuring afterwards within the same batch. Ensure you reset the transaction isolation level if you execute other statements after the delete operation.

The deadlock shows the wait resource is the same for both statements, except the row number: waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)" The row number is the number inside brackets. @Kin shows a great way to look at the details of the waitresource in his answer here

ROWLOCK
Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

4 added 6 characters in body
source | link

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

YouTake this very simple example:

IF OBJECT_ID(N'dbo.DeleteTest', N'U') IS NOT NULL
DROP TABLE dbo.DeleteTest;
GO
CREATE TABLE dbo.DeleteTest
(
    table_id int NOT NULL
        CONSTRAINT PK_DeleteTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(2000) NOT NULL
        CONSTRAINT DF_DeleteTest_someVal
        DEFAULT ((CRYPT_GEN_RANDOM(2000)))
);
GO

--insert 500 rows
INSERT INTO dbo.DeleteTest DEFAULT VALUES;
GO 500

--turn on "Actual" execution plans
DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 90;

The actual execution plan for the above DELETE:

enter image description here You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Now, if we add an additional non-clustered index, and run another delete:

CREATE INDEX IX_DeleteTest
ON dbo.DeleteTest (table_id);

DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 91;

We see the following plan:

enter image description here

As you can see in the two images, the second delete costs nearly twice as much; 0.0132841 for the first delete, and 0.0232851 for the second delete.

If you use SentryOne Plan Explorer (free!) to look at execution plans, you can see the number of additional non-clustered index-delete operations taking place:

enter image description here

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

Take this very simple example:

IF OBJECT_ID(N'dbo.DeleteTest', N'U') IS NOT NULL
DROP TABLE dbo.DeleteTest;
GO
CREATE TABLE dbo.DeleteTest
(
    table_id int NOT NULL
        CONSTRAINT PK_DeleteTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(2000) NOT NULL
        CONSTRAINT DF_DeleteTest_someVal
        DEFAULT ((CRYPT_GEN_RANDOM(2000)))
);
GO

--insert 500 rows
INSERT INTO dbo.DeleteTest DEFAULT VALUES;
GO 500

--turn on "Actual" execution plans
DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 90;

The actual execution plan for the above DELETE:

enter image description here You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Now, if we add an additional non-clustered index, and run another delete:

CREATE INDEX IX_DeleteTest
ON dbo.DeleteTest (table_id);

DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 91;

We see the following plan:

enter image description here

As you can see in the two images, the second delete costs nearly twice as much; 0.0132841 for the first delete, and 0.0232851 for the second delete.

If you use SentryOne Plan Explorer (free!) to look at execution plans, you can see the number of additional non-clustered index-delete operations taking place:

enter image description here

3 added 6 characters in body
source | link

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

Since your statement is deleting rows via a WHERE clause on the primary key, table_id, adding a non-clustered index on table_id is unlikely to help, and may well increase the number of deadlocks occurring.

DELETE FROM ... deletes the row from the table (or clustered index), as well as every non-clustered index defined on the table where the row is present. Adding an extra index necessitates and extra delete operation.

You should probably add the CREATE TABLE statements to your question, along with details about the deadlock via a deadlock graph.

2 added 6 characters in body
source | link
1
source | link