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 [;)])
  3. Jeff Moden New Member

    Oh, be careful, now. What many people don’t understand is that Non-Clustered indexes are just a different type of table that has its own leaf level and that leaf level is actually a duplication of data not only of the columns defined in the Non-Clustered index but also the columns of the Clustered index. If you have a Clustered index of, say, 4 columns in width and 5 non-clustered indexes of, say, just one column in width, the data stored in the leaf level of those non-clustered indexes will actually be 5 columns wide (1 for the NCI definition and 4 for the CI definition, provided that none of the NCI columns are also CI columns… SQL Server won’t duplicate columns in an index). Of course, if the Clustered Index is not unique, then you also have to add in the ROWID column and the uniquifier columns which means that your single column indexes will actually be at least 5 and up to 7 columns wide.

    The best practice for Clustered Indexes, as cited by SQL heavy weights such as Kimberly Tripp and Paul Randall, is that they be narrow, unique, and ever increasing.
  4. Luis Martin Moderator

    Jeff, welcome to the forums!!.
    Check this thread. Is 5 years old.

Share This Page