April 24, 2009 at 1:23 pm
Thanks Gail i appreciate your suggestions.
Here is the script for the problematic table, 2 indexes on the table, and 2 queries that are coming for this table, and these queries block each other or even same queries (each coming with different where clause values).
/********************************TABLE*******************************/
CREATE TABLE [dbo].[Locks](
[Lock_SessionId] [int] NOT NULL,
[Lock_TableId] [int] NOT NULL,
[Lock_RecordId] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Lock_CreatedBy] [int] NOT NULL,
[Lock_CreatedDate] [datetime] NOT NULL,
[Lock_IsWapUser] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Lock_DeviceID] [int] NULL
) ON [PRIMARY]
/********************************INDEX 1*******************************/
CREATE UNIQUE CLUSTERED INDEX [IDX_Lock_TableId_RecordId] ON [dbo].[Locks]
(
[Lock_TableId] ASC,
[Lock_RecordId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
/********************************INDEX 2*******************************/
CREATE NONCLUSTERED INDEX [AllThree] ON [dbo].[Locks]
(
[Lock_CreatedBy] ASC,
[Lock_IsWapUser] ASC,
[Lock_DeviceID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
/********************************QUERY 1*******************************/
DELETE
FROM Locks
WHERE Lock_CreatedBy=835
AND Lock_DeviceID is null
AND Lock_IsWapUser IS NULL
/********************************QUERY 2*******************************/
(@1 int,@2 tinyint,@3 nvarchar(4000))
DELETE [LOCKS]
WHERE coalesce([Lock_TableId],@1)=@2
AND [Lock_RecordId]=@3
April 24, 2009 at 1:32 pm
You can alter the way the default read_committed isolation level works by setting the database option read_committed_snapshot on. The result would be that snapshot isolation would be used by default.
You should read about this in SQL Server 2005 Books Online, and do testing as other have suggested.
Sample command to enable read_committed_snapshot:
use master
alter database MyDatabase set allow_snapshot_isolation on
alter database MyDatabase set read_committed_snapshot on
April 24, 2009 at 1:39 pm
usman.tanveer (4/24/2009)
Thanks Gail i appreciate your suggestions.Here is the script for the problematic table, 2 indexes on the table, and 2 queries that are coming for this table, and these queries block each other or even same queries (each coming with different where clause values).
/********************************TABLE*******************************/
CREATE TABLE [dbo].[Locks](
[Lock_SessionId] [int] NOT NULL,
[Lock_TableId] [int] NOT NULL,
[Lock_RecordId] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Lock_CreatedBy] [int] NOT NULL,
[Lock_CreatedDate] [datetime] NOT NULL,
[Lock_IsWapUser] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Lock_DeviceID] [int] NULL
) ON [PRIMARY]
/********************************INDEX 1*******************************/
CREATE UNIQUE CLUSTERED INDEX [IDX_Lock_TableId_RecordId] ON [dbo].[Locks]
(
[Lock_TableId] ASC,
[Lock_RecordId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
/********************************INDEX 2*******************************/
CREATE NONCLUSTERED INDEX [AllThree] ON [dbo].[Locks]
(
[Lock_CreatedBy] ASC,
[Lock_IsWapUser] ASC,
[Lock_DeviceID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
/********************************QUERY 1*******************************/
DELETE
FROM Locks
WHERE Lock_CreatedBy=835
AND Lock_DeviceID is null
AND Lock_IsWapUser IS NULL
/********************************QUERY 2*******************************/
(@1 int,@2 tinyint,@3 nvarchar(4000))
DELETE [LOCKS]
WHERE coalesce([Lock_TableId],@1)=@2
AND [Lock_RecordId]=@3
This delete is a likely source of you problem:
/********************************QUERY 2*******************************/
(@1 int,@2 tinyint,@3 nvarchar(4000))
DELETE [LOCKS]
WHERE coalesce([Lock_TableId],@1)=@2
AND [Lock_RecordId]=@3
Since column [Lock_TableId] cannot be null, there is no reason to use the coalesce function in your where clause, and it is likely causing a table scan, instead of using the primary key for the direct lookup.
April 24, 2009 at 1:51 pm
Michael Valentine Jones (4/24/2009)
This delete is a likely source of you problem:
/********************************QUERY 2*******************************/(@1 int,@2 tinyint,@3 nvarchar(4000))
DELETE [LOCKS]
WHERE coalesce([Lock_TableId],@1)=@2
AND [Lock_RecordId]=@3
Since column [Lock_TableId] cannot be null, there is no reason to use the coalesce function in your where clause, and it is likely causing a table scan, instead of using the primary key for the direct lookup.
Agreed. The function on the column means that SQL cannot use that column for an index seek. Since there's no index with Lock_RecordID as the leading column and that column is not present in the nonclustered index, SQL will have to do a clustered index (table) scan to find the rows for this delete. That means very likely table-level locks.
Why is @3 defined as NVARCHAR(4000) when the column that it's been compared to is a nchar(50)?
Edit: You have a unique clustered index on two non-null columns. Why is it not the primary key? (is there a primary key?)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 2:04 pm
This is a third party CRM application that we are using, i understand that there is no need of coalesce function but the delete query is coming from a dll and i cannot modify the query.
There is no primary key present on the table, i know its a bad design but this is how vendor has designed the database.
So the only option i have is to make changes at the database level, i can add the primary key or modify/add any index.
April 24, 2009 at 3:00 pm
In that case, I would suggest that you drop that unique clustered index and create a primary key on the columns ([Lock_RecordId], [Lock_TableId]) (In that order!)
That at least will stop that delete from having to table scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2009 at 4:30 pm
Thanks Gail, will give it a shot.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply