defrag_status

Returns metrics of any defragmentation operation that is started or ongoing on the named object or partition.

Syntax

defrag_status( dbid, objid [ , ptnid | -1 [, "tag" ] ]

Parameters

dbid – is the ID of the target database.
objid – is the ID of the target object.

ptnid – is the ID of the partition or enter -1.
-1 refers to all the partitions in the table. If ptnid is unspecified, -1 is the default value.

In case of invoking the built-in with four parameters, the third parameter

'ptnid' cannot be skipped. So, it has to be specified accordingly.

tag – is one of:
- frag index or fragmentation index– the fragmentation index is the number of times the size of the object is larger compared to the size of the same if it was completely defragmented.
  
  This index can be any number greater than or equal to zero. The lower the index, the less fragmented the table or partition is. The higher the index, the more fragmented the object is and is more likely to free up space with defragmentation.
  
  For example, a value of 0.2 , means the table occupies 20% more space than what it would be if the data were fully defragmented. This index can be any number > 0. For example, 1 means the table is occupying 100% more space than what a fully defragmented version of the data would occupy.
- pct defrag or pct defragmented – is the percentage of pages defragmented.
- pages defrag or pages defragmented – the number of pages defragmented.
- pages gen or pages generated– the number of new pages generated.
- pages tbd or pages to be defragmented– the number of pages still left to be processed and defragmented.
- last run – the start time of the most recent invocation of this command.
- executing – boolean, whether the command is executing currently.
- elapsed mins – the number of minutes elapsed since the start of the most recent invocation of this command. This value is non-zero when executing is 1, and is zero otherwise.

Examples

Example 1 – executes defrag_status on the table mymsgs:

select defrag_status(db_id(), object_id('mymsgs'))

If defragmentation has not yet been performed, the output is:

------------------------------------------------------------------
frag index=0.20, pct defrag=0, pages defrag=0, pages gen=0, 
pages tbd=1174, last run=, executing=0, elapsed mins=0

If defragmentation has been performed, the output is:

-----------------------------------------------------------------------
frag index=0.07, pct defrag=100, pages defrag=1167, pages gen=1072, 
pages tbd=0, last run=Oct  9 2012  2:27:11:446PM, executing=0, 
elapsed mins=0

Example 2 – executes defrag_status on the data partition p1:

select defrag_status(db_id(), object_id('t1'), partition_id('t1', 'p1'))

If defragmentation has not yet been performed, the output is:

-----------------------------------------------------------------------
frag index=0.75, pct defrag=0, pages defrag=0, pages gen=0, pages tbd=67,
last run=, executing=0, elapsed mins=0

If defragmentation is executed, the output is:

------------------------------------------------------------------------
frag index=0.00, pct defrag=100, pages defrag=61, pages gen=32, 
pages tbd=0, last run=Oct  9 2012  2:44:53:830PM, executing=0, 
elapsed mins=0

If partial defragmentation is executed, the output is:

-----------------------------------------------------------------------
frag index=0.02, pct defrag=41, pages defrag=135, pages gen=144, 
pages tbd=190, last run=Oct 9 2012  3:17:56:070PM, executing=0, 
elapsed mins=0

While defragmentation is in progress, the output is:

---------------------------------------------------------------------
frag index=0.90, pct defrag=10, pages defrag=40, pages gen=24, 
pages tbd=360, last run=Oct 9 2012  3:01:01:233PM, executing=1, 
elapsed mins=1

Example 3 – executes the pct defrag parameter:

select defrag_status(db_id(), object_id('t1'), -1, 'pct defrag')

The output displays the percentage of the pages that have been defragmented.

--------------------------------------------------------------------
8

When 1 row is affected:

select defrag_status(db_id(), object_id('t1'), partition_id('t1', 'p1'),
   'pct defrag')

The output is:

---------------------------------------------------------------------
41