Considerations for performance comparison with a high fragmented heap?

Question

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?

Answer 1

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.

A more realistic test would be to try recreate the fragmentation that would have resulted from the table being in each design from the start. This way you are comparing the result of the designs as they would look after real world use rather than after a fresh rebuild.

If your application keeps a full audit trail for that data then you could perhaps rebuild each copy by replying that audit. Otherwise you might need to make up some heuristic (for instance if the data includes a creation or last modified date inserting the rows into each copy in that order).

One thing to note when doing this is to do each insert individually rather than a block copy - when you insert or update many rows at once it is bright enough to bunch the index updates to reduce page splits which it can't do with the individual inserts that would result from real application access. The following will illustrate this (using the amount of space allocated as an indication of free page space fragmentation due to page splits caused by the randomness of UUID ordering):

SET NOCOUNT ON
CREATE DATABASE TestFragUUID
GO
USE TestFragUUID
CREATE TABLE IndividualInserts (ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, AnotherID UNIQUEIDENTIFIER UNIQUE)
CREATE TABLE SingleLargeInsert (ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, AnotherID UNIQUEIDENTIFIER UNIQUE)
GO
INSERT IndividualInserts SELECT NEWID(), NEWID()
GO 100000
WITH CTen AS (SELECT TOP 10 name FROM sys.objects)
INSERT SingleLargeInsert 
SELECT NEWID(), NEWID()
FROM   CTen unit
CROSS JOIN cTen ten
CROSS JOIN cTen hun
CROSS JOIN cTen tho
CROSS JOIN cTen tth
GO
EXEC sp_spaceUsed 'IndividualInserts'
EXEC sp_spaceUsed 'SingleLargeInsert'
GO
USE master
DROP DATABASE TestFragUUID