degrading performance on one table

Hello, we use one table in our SQL2k SP3a server for storing all sorts of
parameters. See the description below. It contains about 150.000 records.
What we see happening over the day is that the performance on accessing this
table deteriorates. When joins are made with other tables that use the
parameter table, they get slow too. It is very fast when SQL is freshly
started, but after a day or two spurious locks show up (I assume because of
the lack of response), and at somepoint I can't even do a select count (*)
anymore. Takes forever. There are no locks when I do this, I just wait
forever. Restarting SQL solved the problem, after that it is as fast as
ever!
This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
particular problem. Index has been defragged, to no avail.
Are we missing something obvious? Pointers as to where to look?
René
CREATE TABLE [dbo].[parameter] (
[entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[entity_id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
(
[entity_name],
[entity_id],
[name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOYou mentioned that you did reindex.
Do you do any deletes and updates on the table?
You mentioned no locks on the table when you run count(*). Is the server
performing bad for other tables at that time? Are there any open
transactions (run dbcc opentran). how about DBCC SHOWCONTIG (tablename)
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||Did you look at the query execution plan using one or more typical
"slowed-down" queries? Are the correct indexes being used? How about update
statistics? Is there tempdb issue 'cause it seems OK after service restart?
Any Perfmon findings on memory, processor, disk I/O counters?
Richard
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||I did have a look at temdb, and there was 98% unused space.. But in total it
is currently only
The query plans for a typical query looks ok, In fact, right after a
restart, that query is really fast - 0 second responses. The indexes look
okay, we experimented earlier with different indexes.
There is no change in load on CPU, memory of disk I/Owhen the performance
goed bad... CPU (2) are at maxed out at 50, sometimes peeking when a
full-text query is requested.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uIE7$0sUDHA.360@.TK2MSFTNGP11.phx.gbl...
> Did you look at the query execution plan using one or more typical
> "slowed-down" queries? Are the correct indexes being used? How about
update
> statistics? Is there tempdb issue 'cause it seems OK after service
restart?
> Any Perfmon findings on memory, processor, disk I/O counters?
>
> Richard
> "René" <rene.de.vries/atsign/kexdotnl> wrote in message
> news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> > Hello, we use one table in our SQL2k SP3a server for storing all sorts
of
> > parameters. See the description below. It contains about 150.000
records.
> >
> > What we see happening over the day is that the performance on accessing
> this
> > table deteriorates. When joins are made with other tables that use the
> > parameter table, they get slow too. It is very fast when SQL is freshly
> > started, but after a day or two spurious locks show up (I assume because
> of
> > the lack of response), and at somepoint I can't even do a select count
(*)
> > anymore. Takes forever. There are no locks when I do this, I just wait
> > forever. Restarting SQL solved the problem, after that it is as fast as
> > ever!
> >
> > This is REALLY puzzling us. We've run traces, etc- nothing to indicate
any
> > particular problem. Index has been defragged, to no avail.
> >
> > Are we missing something obvious? Pointers as to where to look?
> >
> > René
> >
> > CREATE TABLE [dbo].[parameter] (
> > [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> > ,
> > [entity_id] [int] NOT NULL ,
> > [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> > CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> > (
> > [entity_name],
> > [entity_id],
> > [name]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> >
>