1

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?

1 Answer 1

2

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
2
  • TY for this helpful answer. I see your point. Different table design leads to diff. fragmentation leads to different performance. However it took a while to create the test data I have tight now and my time to do this operation is very limited. Your suggestion is to delete it and recreate it entirely to re-produce the fragmentation in the copy tables. You say I need to re-insert the rows ordered with SINGLE statements - but how? I can't Use GO 1M to copy the rows sorted. However I really wonder if the fragmentation will really play such a significant role for the results that it's is worth it?
    – Magier
    Aug 3, 2016 at 13:05
  • 1
    Where GO <many> isn't useful could just WHILE loop around individual statements or use a cursor. If it still seems to be bulking things instead of keeping them as completely separate operations use explicit transactions around the inner loop. Remember that part used pages live in memory as well as disk so the fragmentation means less efficient use of RAM by the buffer pool so potentially more IO (and more CPU use too though the IO is likely more significant). Running the code above on SQL2012 the fragmented version takes 44% more space than the solid one. Aug 3, 2016 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.