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.Positions(DeviceId, ATime, Latitude, Longitude)
select
ID % 100 /*DeviceId*/
,dateadd(minute, -(ID % 657), getutcdate()) /*ATime*/
,0 /*Latitude - just dummy value*/
,0 /*Longitude - just dummy value*/
from IDs;
create unique clustered index IDX_Postitions_DeviceId_ATime
on dbo.Positions(DeviceId, ATime);
select index_level, 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.Positions'),1,null,'DETAILED')
At this point, the table has 65,536 rows. A clustered index is created as the last stage during execution. As a result,
there is no fragmentation on the index. Figure
5-3
illustrates this point.
Figure 5-3.
Fragmentation after table creation
Let's run the code that populates the address information. This code, shown in Listing 5-4, emulates post-processing.
Listing 5-4.
Patterns that lead to fragmentation: Post-processing
update dbo.Positions set Address = N'Position address';
select index_level, 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.Positions'),1,null,'DETAILED')
Figure
5-4
shows the index fragmentation. Post-processing doubled the number of leaf-level pages of the index,
making it heavily fragmented both internally and externally.
Figure 5-4.
Fragmentation after post-processing
As you may guess, you can avoid this situation by populating the address information during the insert stage. This
option, however, is not always available.
