MSSQL Query Index Stats

/ MSSQL Server /

Display index physical stats.

SELECT * FROM sys.DM_DB_INDEX_PHYSICAL_STATS (
  DB_ID('database_name'),
  Object_ID('schema.tablename'),
  NULL, -- Index ID
  NULL, -- Partition Number
  'DETAILED' -- Mode: [NULL|DEFAULT|LIMITED|SAMPLED|DETAILED]
)
fragmentation <10% geen actie nodig
fragmentation 10-30% reorganize index (ALTER INDEX index_name ON schema.table_name REORGANIZE)
fragmentation >30% rebuild indexes (ALTER INDEX… REBUILD of CREATE INDEX met DROP_EXISTING optie)