SQL Server Performance

What type of index?

Discussion in 'SQL Server 2005 General DBA Questions' started by brimba, Aug 16, 2007.

  1. brimba New Member

    Hello!
    I have a table (the table is simplified to show the specific issue)

    CREATE TABLE Messages(
    FromUserId INT,
    ToUserId INT
    Topic VARCHAR(100)
    )
    Now I would like to discuss the best index in our situation. The table will contain about 2-3 million rows.

    If I create a clustered index on (FromUserId, ToUserId), then when we make a new insert we will force the table to be resorted since we cannot know the order in which FromUserId that is sending a message, and most likely it will not be in order. Therefore since the clustered index needs to be sorted it will probably be alot of sorting and page splits and that sort of things.
    But if I add a "dummy" identity-column
    CREATE TABLE Messages(
    MessageId INT IDENTITY(1,1),
    FromUserId INT,
    ToUserId INT
    Topic VARCHAR(100)
    )
    And create one clustered index over (MessageId) and then one non-clustered index on (FromUserId, ToUserId).
    Then we dont need to have page splits and resorts on the actual data, but instead there will be some movement on the index. Which will probably save us
    some on the performance. But on the other hand we will have two indexes that needs to be updated.
    The table has alot of activity, booth reads and writes. (selects (90%), inserts (10%))
    Maybe the best thing would be to change the fillfactor?
    What are your thoughts about this?
  2. Luis Martin Moderator

    I'll go with non cluster on FromUserId, TopUserId. And run Profiler to see what is going on.
  3. FrankKalis Moderator

    Given that SELECT:INSERT ratio, I would go for two separate indices. What about UPDATEs? Can they happen?
  4. brimba New Member

    no, there are no updates.

Share This Page