IndexProperty

Recently I had problem of slow insert on tables
During optimization we changed FillFactor of indexes and that improved the performance of insert.
We change the fill factor to 50%.

Lets see how to check different propery of indexes

There is a function INDEXPROPERTY which will give us various index properties.

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )

We can get the details from BOL or http://msdn.microsoft.com/en-us/library/ms187729.aspx

We will see some important properties here

–FillFactor

SELECT Indexproperty (Object_id(‘fact_view’), ‘Ix_FACT_VIEW_url_key’, ‘IndexFillFactor’)

–Clustered Index

SELECT Indexproperty (Object_id(‘fact_view’), ‘Ix_FACT_VIEW_url_key’, ‘IsClustered’)

–Unique Index

SELECT Indexproperty (Object_id(‘fact_view’), ‘Ix_FACT_VIEW_url_key’, ‘IsUnique’)

Other  available properties are as below

IndexDepth
IndexID
IsAutoStatistics
IsDisabled
IsFulltextKey
IsHypothetical
IsPadIndex
IsPageLockDisallowed
IsRowLockDisallowed
IsStatistics