I am planning to perform some performance comparisons for different structures of one and the same table. The table I want to investigate on is a heap having ~ 1Mio rows and 123 columns.
Setup: I have copied the table from production to a test system. It is a heap table and I want to check options to improve it. Then I created 3 fresh copies of this table's data.
Copy1:
- Clustered index on Primary Key (uniqueidentifier)
- Same Nonclustered indexes & foreign keys
Copy2:
- Clustered index on an existing IDENTITY Column
- Same Nonclustered indexes & foreign keys
Copy3:
- Clustered index on another uniqueidentifier and int column that is frequently used to filter a list of the rows
- Same Nonclustered indexes & foreign keys
I have collected several typical and frequently used sample INSERT, UPDATE and SELECT queries from production environment. My plan is to run those multiple times against all those four tables and compare the total IO Statistics and total cpu time statistics as well as the Execution plan details to hopefully come to a valuable result telling me which table performs best with the regular queries of the application.
Since the three table copies are brandnew, so there is no fragmentation in place. For a fair comparison I also re-builded all indexes of the original table.
I am not sure about the fragmentation of the "heap-index" of this table, it is ~60%. I wonder if I have to take care of this first or just leave it as it is since it will be in place in production as well and even if I fixed it, it will however come back and maybe influence query performance anyway?
I would beside an answer to the question above also appreciate any helpful comments what to consider as well to have the best possible result of this work. Especially regarding the data cache. Not sure if I should always clear it in advance of the queries or better run the queries once to FILL it before taking the samples?