June 21, 2011 at 9:54 am
We have a table in our database that gets rows inserted and deleted as users access various parts of our application. As a result, the table never has very many rows in it and there are only 10 columns. There is one nonclustered index on this table on the primarykey.
From time to time, once or twice a day, the deletion of one row from this table takes quite a long time to process and as a result, other users get blocked.
I'm not sure what would cause this simple query, "delete from mytable where id = ###" to take so long when it normally happens so fast.
I'm wondering my index is causing the problem and maybe the options on my index should be modified. Currently three options are checked - automatically compile statistics, use row locks when accessing the index, use page locks when accessing the index. I've never paid attention to these options before, could this be causing my problem? Am I updating statistics during the deletion and that's causing a delay? Am I locking more than I need to by having page locks on?
The database is running on SQL2005, but has compatibility mode of 80. The compatibility mode cannot be changed at this point due to a pending application upgrade.
June 21, 2011 at 10:02 am
Please post the DDL for the table in question, including all constraint and index definitions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 10:34 am
I apologize, I'm a SQL Dummy and I'm not familiar with the term DDL. Are you wanting the scripts to create the table and index?
June 21, 2011 at 11:15 am
Yes please. DDL stands for Data Definition Language. The term refers to any SQL code that constructs schema, e.g. CREATE TABLE, CREATE INDEX and ALTER TABLE...ADD CONSTRAINT, things like that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 11:25 am
So you have no clustered index on that table?
We'll also need the actual execution plan of the slow delete to be able to debug this.
June 21, 2011 at 11:41 am
CREATE TABLE [dbo].[MYTABLE](
[ID] [int] NOT NULL,
[CRDATETIME] [datetime] NULL,
[CREATORID] [int] NULL,
[CLASSID] [int] NULL,
[PARENTID] [int] NULL,
[USERID] [int] NULL,
[WHEN] [datetime] NULL,
[LOCKTYPEORD] [smallint] NULL,
[COMPUTERNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCKOBJECTNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FOLDERNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MYTABLE] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
June 21, 2011 at 11:46 am
Here's the execution plan.
June 21, 2011 at 11:50 am
You didn't post the same query as in the original question. That query will always delete everything...
You didn't answer my question about the clustered index on that table!
June 21, 2011 at 11:56 am
Sorry, I forgot the where clause. Here's the new execution plan.
There is no clustered index on this table. Just one index.
June 21, 2011 at 12:09 pm
Convert the PK to clustered index and you'll be fine.
June 21, 2011 at 12:35 pm
Thanks for the recommendation, I'll see if we can give this a try.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply