as they reside physically on-disk.
SQL Server has limited control over this type of fragmentation
- much of it is controlled by the disk subsystem
and our decision on where to place files,
how many to have, our choice of storage, etc.
All SQL Server can do really is request space for the given data file(s)
when it is told to do so, or when it needs to -
these requests are then passed to the appropriate APIs, storage drivers, etc.
which handle the allocation of new space on-disk.
The best SQL can do is request this space be contiguous,
but it will only at best get a full contiguous block in the size requested,
and this assumes you are using a single spindle with a single file.
Once you start introducing things like RAID, SANs, etc.,
you are now treading into a territory
where it is nearly completely out of SQL Server's control.
RAID is all about spreading data across multiple spindles
for either performance improvements, data protection,
or a combination of both
- if data is being spread across multiple spindles,
then you are by nature not going to have contiguous data on a single spindle
- of course, the benefit here is
that multiple spindles can service more requests than a single spindle,
and you have data protection.
SANs are even more complex in that they implement RAID (in quite a few varieties)
and also provide tons of management capabilities, options, etc., etc.,
making it even more complex to understand
where exactly your data resides on-disk.
If you'd like a very simple example,
let's say you're running SQL on a laptop with a single LUN
that is supported by a single spindle
- in this scenario it is a bit easier to understand
- you simply have the SQL Server data file(s) stored in blocks on this disk,
and if you pre-allocate the appropriate space for the database supported by the file(s),
and the contiguous space on disk is available,
then you'll most likely end up with a very contiguous file on-disk.
If you took the disk and sliced it into blocks,
a single disk with file-level fragmentation for SQL Server data file might look something like this:
Source:
http://www.mssqltips.com/sqlservertip/2262/sql-server-fragmentation-what-it-is-what-types-there-are-
part-2-of-9/