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
> >
> >
>

Defining a "partial" foreign Key..how could I ?

I have a table A, which has 3 fields:

- code, subcode and description (code and subcode are PK)

and a table B which has 2 fields:

- date, code, value (date and code are PK)

So far, no problem. It comes up when I try to define code in table B as foreign key pointing to code in table A, because table A has code and subcode as its PK, not only code...but, I need only code to be in table B. How could I overcome this?

Well this is kind of design issue...

You can't do it directly.. Your table design is wrong.

Try to use the following design:

CodeMaster :: code, date, value , codedescription
Here,
Code is Primary Key

SubCodeMaster :: code, subcode, subcodedescription
Here,
Code references CodeMaster::Code
Code, Subcode is Primary Key

|||

Well, unless the code column is unique in an of itself (thus making it the PK), you can't (and you shouldn't). If it is, you could apply a unique constraint to the code column, but that would be silly.

In all likelihood, you actually need a table for a code itself that relates to both tables. This would be the proper solution, since a code is a different thing than a subcode, and any code info would have to be repeate on each row

You can enforce this with triggers too, but I wouldn't suggest that as the "best" case.

Default Value?

Hi, I'm creating a dynamic group of values using SELECT and UNION

Example:
(SELECT Description = 'Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber
WHERE @.PhoneNumber <> @.old_PhoneNumber UNION ALL

SELECT Description = 'Changed FaxNumber from ' + @.old_FaxNumber + ' to ' + @.FaxNumber
WHERE @.FaxNumber <> @.old_FaxNumber UNION ALL

SELECT Description = 'Changed EmailAddress from ' + @.old_EmailAddress + ' to ' + @.EmailAddress
WHERE @.EmailAddress <> @.old_EmailAddress)

The problem here is that SQL Server thinks "Description" is an int (by default probably) and gives me an error when I try to assign a string to it.

I'm taking that information and using it as a field in a INSERT INTO ... SELECT statement, so I don't think I am able to use a DECLARE statement or if that would even work.

Does anyone know how I can make it so that Description is always a varchar?

Maybe?

SELECT 'Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber AS Description

You could also do this:

SELECT CAST('Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber AS varchar) AS Description

OR:

SELECT 'Changed PhoneNumber from ' + CAST(@.old_PhoneNumber AS varchar) + ' to ' + cast(@.PhoneNumber AS varchar) AS Description

|||

The third option worked, but I only needed to do it with the Integers. Since there were integers in the string SQL Server tried to convert the entire string into an integer across every SELECT command in the union.

So since I had an integer many SELECTs down it was telling me "can't convert name to integer" even though there was no integer in sight of that particular SELECT statement. Pretty confusing if you ask me.