October 4, 2003 at 10:13 am
Hi:
I have a large table with 600 mil records. The clustered index is about 120 GB and the 8 clustered indexes consume about 300 GB. Part of the reason the NC indexes are large is because the clustered index is on 5 columns. I have a couple of questions,
1. Should I add an identity column and make that a clustered index and make a NC index on current 5 columns that the current clustered index is on. My thinking is that this would significantly reduce the size of my NC index. Other than saving space are there any other performance benefits with the approach? My current range queries use the 5 column clustered index but since they are inserted through a bath load, even my identity column would be in the same order. But I presume the range queries on the 5 column NC index would result in an extra bookmark lookup. I know I am thinking loud but by this reasoning I might just stick with my 5 column Clustered index. Space is not as big a concern as the performance for the queries.
2. My second question is, right now my 120 GB data is on a single file and 300GB NC indexes are on other single file on a different drive. I am debating whether to divide the table into two to three files and the NC indexes into 4 to 5 files, so each file is about 40 GB. Are there any advantages or disadv with this approach. One +ve is that the files will be more manageable, should I choose to move them around to new RAIDs. On -ve side it may induce some fragmentation, but then I am going to create the files large enough so there is no auto growth.
I appreciate any input or feedback.
Thanks.
October 5, 2003 at 11:33 pm
You would have to try it and see.
You could also try splittimg the table up and accessing through a partitioned view.
Depends how it is used but for something this large it is probably best for nothing to access the base table. You data is inserted via a batch load. You can create tables to support any queries required and updated these when the batch load takes place.
In this way the accessed tables will still be available while the load is taking place and will only be unavailable for the short time they are being updated so you may even get benefit from this.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply