sp_helpdefrag

sp_helpdefrag uses the built-in defrag_status() on each of the required tables or on each of the required data partitions to get the information about defragmentation.

If table_name is not specified, defragmentation information for all eligible tables for reorg defrag (that is, user tables with datarows or datapages locking scheme) is reported. Rows for tables on which reorg defrag is currently executing precede those for tables where reorg defrag is not currently executing. Among these two sets, rows are in ascending order of the pct_defrag.
If table_name is specified, and if the table is eligible for reorg defrag, defragmentation information of the table as well as that of each data partition is reported. Rows are in the ascending order of percentage defragmented portion. Row for the table comes first and has NULL in partition column.
If partition_name is specified, only that particular data partition's information is reported.

Syntax

The syntax is:

sp_helpdefrag [table_name][,partition_name]

Parameters

table_name – is the name of the table.

partition_name – is the name of the partition.

Examples

No parameters and before defragmentation – If sp_helpdefrag is executed without parameters on database testdb with user data-only locking tables before defragmentation:

sp_helpdefrag

The output is:

table             frag_index      pct_defrag     executing      last_run  
---------------- ------------- ------------- -------------- ----------
t1_forw                  0.01           0               0          NULL  
mymsgs                   0.39           0               0          NULL  
mymsgs_clone             0.57           0               0          NULL  
t1                       0.66           0               0          NULL  
myprocs                  0.86           0               0          NULL  
mymsgs_ptnd              1.07           0               0          NULL  
t1_clone                 1.98           0               0          NULL  
myprocs_clone            2.16           0               0          NULL  
t1_ptnd                  2.99           0               0          NULL  
myprocs_ptnd             3.03           0               0          NULL  

(1 row affected) 
(return status = 0)

If you execute sp_helpdefrag after defragmentation, the output is:

table          frag_index    pct_defrag    executing          last_run
------------- ------------- ------------- ------------- --------------
t1_forw            0.01          100            0          Oct 10 2012  4:15PM
mymsgs             0.05          100            0          Oct 10 2012  4:15PM
mymsgs_clone      0.06          100             0          Oct 10 2012  4:15PM
t1                 0.08          100            0          Oct 10 2012  4:15PM
myprocs            0.09          100            0          Oct 10 2012  4:15PM
mymsgs_ptnd       0.09          100             0          Oct 10 2012  4:15PM
t1_clone           0.10          100            0          Oct 10 2012  4:15PM
myprocs_clone     0.11          100             0          Oct 10 2012  4:15PM
t1_ptnd            0.12          100            0          Oct 10 2012  4:15PM
myprocs_ptnd       0.14          100            0          Oct 10 2012  4:15PM

(1 row affected) 
(return status = 0)

On a specified table – If sp_helpdefrag is executed on table t1 in database testdb:

sp_helpdefrag t1

The output is:

table   partition  frag_index   pct_defrag  executing          last_run
------- ---------- ----------- ------------- ---------- -----------------
t1       NULL        0.35           35           0       Oct 10 2012  4:33PM
t1       p2          0.50           0           0                      NULL
t1       p1          0.42           20           0       Oct 10 2012  4:33PM
t1       p3          0.42           20           0       Oct 10 2012  4:33PM
t1       p4          0.05          100           0       Oct 10 2012  4:33PM

(1 row affected)
(return status = 0)

If reorg defrag is currently processing, the output is:

table   partition  frag_index   pct_defrag  executing          last_run
------- ---------- ----------- ------------- ---------- -----------------
t1       NULL        0.48           13           1       Oct 10 2012  4:33PM
t1       p2         0.50            0          1                      NULL
t1       p4          0.60            0           1       Oct 10 2012  4:33PM
t1       p1          0.42           20           1       Oct 10 2012  4:33PM
t1       p3          0.42           20           1       Oct 10 2012  4:33PM

(1 row affected)
(return status = 0)

On a specified partition – If sp_helpdefrag is executed on partition p1 in table t1:

sp_helpdefrag t1, p1

The output is:

table   partition  frag_index   pct_defrag  executing       last_run
------- ---------- ----------- ------------- ---------- -----------------
t1        p1         0.42           20           0       Oct 10 2012  4:33PM

(1 row affected)
(return status = 0)