Many to many table - index design question

Hi
I have a Many-to-Many table , like
Author_ID int
Book_ID int
What is the best practices in designing the indexes for this table?
Of course the table should not be clustered, do you all agree on that?
So my options is a combined compound index on both fields. The
advantage
is that I get "is unique" automatically.
Or I create two separate indexes, one for each field.
What a bout primary keys? I guess I should set that on both? But not
making it
clustered?
What do you recommend that I do here?
//Andy
I would use both columns in the primary key, and yes I would cluster
it. And I would use both columns in the reverse order in a
non-clustered unique index, so it would be indexed both ways.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Feb 2008 07:43:51 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:

>Hi
>I have a Many-to-Many table , like
>Author_ID int
>Book_ID int
>What is the best practices in designing the indexes for this table?
>Of course the table should not be clustered, do you all agree on that?
>So my options is a combined compound index on both fields. The
>advantage
>is that I get "is unique" automatically.
>Or I create two separate indexes, one for each field.
>What a bout primary keys? I guess I should set that on both? But not
>making it
>clustered?
>What do you recommend that I do here?
>//Andy
|||Thanks for the reply.
Why would you want to have a clustered index on a many-to-many column?
I mean there's nothing sequencial, increasing and
stuff are added and deleted in random order. I guess inserts will take
quite some time if the DB has to phyiscally re-order the
data? Or am I wrong?
Why is it better to do clustering in this case?
//andy
|||On Thu, 21 Feb 2008 14:37:46 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:

>Thanks for the reply.
>Why would you want to have a clustered index on a many-to-many column?
>I mean there's nothing sequencial, increasing and
>stuff are added and deleted in random order. I guess inserts will take
>quite some time if the DB has to phyiscally re-order the
>data? Or am I wrong?
>Why is it better to do clustering in this case?
>//andy
I want two indexes, and both indexes "cover" all columns in the table.
If neither index is clustered there will be the table in a heap, a
complete copy of the table in the first index, and a complete copy of
the table in the second index. Three copies. If we cluster one of
the indexes, the leaf level is the base table, so we only have two
complete copies.
Roy Harvey
Beacon Falls, CT
|||Generally, but not always, tables with clustered indexes perform better than
heaps (the term for a table without a clustered index).
It is true that doing an insert that inserts a row that row needs to go into
a page that was full, the that page will have to be split, but while that
costs something, it's normally not that bad. I'm not quite sure what you
mean by "physically reorder the data", but I suspect you are thinking that
something goes on that is very drastic and that's not true.
You may want to read
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
for a case study of a comparison of performance results with and without a
clustered index.
Of course, as always, "your mileage may vary". And it is possible that with
your data and your particular combination os selects, updates, inserts, and
deletes the heap will be faster. The only way to know is for you to test it
with your data. But my guess is that you will find either the clustered
index is faster, or there is no significant difference in performance.
If it were my system, unless I had some a priori reason to believe this
table was going to be a major performance bottleneck in my system, I would
just use a clustered index since that should usually turn out to be the
correct choice. If it turned out to be a performance problem, then I would
would run tests to see if I either made the wrong choice of keys for the
clustered index and/or if a heap was faster.
Tom
"Sune42" <sune42@.hotmail.com> wrote in message
news:9e5429c5-1f73-4a0b-9b70-6a9cff80803a@.c33g2000hsd.googlegroups.com...
> Thanks for the reply.
> Why would you want to have a clustered index on a many-to-many column?
> I mean there's nothing sequencial, increasing and
> stuff are added and deleted in random order. I guess inserts will take
> quite some time if the DB has to phyiscally re-order the
> data? Or am I wrong?
> Why is it better to do clustering in this case?
> //andy

Many to many table - index design question

Hi
I have a Many-to-Many table , like
Author_ID int
Book_ID int
What is the best practices in designing the indexes for this table?
Of course the table should not be clustered, do you all agree on that?
So my options is a combined compound index on both fields. The
advantage
is that I get "is unique" automatically.
Or I create two separate indexes, one for each field.
What a bout primary keys? I guess I should set that on both? But not
making it
clustered?
What do you recommend that I do here?
//AndyI would use both columns in the primary key, and yes I would cluster
it. And I would use both columns in the reverse order in a
non-clustered unique index, so it would be indexed both ways.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Feb 2008 07:43:51 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:
>Hi
>I have a Many-to-Many table , like
>Author_ID int
>Book_ID int
>What is the best practices in designing the indexes for this table?
>Of course the table should not be clustered, do you all agree on that?
>So my options is a combined compound index on both fields. The
>advantage
>is that I get "is unique" automatically.
>Or I create two separate indexes, one for each field.
>What a bout primary keys? I guess I should set that on both? But not
>making it
>clustered?
>What do you recommend that I do here?
>//Andy|||Thanks for the reply.
Why would you want to have a clustered index on a many-to-many column?
I mean there's nothing sequencial, increasing and
stuff are added and deleted in random order. I guess inserts will take
quite some time if the DB has to phyiscally re-order the
data? Or am I wrong?
Why is it better to do clustering in this case?
//andy|||On Thu, 21 Feb 2008 14:37:46 -0800 (PST), Sune42 <sune42@.hotmail.com>
wrote:
>Thanks for the reply.
>Why would you want to have a clustered index on a many-to-many column?
>I mean there's nothing sequencial, increasing and
>stuff are added and deleted in random order. I guess inserts will take
>quite some time if the DB has to phyiscally re-order the
>data? Or am I wrong?
>Why is it better to do clustering in this case?
>//andy
I want two indexes, and both indexes "cover" all columns in the table.
If neither index is clustered there will be the table in a heap, a
complete copy of the table in the first index, and a complete copy of
the table in the second index. Three copies. If we cluster one of
the indexes, the leaf level is the base table, so we only have two
complete copies.
Roy Harvey
Beacon Falls, CT|||Generally, but not always, tables with clustered indexes perform better than
heaps (the term for a table without a clustered index).
It is true that doing an insert that inserts a row that row needs to go into
a page that was full, the that page will have to be split, but while that
costs something, it's normally not that bad. I'm not quite sure what you
mean by "physically reorder the data", but I suspect you are thinking that
something goes on that is very drastic and that's not true.
You may want to read
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/clusivsh.mspx
for a case study of a comparison of performance results with and without a
clustered index.
Of course, as always, "your mileage may vary". And it is possible that with
your data and your particular combination os selects, updates, inserts, and
deletes the heap will be faster. The only way to know is for you to test it
with your data. But my guess is that you will find either the clustered
index is faster, or there is no significant difference in performance.
If it were my system, unless I had some a priori reason to believe this
table was going to be a major performance bottleneck in my system, I would
just use a clustered index since that should usually turn out to be the
correct choice. If it turned out to be a performance problem, then I would
would run tests to see if I either made the wrong choice of keys for the
clustered index and/or if a heap was faster.
Tom
"Sune42" <sune42@.hotmail.com> wrote in message
news:9e5429c5-1f73-4a0b-9b70-6a9cff80803a@.c33g2000hsd.googlegroups.com...
> Thanks for the reply.
> Why would you want to have a clustered index on a many-to-many column?
> I mean there's nothing sequencial, increasing and
> stuff are added and deleted in random order. I guess inserts will take
> quite some time if the DB has to phyiscally re-order the
> data? Or am I wrong?
> Why is it better to do clustering in this case?
> //andy

many "or" operation make system choose incorrect index

Hi All,

I have one question about many "or" operation make system choose
incorrect index

There is one table TT (
C1 VARCHAR(15) NOT NULL,
C2 VARCHAR(15) NOT NULL,
C3 VARCHAR(15) NOT NULL,
C4 VARCHAR(15) NOT NULL
C5 VARCHAR2(200),
)

Primary Key TT_PK (C1, C2, C3, C4)

SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND ((C2 =
'07RES' AND C3 = '00000' AND C4 = '02383') OR (C2 = '07RES' AND
C3 = '00000' AND C4 = '02382') OR (C2 = '07RES' AND C3 = '00000'
AND C4 = '02381') OR (C2 = '07RES' AND C3 = '00000' AND C4 =
'02380') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02379') OR
(C2 = '07RES' AND C3 = '00000' AND C4 = '02378') OR (C2 = '07RES'
AND C3 = '00000' AND C4 = '02377') OR (C2 = '07RES' AND C3 =
'00000' AND C4 = '02376') OR (C2 = '07RES' AND C3 = '00000' AND
C4 = '02375') OR (C2 = '07RES' AND C3 = '00000' AND C4 =
'02374') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02373') OR
(C2 = '07RES' AND C3 = '00000' AND C4 = '02372')
... about 100 or operations
OR (C2 = '07COM' AND C3 = '00000' AND C4 = '00618') OR (C2 =
'07COM' AND C3 = '00000' AND C4 = '00617') OR (C2 = '07COM' AND
C3 = '00000' AND C4 = '00616') OR (C2 = '07COM' AND C3 = '00000'
AND C4 = '00608') )

The system choose index prefix, and query all index leaf with
C1='TEST'

Prefix: [dbo].[TT].C1 = 'TEST'

After I reduce the OR operators to 50, it use choose

Prefix: [dbo].[TT].C1, [dbo].[TT].C2,[dbo].[TT].C3,[dbo].[TT].C4=
'TEST, '07RES', '00000', '02383'
Then Merge Join, it is very quick,

Can anyone help on this, do I have to reduce the OR operator to 50?

Thanks in advance!lsllcm,

Is there a join in the query? I get the feeling you did not post all
relevant parts of the query. Why would you get a merge join? And what
does "The system choose index prefix" mean?

There is no hard or fast rule for this. Although I could imagine that
too many predicates would disqualify index seeks, in general it is all
about selectivity. During compilation the optimizer will try to
determine whether index seeks (followed by bookmark lookups) are faster
than (partially) scanning the (clustered) index, based on the estimate
of the number of qualifying rows.

Please note that there is a certain point at which the compilation time
grows a lot for each addition predicate you add to the WHERE clause. If
the compilation time exceeds the estimated gains, the optimizer will
stop compilation and simply choose a "good enough" plan.

If the performance of this query is very important to you, and the
structure of the predicates is as "simple" and predictable as your
example, then you could consider rewriting the query as below:

SELECT C1, C2, C3, C4
FROM TT
WHERE C1 = 'TEST'
AND C2 = '07RES'
AND C3 = '00000'
AND C4 IN ('02383','02382','02381','02380','02379', ...)
UNION ALL
SELECT C1, C2, C3, C4
FROM TT
WHERE C1 = 'TEST'
AND C2 = '07COM'
AND C3 = '00000'
AND C4 IN ('00618','00617','00616', ...)

--
Gert-Jan

lsllcm wrote:

Quote:

Originally Posted by

>
Hi All,
>
I have one question about many "or" operation make system choose
incorrect index
>
There is one table TT (
C1 VARCHAR(15) NOT NULL,
C2 VARCHAR(15) NOT NULL,
C3 VARCHAR(15) NOT NULL,
C4 VARCHAR(15) NOT NULL
C5 VARCHAR2(200),
)
>
Primary Key TT_PK (C1, C2, C3, C4)
>
SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND ((C2 =
'07RES' AND C3 = '00000' AND C4 = '02383') OR (C2 = '07RES' AND
C3 = '00000' AND C4 = '02382') OR (C2 = '07RES' AND C3 = '00000'
AND C4 = '02381') OR (C2 = '07RES' AND C3 = '00000' AND C4 =
'02380') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02379') OR
(C2 = '07RES' AND C3 = '00000' AND C4 = '02378') OR (C2 = '07RES'
AND C3 = '00000' AND C4 = '02377') OR (C2 = '07RES' AND C3 =
'00000' AND C4 = '02376') OR (C2 = '07RES' AND C3 = '00000' AND
C4 = '02375') OR (C2 = '07RES' AND C3 = '00000' AND C4 =
'02374') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02373') OR
(C2 = '07RES' AND C3 = '00000' AND C4 = '02372')
... about 100 or operations
OR (C2 = '07COM' AND C3 = '00000' AND C4 = '00618') OR (C2 =
'07COM' AND C3 = '00000' AND C4 = '00617') OR (C2 = '07COM' AND
C3 = '00000' AND C4 = '00616') OR (C2 = '07COM' AND C3 = '00000'
AND C4 = '00608') )
>
The system choose index prefix, and query all index leaf with
C1='TEST'
>
Prefix: [dbo].[TT].C1 = 'TEST'
>
After I reduce the OR operators to 50, it use choose
>
Prefix: [dbo].[TT].C1, [dbo].[TT].C2,[dbo].[TT].C3,[dbo].[TT].C4=
'TEST, '07RES', '00000', '02383'
Then Merge Join, it is very quick,
>
Can anyone help on this, do I have to reduce the OR operator to 50?
>
Thanks in advance!

|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:

Quote:

Originally Posted by

Please note that there is a certain point at which the compilation time
grows a lot for each addition predicate you add to the WHERE clause. If
the compilation time exceeds the estimated gains, the optimizer will
stop compilation and simply choose a "good enough" plan.

Indeed. Many OR clauses, or many values in IN can result in horrendeous
compilation times. SQL 2005 fare a lot better than SQL 2000, but the cost
is still high.

One thing I've notice that when there are more than 63 values (I think
that was the value), SQL Server stashes all the constants into a work
table, and you get the same result as you had the values in a temp table.
At least that was what I saw in a test that I ran.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Gert-Jan,

Is there a join in the query? I get the feeling you did not post all
relevant parts of the query. Why would you get a merge join? And what
does "The system choose index prefix" mean?

There is no join in the query, I think the merge join is to merge the
results of different OR.

The system choose run the partial index of beginning "SERV_PROV_CODE".

Thanks
Jacky|||Thank you|||The version mssql 2005 sp1

Managing Indexes on large tables

Hi I wonder if anyone could give some advice around managing a non clustered
Index on a large table:
rows reserved data
index_size unused
229324002 27215584 KB 20200600 KB 6940968 KB 74016
KB
We keep getting problems with this index as it is on a large & busy table -
Ideally I'd rebuild the index every day but it is a very busy production
system & that just isn't practical.
Would Index defragmentation help with Invalid key/corruption issues & the
general health of this index? or has anyone any advice on mangement
strategies for big tables/indexes'
Many Thanks for any input on this!
--
Mike Knee
Attenda Monitoring & ManagementDefragmentation is done for performance purposes. I don't know what you mean by "invalid key", but
if you have corruption issues, you need to get to the root cause of this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com> wrote in message
news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non clustered
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB 74016
> KB
> We keep getting problems with this index as it is on a large & busy table -
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes'
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management|||If you are getting errors sucha s that you most likely have corruption in
the index. Since it is a nonclustered index I suggest you drop and recreate
it or run DBCC DBREINDEX on just that index to be sure you get a clean
index. It might be busy but you need to fix it. It's unlikely that you need
to rebuild it every night. If you do then you may want to adjust your fill
factor to avoid the pagesplits.
--
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:6195F440-E5C7-44E7-B162-70E5AA99A8CE@.microsoft.com...
> Hi I wonder if anyone could give some advice around managing a non
> clustered
> Index on a large table:
> rows reserved data
> index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB
> 74016
> KB
> We keep getting problems with this index as it is on a large & busy
> table -
> Ideally I'd rebuild the index every day but it is a very busy production
> system & that just isn't practical.
> Would Index defragmentation help with Invalid key/corruption issues & the
> general health of this index? or has anyone any advice on mangement
> strategies for big tables/indexes'
> Many Thanks for any input on this!
> --
> Mike Knee
> Attenda Monitoring & Management|||On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
<SinisterChinaPenguin@.discussions.microsoft.com> wrote:
>Hi I wonder if anyone could give some advice around managing a non clustered
>Index on a large table:
> rows reserved data index_size unused
> 229324002 27215584 KB 20200600 KB 6940968 KB 74016 KB
Well, that's reasonably large, alrighty!
>We keep getting problems with this index as it is on a large & busy table -
>Ideally I'd rebuild the index every day but it is a very busy production
>system & that just isn't practical.
>Would Index defragmentation help with Invalid key/corruption issues & the
>general health of this index? or has anyone any advice on mangement
>strategies for big tables/indexes'
That is a lot of rows on one table. Have you looked at partitioned
views (SQL2000) or partitioned tables (SQL2005)?
Only situation I've had with that many rows was with them split into
ten tables and joined with a partitioned view, had no problems with
that.
Can you say what is going on with that table when the problems occur,
I presume not all select's?
Also, what the PK is, and what the clustered key is, if different?
For that matter, what is the key in this index - single field int, or
multiple varchars, or what?
J.|||Thanks for the replies - some more details: (System is SQL2000 clustered on
Win2K)
- The table collects data points for I.T. systems performance (perfmons etc)
on 800 odd servers so there are hundreds of Inserts going on 24/7 & no
updates.
- Once a day a maintenance job runs to delete any data points > 200 days old
so the size of the table stays roughly the same...
- This table has just the one Non-Clustered Index on DataID(int - non
unique) , Time(Int) & Value (float)
- The DataID is non unique as it id defined in a "Header" table which
contains all the definitions for the performance data being collected (name
of perfmon, system name etc), the V large table I originally posted about
holds the actual performance data so there are thousands of entries for each
dataID.
- The Table is part of a commercial product (NetIQ's AppManager) so I have
no control over the schema/Indexes etc.
I'm not sure when the problems occur - I'm gussing during a flurry of Inserts?
I hope this all makes sense - I can't help but think this table could be
managed better - should I force a Full table lock for example when the
deletes are taking place? or can I manage the Index better (hence the
questions about defragging/rebuilding)
Thanks again for any advice or ideas.
--
Mike Knee
Attenda Monitoring & Management
"JXStern" wrote:
> On Fri, 10 Feb 2006 11:03:29 -0800, "Sinister China Penguin"
> <SinisterChinaPenguin@.discussions.microsoft.com> wrote:
> >Hi I wonder if anyone could give some advice around managing a non clustered
> >Index on a large table:
> >
> > rows reserved data index_size unused
> > 229324002 27215584 KB 20200600 KB 6940968 KB 74016 KB
> Well, that's reasonably large, alrighty!
> >We keep getting problems with this index as it is on a large & busy table -
> >Ideally I'd rebuild the index every day but it is a very busy production
> >system & that just isn't practical.
> >
> >Would Index defragmentation help with Invalid key/corruption issues & the
> >general health of this index? or has anyone any advice on mangement
> >strategies for big tables/indexes'
> That is a lot of rows on one table. Have you looked at partitioned
> views (SQL2000) or partitioned tables (SQL2005)?
> Only situation I've had with that many rows was with them split into
> ten tables and joined with a partitioned view, had no problems with
> that.
> Can you say what is going on with that table when the problems occur,
> I presume not all select's?
> Also, what the PK is, and what the clustered key is, if different?
> For that matter, what is the key in this index - single field int, or
> multiple varchars, or what?
> J.
>|||Sinister China Penguin wrote:
> Thanks for the replies - some more details: (System is SQL2000
> clustered on Win2K)
> - The table collects data points for I.T. systems performance
> (perfmons etc) on 800 odd servers so there are hundreds of Inserts
> going on 24/7 & no updates.
> - Once a day a maintenance job runs to delete any data points > 200
> days old so the size of the table stays roughly the same...
> - This table has just the one Non-Clustered Index on DataID(int - non
> unique) , Time(Int) & Value (float)
> - The DataID is non unique as it id defined in a "Header" table which
> contains all the definitions for the performance data being collected
> (name of perfmon, system name etc), the V large table I originally
> posted about holds the actual performance data so there are thousands
> of entries for each dataID.
> - The Table is part of a commercial product (NetIQ's AppManager) so I
> have
> no control over the schema/Indexes etc.
> I'm not sure when the problems occur - I'm gussing during a flurry of
> Inserts?
> I hope this all makes sense - I can't help but think this table could
> be managed better - should I force a Full table lock for example when
> the deletes are taking place? or can I manage the Index better (hence
> the questions about defragging/rebuilding)
> Thanks again for any advice or ideas.
It sounds as if having a clustered index on the timestamp could be a good
idea.I'm guessing that you query and delete data based on timestamp so
this index probably would help both. But mind you it'll take considerable
time and space to create it - it's likely that it'll be even too resource
intensive in your case.
I still do not understand what problems you have. Did you get any error
messages about a bad index or IO errors? If so then something seems to be
seriously wrong with your db. Are queries slow? Inserts?
Regards
robert|||Really I'm just afterany general advice around looking after this big, busy
table - the Integrity checks do seem to come up with Index problems quite
often & I wanted to make sure I was doing all the right things to keep it
working well.
For Example I don't really fully understand fragmentattion of Non Clustered
Indexes should I be defragging often? or is it not worth it? would it help to
defrag/rebuild indexes before or after this big daily delete from the table?
or doesn't it matter?
Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna make
sure i'm doing things properly!
Cheers
--
Mike Knee
Attenda Monitoring & Management
"Robert Klemme" wrote:
> Sinister China Penguin wrote:
> > Thanks for the replies - some more details: (System is SQL2000
> > clustered on Win2K)
> >
> > - The table collects data points for I.T. systems performance
> > (perfmons etc) on 800 odd servers so there are hundreds of Inserts
> > going on 24/7 & no updates.
> > - Once a day a maintenance job runs to delete any data points > 200
> > days old so the size of the table stays roughly the same...
> > - This table has just the one Non-Clustered Index on DataID(int - non
> > unique) , Time(Int) & Value (float)
> > - The DataID is non unique as it id defined in a "Header" table which
> > contains all the definitions for the performance data being collected
> > (name of perfmon, system name etc), the V large table I originally
> > posted about holds the actual performance data so there are thousands
> > of entries for each dataID.
> > - The Table is part of a commercial product (NetIQ's AppManager) so I
> > have
> > no control over the schema/Indexes etc.
> >
> > I'm not sure when the problems occur - I'm gussing during a flurry of
> > Inserts?
> >
> > I hope this all makes sense - I can't help but think this table could
> > be managed better - should I force a Full table lock for example when
> > the deletes are taking place? or can I manage the Index better (hence
> > the questions about defragging/rebuilding)
> >
> > Thanks again for any advice or ideas.
> It sounds as if having a clustered index on the timestamp could be a good
> idea.I'm guessing that you query and delete data based on timestamp so
> this index probably would help both. But mind you it'll take considerable
> time and space to create it - it's likely that it'll be even too resource
> intensive in your case.
> I still do not understand what problems you have. Did you get any error
> messages about a bad index or IO errors? If so then something seems to be
> seriously wrong with your db. Are queries slow? Inserts?
> Regards
> robert
>|||Robert is correct in that the table should have a clustered index on
datetime. It should make the inserts and especially the deletes smoother
and faster. Does the nonclustered index cover all the queries on the table
properly? How are the deletes being done now? Are they in small batches or
one big delete statement each day?
--
Andrew J. Kelly SQL MVP
"Sinister China Penguin" <SinisterChinaPenguin@.discussions.microsoft.com>
wrote in message news:EE3A13E0-7D0D-4E30-90FF-6CF5AE5E7501@.microsoft.com...
> Really I'm just afterany general advice around looking after this big,
> busy
> table - the Integrity checks do seem to come up with Index problems quite
> often & I wanted to make sure I was doing all the right things to keep it
> working well.
> For Example I don't really fully understand fragmentattion of Non
> Clustered
> Indexes should I be defragging often? or is it not worth it? would it help
> to
> defrag/rebuild indexes before or after this big daily delete from the
> table?
> or doesn't it matter?
> Sorry to be so vauge - my DBA knowledge is a bit patchy & I just wanna
> make
> sure i'm doing things properly!
> Cheers
> --
> Mike Knee
> Attenda Monitoring & Management
>
> "Robert Klemme" wrote:
>> Sinister China Penguin wrote:
>> > Thanks for the replies - some more details: (System is SQL2000
>> > clustered on Win2K)
>> >
>> > - The table collects data points for I.T. systems performance
>> > (perfmons etc) on 800 odd servers so there are hundreds of Inserts
>> > going on 24/7 & no updates.
>> > - Once a day a maintenance job runs to delete any data points > 200
>> > days old so the size of the table stays roughly the same...
>> > - This table has just the one Non-Clustered Index on DataID(int - non
>> > unique) , Time(Int) & Value (float)
>> > - The DataID is non unique as it id defined in a "Header" table which
>> > contains all the definitions for the performance data being collected
>> > (name of perfmon, system name etc), the V large table I originally
>> > posted about holds the actual performance data so there are thousands
>> > of entries for each dataID.
>> > - The Table is part of a commercial product (NetIQ's AppManager) so I
>> > have
>> > no control over the schema/Indexes etc.
>> >
>> > I'm not sure when the problems occur - I'm gussing during a flurry of
>> > Inserts?
>> >
>> > I hope this all makes sense - I can't help but think this table could
>> > be managed better - should I force a Full table lock for example when
>> > the deletes are taking place? or can I manage the Index better (hence
>> > the questions about defragging/rebuilding)
>> >
>> > Thanks again for any advice or ideas.
>> It sounds as if having a clustered index on the timestamp could be a good
>> idea.I'm guessing that you query and delete data based on timestamp so
>> this index probably would help both. But mind you it'll take
>> considerable
>> time and space to create it - it's likely that it'll be even too resource
>> intensive in your case.
>> I still do not understand what problems you have. Did you get any error
>> messages about a bad index or IO errors? If so then something seems to
>> be
>> seriously wrong with your db. Are queries slow? Inserts?
>> Regards
>> robert
>>