sp_iqrowdensity Procedure

Reports information about the internal row fragmentation for a table at the FP index level.

Syntax

dbo.sp_iqrowdensity (‘target ‘)

target:(table table-name | (column column-name (...))

Parameter

table-name – reports on all columns in the named table.
column-name – reports on the named column in the target table. You may specify multiple target columns, but must repeat the keyword each time.

You must specify the keywords table and column. These keywords are not case-sensitive.

Privileges

You must have EXECUTE privilege on the system procedure. You must also have one of the following system privileges:

MONITOR
MANAGE ANY DBSPACE
CREATE ANY INDEX
ALTER ANY INDEX
CREATE ANY OBJECT
ALTER ANY OBJECT
You own the table

Remarks

sp_iqrowdensity measures row fragmentation at the default index level. Density is the ratio of the minimum number of pages required by an index for existing table rows to the number of pages actually used by the index. This procedure returns density as a number such that 0 < density < 1. For example, if an index that requires 8 pages minimum storage occupies 10 pages, its density is .8.

The density reported does not indicate the number of disk pages that may be reclaimed by re-creating or reorganizing the default index.

This procedure displays information about the row density of a column, but does not recommend further action. You must determine whether or not to re-create, reorganize, or rebuild an index.

The sp_iqrowdensity IndexType column always returns the maximum number of bits required to encode the column.

Unlike the FP(1), FP(2), FP(3) dictionary compression in previous releases, which uses the same number of bits for each page, NBit encodes each page dynamically. sp_iqrowdensity always returns the largest number of bits used among all of the pages

Example

Reports the row density on column ID in table SalesOrders:

sp_iqrowdensity('column groupo.SalesOrders.ID')

Tablename	ColumnName	IndexType	Density
GROUPO.SalesOrders	ID	NBit FP	1.0