Cari Blog Ini

25 Juli 2012

What Causes in File-Level Fragmentation SQL Server ?

(1).
Other applications/services/etc. (or SQL Server itself)
writing data to the same spindles
that SQL Server data/log files reside on
- this can lead to fragmentation on the file system
because as the SQL Server files grow,
they end up being allocated space on the spindle(s)
that is not physically contiguous to prior allocations for the same file,

since data for these other applications/services/etc. have been allocated
that space for storing whatever data it may be
(could be a word document,
could be a backup file (sql server or otherwise),
could a another sql server data file,
could be a text document, etc., etc.).

Notice that it doesn't have to be a non-sql server file
that can cause file-level fragmentation

- if you have 2 data files (even for the same database)
that reside on the same spindle(s),
and those files have grown multiple times,
chances are quite good that they are interleaved on disk.


(2).
Auto-growth / growth of data/log files
- this doesn't just apply to auto-grow,
it applies to any growth of the file(s), manual or automatic.

Generally auto-grow is the larger contributor
in cases where this is a problem
because it's more feasible
that a file has auto-grown hundreds/thousands/millions of times
in small scattered chunks than manually so.



To display this is a simple diagram,
if a set of storage spindles could be described
as contiguous from left-right as implied in the diagram below,
and you assume each color-coded block is a section of data for each given file,
file-level fragmentation might look like this:


Source:
http://www.mssqltips.com/sqlservertip/2263/sql-server-fragmentation-what-causes-it-part-3-of-9/