SQL Server Performance

Cluster Indexes

Discussion in 'SQL Server 2005 General DBA Questions' started by Luis Martin, Apr 21, 2005.

  1. Luis Martin Moderator

    Why Database Tuning Advisor don't recommend any cluster index, even when big table has nonone?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  2. FrankKalis Moderator

  3. satya Moderator

    From the initial outlook of DTA tool, it will:

    - Recommend the best mix of indexes for databases given a workload, by using the query optimiser to analyse the queries in the workload.
    - Recommend indexed views for databases referenced in a workload.
    - Analyse the effects of the proposed changes, including index usage, distribution of queries among tables, and performance of queries in the workload.
    - Provide reports summarizing the effects of implementing the recommendations for a given workload.

    Clarify whether the table has Primary key defined or not?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. Adriaan New Member

    And if you say "big table", is that going by the number of rows or by the number of columns?

    If it is one of those tables where the PK covers all relevant columns in the table, the table is its own covering index.
  5. Luis Martin Moderator

    Frank,

    I'm not B tester (Microsoft don't give that privilege), if that is what you mean.

    Satya,

    No PK.

    Adriaan,

    12000000 rows, 70 columns.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  6. mmarovic Active Member

    Maybe it is not usefull to have clustered index when there are too many columns? That's at least ORACLE recommendation for index organized tables - equivalent of table with clustered index on MSSQL Server.
  7. satya Moderator

    I agree with that in the terms of storage having clustered index on this table is a nightmare, as long as the query responses are good I don't worry much about type of index to opt.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. FrankKalis Moderator

  9. Luis Martin Moderator

    Thanks to all.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  10. satya Moderator

    Luis

    Let us know your assumptions about the indexes and performance without them from your testing.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. Luis Martin Moderator

    In all my test, DTA give better indexes than ITW even when ITW suggest clustered and DTA no.
    I sent to Brad an English article comparing ITW vs DTA with a real case (no test case).



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page