I analysed a huge query in DTA. The database is kind of badly designed and does not have primary keys on tables, also they dont have clustered indexes on them. I found around 80 tables as such. Now, without indexes the queries are obviously going to run slow but when I put them through the DTA, it always asked me create a non clustered index (even on heaps). Something like
CREATE NONCLUSTERED INDEX [_dta_index_IO_Status_MAster_10_1899153811__K2_4_5_1912]
ON [Scorecard].[IO_Status_MAster]
(
[WBS] ASC
)
INCLUDE ([OrderNumber], [IO_STATUS])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
My question, does DTA works on the premise that table has a clustered index on it? Why does it not advise to create a Clustered Index?
Best Answer
By default, the DTA doesn't recommend clustered indexes. In addition, check out the documentation for even more things it doesn't do.
If you do want recommendations for clustered indexes, click the radio button for "Do not keep any existing PDS" as shown in the below screenshot:
But be aware that it can produce some stunningly bad clustered index recommendations. The DTA isn't a replacement for basic data modeling for how to store your data.
Generally, your clustered indexes should follow the SUN-E method: