Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
niaher
Starting Member
1 Post |
 Posted - 2009-11-22 : 02:25:04
|
I have the following table CREATE TABLE DiaryEntries ( [userId] [uniqueidentifier] NOT NULL, [setOn] [datetime] NOT NULL, -- always set to GETDATE(). [entry] [nvarchar](255) NULL )Each user will insert around 3 entries per day. There will be around 1'000'000 users. This means 3'000'000 new records in this table every single day. Once a record is older than 1 month, we delete it.Most of the queries have the following WHERE clause: WHERE userId = @userId AND setOn > @setOnMost queries return no more than 3 rows, except for one which returns all rows inserted within this month (which is at most 90 rows).The date and userId cannot be changed, once the record is inserted.Now my question is - how to best arrange the index for this table? I am stuck with the two alternatives:- 1. Clustered index on (userId, setOn) - this will give me fast searches, but I am worried about excessive page splits, because we will insert a lot of intermediary values (same userId but different date).
- 2. Non-Clustered indexes on (userId) and on (setOn) - this will also cause page splits on (userId) index (but is it as expensive as in the first option?).
- 3. Clustered index on an additional column (id) and Non-clustered index on (userId, setOn) - this will eliminate page splits of the data table, but will still cause some on the NC index. This option is also not optimal for search, since we search using NC index.
What are your suggestions?PS - Thanks for your time. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-22 : 11:11:19
|
| use a generous fill factor to mitigate the impact of page splits. i'd probably cluster on date to help with the deletes (and splits) but it might still scan to perform them. then nonclustered on userid...but...the way to get the best answer is to populate your table with test data and try it out |
 |
|
|
|
|
|
Page split vs. Slow search