SQL Server Performance

Performance tip 'Optimizing SQL Server Clustered Indexes' true?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Expansion, Jul 15, 2008.

  1. Expansion New Member

    Is the following performance tip in the Newsletter from 14-07-2008 true? I don't think so.
    Optimizing SQL Server Clustered Indexes
    If you run into a circumstance where you need to have a single wide index (a composite index of three or more columns) in a table, and the rest of the indexes in this table (assuming there are two or more) will only be one column wide, then consider making the wide index a clustered index and the other indexes non-clustered indexes.
    Why? If the wide index is a clustered index, this means that the entire table is the index itself, and a large amount of additional disk space is not required to create the index. But if the wide index is a non-clustered index, this means SQL Server will have to create a "relatively large" index, which will indeed consume a large amount of additional disk space. Whenever the index needs to be used by the query optimizer, it will be more efficient to access the clustered index than the non-clustered index, and performance will be better. [6.5, 7.0, 2000, 2005]
    Books On line SqlServer 2005 says:
    If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 2005 makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.
    So it means the Clustered key is in the leaf row of every Nonclustered index and you use space for the Clustered key in every index.
    I think the tip was good for Sql Server 6.5 (as the index structure was different), but it must be the opposite for Sql Server 7.0, 2000 en 2005.
    I think normally for retrieving rows you should make your Clustered index on a simple primary key.
    You could consider a wide cluster key if you have a lot of searching on a range or if you want a big resultset in the order of a wide key.
    Cor Westra, Expansion
  2. moh_hassan20 New Member

    [quote user="Expansion"]
    Optimizing SQL Server Clustered Indexes
    If you run into a circumstance where you need to have a single wide index (a composite index of three or more columns) in a table, and the rest of the indexes in this table (assuming there are two or more) will only be one column wide, then consider making the wide index a clustered index and the other indexes non-clustered indexes.
    [/quote]
    Non clustered index reference RID (physical record ID ) of the clustered index , and use it as a lookup.
    So with change the value of any field of the composite key of the clustered index , it lead to rebuilding of non clustered index, which may lead IO overhead
    I think :
    • If table have no Clustered index , it must have one
    • let the clustered index as PK of the table to benefit FK of child tables.
    • any change in clustered index , lead to rebuild of non clustered index , so
    All the time , when you rebuild index , start with Clustered index first , then non clustered index (so review your index maintainance plan and reorder execution [;)])

Share This Page