I have a database that is used to store data collected from a number of channels. It is real time data so each data point has an associated DateTime value. In its most basic form it it looks like this:
I have a clustered index on the ChannelId and DataTimeStamp to allow retrieval of data over a given time span. This works well for queries but the index fragments, and rebuilding is getting slower as the table grows in size.
I've looked changing the fill factor but I think this would be effective only if data was inserted with a random DataTimeStamp, rather than monotonically increasing.
Is there anything I can do to reduce the fragmentation? The only option I can see would be to create a separate table for each channel.
asked Feb 02 '10 at 06:09 AM in Default
Change the clustered index to have
It will have a negative effect on querying for a particular channel with no specified date range, however.
I would still give it a fill factor appropriate to how your data is populated (i.e. if late data can be inserted then a fill factor would alleviate page splits in that scenario).
answered Feb 02 '10 at 06:14 AM
Matt Whitfield ♦♦