SQL Server Performance Forum – Threads Archive
Cluster Indexes
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.
Have you asked this in the 2005 newsgroup? —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
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.
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.
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.
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.
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.
Nope Luis, although I meant the beta newsgroup, I don’t think they are only for beta testers available:http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us I least haven’t I’ve seen such a limitation. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
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.
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.
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.
]]>