Sql-server – Figuring out if SQL Server database needs to be defragmented

sql serversql server 2014

I am tasked with looking at if a database in SQL Server 2014 needs to be defragmented. I ran DBCC SHOWCONTIG but am having trouble interpreting the results.

use databasename;
GO  
DBCC SHOWCONTIG ('Table');  
GO

The results are:

DBCC SHOWCONTIG scanning 'Table' table...
Table: 'Table' (802206008); index ID: 1, database ID: 20
TABLE level scan performed.
- Pages Scanned................................: 2239
- Extents Scanned..............................: 280
- Extent Switches..............................: 279
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [280:280]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 31.79%
- Avg. Bytes Free per Page.....................: 366.8
- Avg. Page Density (full).....................: 95.47%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Questions:

  1. What do the percentages above mean? (like the difference between logical and extent scan fragmentation).
  2. What percentage do I need to check and what value indicates the disk needs to be defragmented?

Best Answer

Logical Scan Fragmentation Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

Extent Scan Fragmentation Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

Did you try this page: DBCC SHOWCONTIG?

Related Question