Fragmentation

1
Fragmentation

• Tony Rogerson SQL Server MVP
• Torver Computer Consultants
• www.sqlserverfaq.com
• tonyrogerson_at_sqlserver.eu.com

2
Agenda

• Logical Disk Fragmentation
• NTFS Backgrounder
• Fragmentation
• Best practices
• SQL Server Fragmentation
• Storage internals (Files, Extents and Pages)
• Extent and Page fragmentation
• Shrinking the database
• Scripts to deal with SQL Server fragmentation
• Best practices

3
NTFS Backgrounder

• Do not use Compression with SQL Server (not
  supported)!
• Writes/Reads using Clusters, default cluster size
  is variant on size of disk (see
  http//support.microsoft.com/?kbid140365) can be
  up to 4K, specify values up to 64K (NT defrag
  only works up to 4K)
• Recommend higher cluster sizes as SQL Server
  deals with 8K pages and 64K extents use a
  cluster size of 4K so you can defrag (/A option
  of FORMAT)
• On fresh NTFS volume, Master File Table and a
  number of system logs are held near middle of the
  new volume (prevents starvation).

4
Logical Disk Fragmentation

• Our problem we have fewer files but big ones.
• If you request 50MB, NT looks for free contiguous
  clusters to satisfy that request, that could be
  anywhere on the disk (fills bigger gaps first).
• Causes increased disk head movement
• More fragmentation More head movement Slower
  disk read/write.
• Increased wear on disk reduces life expectancy

5
Fragmentation Example
Based on a 1GB logical volume
BEFORE create database (SQL programs after a
defrag)
AFTER create database (250MB MDF)
AFTER create database (550MB MDF)
Fragment 1 (start of database)
Fragment 2 (end of database)

• Cannot defrag 550MB because of NTFS system files
  in middle of volume

6
Extending the Database

• If you request 50MB, NT looks for free contiguous
  clusters to satisfy that request, that could be
  anywhere on the disk (fills bigger gaps first).
• If lots of other files on the disk then chances
  of free contiguous clusters diminish
  fragmentation occurs.

7
Best Practices

• Pre allocate your databases (do not let them
  auto-grow) be in control!
• Keep backups on another volume (especially
  transaction logs as there are lots of them and
  vary in size)
• Check fragmentation once a week/month (depends on
  your system)
• Use allocation size of 4K
• To defrag you need to stop SQL Server

8
SQL Server Fragmentation
9
Storage Internals

• Refer to Inside SQL Server 2000 pages 192-196
• 8 Pages in an extent 1 page 8K 1 extent 64K

10
Index Allocation Map (IAM)

• Holds details of the extents and pages that
  belong to the table or index.
• Entries are held in the order they appear on the
  disk thereby aiding scanning performance (less
  disk head movement).

11
Fragmentation

• Fragmentation occurs at the extent and page
  level, for indexes it can also occur within
  contiguous pages.
• SQL Server doesnt pre-allocate extents.
• Causes of Fragmentation
• Inserting and Updating data (page splits for
  existing data and acquiring free extents).
• Shrinking databases (talked about later).

12
Table Fragmentation Example
Extent
Page 0 FULL A
Page 1 FULL B
INSERT VALUE E
Page 2 - FREE
Page 3 - FREE
Page 4 FULL C
Page 5 FULL D
Page 6 FULL F
Page 7 FULL G

• Clustered index on our Letter column.
• On Index Scan (ordered) reads 0, 1, 4, 5, 2, 6, 7
• For un-ordered access doesnt matter as the IAM
  (Index Allocation Map) holds pages in the order
  they are held on disk.
• For index seek, doesnt matter either - getting
  back a single value so very little pages need to
  be read.

13
Table De-fragment

• Free pages are bubbled to the end of the table
  any extents becoming free are released to the
  pool.
• Methods of defrag include
• DBCC INDEXDEFRAG
• DBCC DBREINDEX
• BCP/BULK INSERT
• DROP/CREATE INDEX (DROP_EXISTING)

Extent
Page 0 FULL A
Page 1 FULL B
Page 2 FULL C
Page 3 FULL D
Page 4 FULL E
Page 5 FULL F
Page 6 FULL G
Page 7 FREE
14
Combating Database Fragmentation

• Tables grow and use up free extents
• Extents free up when data is deleted
• Enter FILLFACTOR to give more free space for new
  data/updates that extend the row size.
• Page numbers are unique to a file (start at 0).
• Consider placing volatile tables (work tables,
  staging tables) on their own File Group.
• Consider one table per file group (very radical!)

15
DBCC EXTENTINFO
page_id pg_alloc ext_size
TEST1 75 1 1
TEST1 77 1 1
TEST1 78 1 1
TEST1 79 1 1
TEST1 80 1 1
TEST1 81 1 1
TEST1 82 1 1
TEST1 83 1 1
TEST2 84 1 1
TEST2 86 1 1
TEST2 87 1 1
TEST1 88 8 8
TEST2 96 1 1
TEST2 97 1 1
TEST2 98 1 1
TEST2 99 1 1
TEST2 100 1 1
TEST2 104 8 8
TEST2 112 8 8
TEST1 120 8 8
TEST1 128 7 8
TEST2 136 8 8
TEST2 144 8 8
TEST2 152 1 8

• TEST2 is mixed in with TEST1 (fragmentation)
• ext_size of 1 are pages on mixed extents
• Missing page number is the IAM (Index Allocation
  Map) which isnt reported by this DBCC command.

Example \fragmentation - dbcc extentinfo.sql
16
DBCC SHOWCONTIG
- Pages Scanned................................
40 - Extents Scanned..............................
7 - Extent Switches.............................
. 6 - Avg. Pages per Extent......................
.. 5.7 - Scan Density Best CountActual
Count....... 71.43 57 - Logical Scan
Fragmentation .................. 0.00 - Extent
Scan Fragmentation ................... 28.57 -
Avg. Bytes Free per Page.....................
1018.2 - Avg. Page Density (full).................
.... 87.42

• Shows degree of fragmentation for an index /
  heap.
• DBCC SHOWCONTIG( test2, 1 )
• Extent Scan Fragmentation is the most important
  one to watch.
• Scan Density doesnt work when the table is on
  multiple files.

17
DBCC DBREINDEX

• Locks table and rebuild the index (moves the data
  as well proper defrag!)
• DBCC INDEXDEFRAG doesnt lock everything but only
  the pages its working on can be stopped as well
  and work done is kept.
• DBCC INDEXDEFRAG only defrags existing leaf pages
  of the index, causes no new extent allocations.
• DBREINDEX does more than INDEXDEFRAG

- Pages Scanned................................
40 - Extents Scanned..............................
6 (was 7) - Extent Switches.....................
......... 5 - Avg. Pages per Extent..............
.......... 6.7 - Scan Density Best CountActual
Count....... 83.33 56 - Logical Scan
Fragmentation .................. 0.00 - Extent
Scan Fragmentation ................... 0.00
(was 28.57) - Avg. Bytes Free per
Page..................... 1018.2 - Avg. Page
Density (full)..................... 87.42
18
What happened?

• Because database is full (no free extents),
  database is extended and data is put there, which
  is good extents are contiguous.
• Object moved off the mixed extent.
• Free extents left within the database.

19
DBCC DBREINDEX
MDF File
Extent FREE
Extent FREE
Extent Table 2
Extent FREE
Extent FREE
Extent Table 2
Extent Table 2
Extent Table 2
Extent FREE
Extent FREE
Extent Table 1
Extent Table 1
Extent Table 1
Extent Table 1
20
Example

• DBREINDEX Data Movement

Example \fragmentation - dbcc extentinfo.sql
21
Shrinking the Database

• DBCC SHRINKDATABASE
• Data is moved into free extents at the start of
  the MDF.
• Everything is probably fragmented again.
• Run DBREINDEX on important tables after
  SHRINKDATABASE or dont SHRINKDATABASE!
• Good way of getting a reasonably contiguous
  database without BCPing everything out and back
  in again.
• Make sure you have plenty of free extents at the
  end of your database!

22
Example

• SHRINKDATABASE Data Movement

Example \fragmentation - dbcc extentinfo.sql
23
Script

• DBREINDEX fragmented tables

Example \fragmentation - dbcc dbreindex.sql
24
Summary (Best Practices)

• Use File Groups for volatile tables
• DBREINDEX
• Requires free extents
• Moves data
• INDEXDEFRAG defrags index structure (leaves
  extents where they are)
• Careful if ever using SHRINKDATABASE data is
  moved down file into free extents giving rise to
  possible fragmentation.

25
Further Reading / Q A

• NTFS
• http//www.digit-life.com/articles/ntfs/
• http//people.msoe.edu/taylor/cs384/sitania.pdf
• Inside SQL Server 2000 Kalen Delaney

Tony Rogerson SQL Server MVP Torver Computer
Consultants www.sqlserverfaq.com tonyrogerson_at_sqls
erver.eu.com