MSSQL Query Index Stats

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)

Related Posts