Can GUID cluster keys cause non-clustered index fragmentation?

A GUID key causes fragmentation because of its randomness. The insertion point of a new record in an index is dictated by the value of the index key, so if the key value is random, so is the insertion point. This means that if an index page is full, a random insert that happens to have to go onto that page will cause a page split to make room for the new record. A page-split is where a new page is allocated and (as near as possible to) half the rows from the splitting page are moved to the new page. The new row is then inserted into one of the two pages, determined by the key value. Usually the newly allocated page is not physically contiguous to the splitting page, and so fragmentation has been caused. In this case *two* kinds of fragmentation have been caused – logical fragmentation (where the next logical page as determined by the index order is not the next physical page in the data file) and physical (or internal) fragmentation (where space is being wasted on index pages). These can both affect query performance (topic for a later post), as well as the expense of having to do the page split in the first place.

It’s fairly well known that GUIDs can cause fragmentation in the index where the GUID is the key (e.g. a clustered index), but not about the knock-on effects in non-clustered indexes. Here’s an example – I’ll create two clustered indexes with GUID keys (one generated from NEWID and one from NEWSEQUENTIALID), plus a non-clustered index on each. Let’s see what happens when we insert 100000 rows:

— Create a table with a GUID key
CREATE TABLE BadKeyTable (
   c1 UNIQUEIDENTIFIER DEFAULT NEWID () ROWGUIDCOL,
   c2 DATETIME DEFAULT GETDATE (),
   c3 CHAR (400) DEFAULT ‘a’);
CREATE CLUSTERED INDEX BadKeyTable_CL ON BadKeyTable (c1);
CREATE NONCLUSTERED INDEX BadKeyTable_NCL ON BadKeyTable (c2);
GO

— Create another one, but using NEWSEQUENTIALID instead
CREATE TABLE BadKeyTable2 (
   c1 UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID () ROWGUIDCOL,
   c2 DATETIME DEFAULT GETDATE (),
   c3 CHAR (400) DEFAULT ‘a’);
CREATE CLUSTERED INDEX BadKeyTable2_CL ON BadKeyTable2 (c1);
CREATE NONCLUSTERED INDEX BadKeyTable2_NCL ON BadKeyTable2 (c2);
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 10000)
BEGIN
   INSERT INTO BadKeyTable DEFAULT VALUES;
   INSERT INTO BadKeyTable2 DEFAULT VALUES;
   SELECT @a = @a + 1;
END;
GO

— And now check for fragmentation
SELECT
   OBJECT_NAME (ips.[object_id]) AS ‘Object Name’,
   si.name AS ‘Index Name’,
   ROUND (ips.avg_fragmentation_in_percent, 2) AS ‘Fragmentation’,
   ips.page_count AS ‘Pages’,
   ROUND (ips.avg_page_space_used_in_percent, 2) AS ‘Page Density’
FROM sys.dm_db_index_physical_stats (DB_ID (‘DBMaint2008’), NULL, NULL, NULL, ‘DETAILED’) ips
CROSS APPLY sys.indexes si
WHERE
   si.object_id = ips.object_id
   AND si.index_id = ips.index_id
   AND ips.index_level = 0;
GO

Object Name    Index Name        Fragmentation  Pages  Page Density
————-  —————-  ————-  —–  ————
BadKeyTable    BadKeyTable_CL    99.13          8092   66.08
BadKeyTable    BadKeyTable_NCL   30.97          78     64.1
BadKeyTable2   BadKeyTable2_CL   0.83           5556   96.26
BadKeyTable2   BadKeyTable2_NCL  1.88           372    99.61