SQL Server Performance

What's the best way to create à clustered index?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Steph2004, Aug 20, 2008.

  1. Steph2004 New Member

    I keep wondering what's the best way to create a clustered index. I tried many ways. Some make the query fast even though I think it could be faster, others are pretty slow, even though it shows an "clustered index seek" in the performance plan.
    So here's the thing. I have a logs table for a custom web log analyzer. It goes like this:
    id bigint, logDate datetime, fk_websites_id int, logFields N...
    I have an id because two logs can have the same date. Rarely, but it happens. The fk_websites_id refers to a websites table because we monitor around 1000 websites. So far, we have logged around 100 000 000 rows with dates ranging from 3 years ago to present.
    Now, the queries are always something like
    select SomeFields from tbl_logs where fk_websites_id = 100000 and logDate between dateFrom and dateTo
    The where clause might be more complicated by adding others fields (field_1 = someValue and field_2 like 'blabla%'), but basically, the website_id and the logDate are always required.
    I tried several combinations of the clustered index:
    logDate, fk_websites_id, id (this is slow)
    fk_websites_id, logDate, id (this is the fastest)
    id, logDate (I also have a non-clustered index on fk_websites_id)
    logDate, id (I also have a non-clustered index on fk_websites_id)
    Along with theses combinations, I tried several combinations of the where clause putting the fk_websites_id before the logDate and so on.
    So I need some piece of advice here. What would give the best results for a clustered index? What's the golden rule of creating such clustered index for a log table?
    Thanks for any idea
  2. preethi Member

    Knowing the size of your table, I think you should think of partitioning the table (by logdate or websites_id)and create the clustered index where partitioning key will not be the first column.
    For example, if you choose, logdate as the aprtitioning key, create a clustered index on websites_id, logdate, id
    Does the edition you have support table partitioning?

Share This Page