Skip to main content
added 147 characters in body
Source Link
Michael Green
  • 25.1k
  • 13
  • 53
  • 98

Fragmentation occurs when the storage engine can't physically place a row in its correct logical position. So you need a table with no internal space, then insert rows in the "middle".

The easiest way to fill pages is to ensure only one row will fit

create table T (
id int,                  -- primary key & clustered; fill factor 100%
c1 char (5000) not null  -- fixed length, over half the size of a page
)

Write as many rows as you need; a few hundred thousand should do it. Keep id sequential to avoid fragmentation a this stage. Leave gaps for new values, however.

Now INSERT rows to achieve the desired fragmentation. Randomly generate id values between the current min and max. This can be done iteratively, or set-wise by selecting the top n from the current rows and incrementing the id to fit in the gaps reserved.

A target row count can be achieved by starting with a smaller initial set or deleting rows randomly.

Fragmentation occurs when the storage engine can't physically place a row in its correct logical position. So you need a table with no internal space, then insert rows in the "middle".

The easiest way to fill pages is to ensure only one row will fit

create table T (
id int,                  -- primary key & clustered; fill factor 100%
c1 char (5000) not null  -- fixed length, over half the size of a page
)

Write as many rows as you need; a few hundred thousand should do it. Keep id sequential to avoid fragmentation a this stage. Leave gaps for new values, however.

Now INSERT rows to achieve the desired fragmentation. Randomly generate id values between the current min and max. A target row count can be achieved by starting with a smaller initial set or deleting rows randomly.

Fragmentation occurs when the storage engine can't physically place a row in its correct logical position. So you need a table with no internal space, then insert rows in the "middle".

The easiest way to fill pages is to ensure only one row will fit

create table T (
id int,                  -- primary key & clustered; fill factor 100%
c1 char (5000) not null  -- fixed length, over half the size of a page
)

Write as many rows as you need; a few hundred thousand should do it. Keep id sequential to avoid fragmentation a this stage. Leave gaps for new values, however.

Now INSERT rows to achieve the desired fragmentation. Randomly generate id values between the current min and max. This can be done iteratively, or set-wise by selecting the top n from the current rows and incrementing the id to fit in the gaps reserved.

A target row count can be achieved by starting with a smaller initial set or deleting rows randomly.

Source Link
Michael Green
  • 25.1k
  • 13
  • 53
  • 98

Fragmentation occurs when the storage engine can't physically place a row in its correct logical position. So you need a table with no internal space, then insert rows in the "middle".

The easiest way to fill pages is to ensure only one row will fit

create table T (
id int,                  -- primary key & clustered; fill factor 100%
c1 char (5000) not null  -- fixed length, over half the size of a page
)

Write as many rows as you need; a few hundred thousand should do it. Keep id sequential to avoid fragmentation a this stage. Leave gaps for new values, however.

Now INSERT rows to achieve the desired fragmentation. Randomly generate id values between the current min and max. A target row count can be achieved by starting with a smaller initial set or deleting rows randomly.