SQL Server Performance

What are the reasons to make an index cluster?

Discussion in 'T-SQL Performance Tuning for Developers' started by pcsql, May 12, 2004.

  1. pcsql New Member

    I hope something can give a general guideline in creating a clustered index. As of now, I can only think of one:

    1. To optimize queries which will perform on many rows using the columns in the clustered index.



    Thank you for any input,

    Peter
  2. gaurav_bindlish New Member

    Except for the obvious like helping sorting, searching and grouping performance, Help organize data so that the rows are not thrown all over the place and hence the Read ahead can be more efficient.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. FrankKalis Moderator

    One important thing about clustered indexes is that the leaf level of such an index contains actually the data pages. Which means they are part of the index and do not only contain a pointer to the data like non clustered indexes do. So often you will read that clustered indexes are extremely useful for range queries. As every table can only have one clustered index (and almost every table should have one!), deciding on how to design this index has a serious impact on performance. I don't think there are general guidelines for this. You have to analyse your table, the data and the queries that run against this table and then decide on which to choose. Reading carefully through BOL and MSDN and/or technet might also help. Here are some links that might get you started (although the two I was searching for, somehow got lost here. I'll post them when I find them again)

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/itwforsql.asp
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209

    HTH


    --Frank
    http://www.insidesql.de
  4. satya Moderator

Share This Page