Figuring out if SQL Server database needs to be defragmented

0 votes

1 answer

222 views

sql-server sql-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?

                        

Asked by cdub (103 rep)

Jun 26, 2017, 09:08 PM
Last activity: Jun 11, 2025, 02:04 PM

Figuring out if SQL Server database needs to be defragmented

Related Questions