Clustered vs Nonclustered PK

Ok, let me try to set the stage.

Between 2m and 5m inserts per day. NO UPDATES.

Table has a 4 part primary key. All BigInt data types. Key value 1 and 2 have a range between 1 and 100. Key values 3 and 4 are auto incrementing values (forign key values) from other tables.

Space is an issue, so we have chosen not to have an additional column for a counter field for the PK. (We would never use the field for querying.)

Users complained of query speeds, so we added a couple non clustered indexs. This brought up the query speeds a lot. But of course it slowed down the input speed a bit. Nothing dramatic, but enough so we could tell.

Now the users was to increase the amount of data by about 5X. Obviosly I'm somewhat concerend, as SQL is already spending a lot of the day pegged.

So, in looking around, since the new indexes seem to be the most help in querying, I'm thinking of dropping the PK back to a nonclustered index, so I can get rid of the over head of restructring the data table on every insert. Then maybe making one of the other indexes the clustered index. (only 2 columns in this index)

Thoughts?

I think the clustered index should be your most selective and help the most queries - it doesn't really matter if its the primary key or not as its just a constraint across the 4 fields.

As you're getting a lot of new rows each day, i'd pay some attention to the fillfactor of the indexes as this will allow space for new rows to the index and reduce fragmentation.

However, its difficult to recommend anything without knowing the query types. Perhaps post a few examples and potential frequencies.

Also, the Tuning Wizard could be your friend here and the new system views and funcs in sql2005 are really handy when it comes to assessing the cost/benefit of indexes.
|||

IF you really mean that the first two key values are ONLY between 1 and 100, then those are not near selective enough to be used for clustered indexes.

And it doesn't seem as if there there is a reason for them to be bigints.

I would most certainly engage the index tuning wizard, and a adequate 'workload'.

You may be better off with not having a clustered index on the table (at least not these combination of fields) to eliminate the constant page splits/reorganization.

|||

Well, we moved the clustered index to the index that's being used for queries.

The PK is now a nonclustered index.

Got a 30% increase in insert performance, and a notable increase in query preformance.

Oh, and the X and Y were only INTs. Not big ints. My bad.