Database Reference
In-Depth Information
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 CROSS JOIN N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.UniqueidentifierCI(Val)
select ID from IDs;
select page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats
(db_id(),object_id(N'dbo.IdentityCI'),1,null,'DETAILED');
select page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats
(db_id(),object_id(N'dbo.UniqueidentifierCI'),1,null,'DETAILED')
Figure 6-4.
Fragmentation of the indexes
As you can see, the index on the uniqueidentifier column is heavily fragmented, and it uses about 40 percent
more data pages as compared to the index on the identity column.
A batch insert into the index on the uniqueidentifier column inserts data at different places in the data file, which
leads to heavy, random physical I/O in the case of the large tables. This can significantly decrease the performance of
the operation.
perSONaL eXperIeNCe
some time ago, i had been involved in the optimization of a system that had a large 250gB table with one clustered
and three nonclustered indexes. One of the nonclustered indexes was the index on the uniqueidentifier column. By
removing this index, we were able to speed up a batch insert of 50,000 rows, from 45 to down 7 seconds.
There are two common use-cases to create indexes on uniqueidentifier columns. The first one is for supporting
the uniqueness of values across multiple databases. Think about a distributed system where rows can be inserted into
every database. Developers often use uniqueidentifiers to make sure that every key value is unique system wide.
