Database Reference
In-Depth Information
Listing 8-7. Triggers and fragmentation: Table creation
create table dbo.Data
(
ID int not null identity(1,1),
Value int not null,
LobColumn varchar(max) null,
constraint PK_Data
primary key clustered(ID)
);
;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
,Numbers(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.Data(Value)
select Num
from Numbers;
Now let's delete every other row in the table, looking at the index's physical statistics before and after the deletion.
The code is found in Listing 8-8 and the results are shown in Figure 8-2 .
Listing 8-8. Triggers and fragmentation: Physical index stats before and after deletion
select
alloc_unit_type_desc as [AllocUnit],
index_level,
page_count,
avg_page_space_used_in_percent as [SpaceUsed],
avg_fragmentation_in_percent as [Frag %]
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'dbo.Data'),1,null,'DETAILED');
delete from dbo.Data
where ID % 2 = 0;
select
alloc_unit_type_desc as [AllocUnit],
index_level,
page_count,
avg_page_space_used_in_percent as [SpaceUsed],
avg_fragmentation_in_percent as [Frag %]
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'dbo.Data'),1,null,'DETAILED');
 
Search WWH ::




Custom Search