Index Fragmentation - Pro SQL Server Internals - page 117

■ Be careful with SQL Server maintenance plans. they tend to perform index maintenance on all indexes, even
when it is not required.

Note

■ Ola Hallengren's free database maintenance script is a great solution that analyzes fragmentation level on a
per-index basis, and it performs index rebuild/reorganize only when needed. It is available for download at:
http://ola.hallengren.com/ .

Tip

Finally, do not worry about index fragmentation when a table or index is small, perhaps less than 1,000 data
pages. That number, however, could be greater based on the server and system load, available memory, and the
number of indexes to rebuild.
With all that being said, the best way to reduce fragmentation is to avoid creating patterns in the database design
and code that lead to such conditions.

Patterns that Increase Fragmentation
One of the most common cases that leads to fragmentation is indexing complete random values, such as
uniqueidentifiers generated with NEWID() or byte sequences generated with HASHBYTE() functions. Values generated
with those functions are randomly inserted into different part of the index, which cause excessive page splits and
fragmentation. You should avoid using such indexes if it is at all possible.

■ Note We will discuss the performance implications of indexes on random values in the next chapter.

Another common pattern contributing to index fragmentation is increasing the size of the row during an update,
for example when a system collects data and performs post-processing of some kind, populating additional attributes/
columns in a data row. This increases the size of the row, which triggers a page split if the page does not have enough
space to accommodate it.
As an example, let's think about a table that stores GPS location information, which includes both geographic
coordinates and the address of the location. Let's assume that the address is populated during post-processing, after
the location information has already been inserted into the system. Listing 5-3 shows the code that creates the table
and populates it with some data.

Listing 5-3. Patterns that lead to fragmentation: Table creation

create table dbo.Positions
(
DeviceId int not null,
ATime datetime2(0) not null,
Latitude decimal(9,6) not null,
Longitude decimal(9,6) not null,
Address nvarchar(200) null,
Placeholder char(100) null,
);