Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Clustered index and performance

Status
Not open for further replies.

JtheRipper

IS-IT--Management
Oct 4, 2002
274
GB
Hi there,

I would like to know whether my understanding of the clustered index is correct or not.
We are having performance problems on a 50GB SQL Server 2000 database, and I am running out of ideas.

I checked the 3 bigest tables on the database, approx 10 - 30 million rows, and none of them have a clustered index. They do have quite a number (too many in my opinion for an OLTP system) of non-clustered indexes.
Am I correct in saying that it is ALWAYS a good idea to have a clustered index on a table, especially big tables such as these?
BOL also states:"CLUSTERED - Creates an object where the physical order of rows is the same as the indexed order of the rows, and the bottom (leaf) level of the clustered index contains the actual data rows"
Does this mean that the table will be recreated? I was always under the impression that by creating a clustered index you will create an index with i.e. 3 columns and only those columns' data will be the actual rows and not pointers like in non-clustered index. But after reading the BOL description a few times it looks like the table will be reorganized to "match" the index...

Any input/comments to help clear up my confusion will be greatly appreciated!

Thanks,
J.
 
clustered indexes do put the data in order - the values also I think have to be unique.

they are very good for doing specific searches of data (if you are bringing back only about 2% of the data)

be careful with them if the tables are updated regularly as each time aninser is done all the data in the table needs to be rearranged - ie it cant just add a row to the end - it neds to put the new row where it should be according to the index.

hope this helps

[bandito] [blue]DBomrrsm[/blue] [bandito]
 
dbomrrsm was correct in most info relayed except that clustered indexes DO NOT have to be unique. You can create a unique clustered index but by default, it is not created unique.



Thanks

J. Kusch
 
Thank you very much for the input!

So if I understand this correct (Q's might sound a bit silly, but I am pretty fed-up with this performance issue of ours and would really like to make the correct decision).

You should built a clustered index on the colums that have verly low cardinality, i.e. gender, and so on? (if these colums are being used frequently in queries)
It is best not to have a clustered index if a table is being updated extensively, but a good idea to have a CI where the table is mostly used for reporting purposes?

During an insert/update, the new rows are inserted in the correct order. What will this do to fragmentation? (I suspect it will be a good idea to do a defrag every once in a while if my suspicions are correct about fragmentation).

As always, thanks for any input.

J.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top