0

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?

3

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:

Heaps of trouble

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:

  • Static - fields that don't change, so you don't have to update your nonclustered indexes
  • Unique - so SQL Server doesn't have to add a uniqueifier behind the scenes
  • Narrow - so lots of data isn't copied into your nonclustered indexes
  • Ever-increasing - creating a hotspot at the end, guaranteeing that where you're inserting will usually be in memory (although this is one rule that folks violate on purpose during extremely heavy concurrency, like tens of thousands of inserts per second)
  • 1
    Brent -- Thank you for your excellent explanation. – rohit Sep 15 '17 at 15:11
  • You're welcome, glad I could help. – Brent Ozar Sep 15 '17 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.