Select, insert and delete queries timing out

[Simon Withers]

I am using a sql server 2000 database to log the results from a monitor
that I have running - essentially every minuite, the table described
below has a insert and delete statements similar to the ones below run
againt it.

Everything is fine for a few weeks, and then without fail, all accesses
to the table start slowing down, to the point where even trying to
select all rows starts timing out.

At that point, the only way to make things right that I have found, is
to delete the table and recreate it.

Am I doing something specific that sql server really doesn't like? Is
there a better solution then deleting and recreating the table?

CREATE TABLE [www2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),
[success] [bit] NULL ,
[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level] [int] NULL ,
[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])
VALUES ('Error on: <a
href="http://www2.klickit.com/include/asp/system_test.asp">http://www2.k
lickit.com/include/asp/system_test.asp</a><br><br>The operation timed
out
<br><br>(Test Activated From: Lynx/2.8.2rel.1
libwww-FM/2.14)',0,1,'',getDate())

DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (Report
Not Like 'Reset
Thanks in advance,
Simon Withers
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Simon Withers (si***@klickit.com) writes:

I am using a sql server 2000 database to log the results from a monitor
that I have running - essentially every minuite, the table described
below has a insert and delete statements similar to the ones below run
againt it.

Everything is fine for a few weeks, and then without fail, all accesses
to the table start slowing down, to the point where even trying to
select all rows starts timing out.

At that point, the only way to make things right that I have found, is
to delete the table and recreate it.

Am I doing something specific that sql server really doesn't like? Is
there a better solution then deleting and recreating the table?

I can't see any indexes on the table, so once the table start to
fill up, the DELETE statement will have more and more rows to scan.
That could also cause the INSERTS to block. A clustered index on stamp
seems to be in place. Maybe Success should be included too.

You could also consider whether you really need to have the report
and listtrace columns as text, maybe a varchar(4000) each could do?


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp