Source: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
--// Validate last time that has been ran the reindex
select name, Stats_date(id, indid) DataAtualizacao
from sysindexes
where id = Object_id('incident')
I used to analyse the index fragmentation collecting eventually by some samples during the day and saving in a table to after I realize a rebuild of the index by “punctual” and never for all of them, this kind of monitoring can help to avoid to run for all of index in the same time, reducing time and wasting excessive server resources.
This method has been important to reduce the lock waiting time and enhance the performance.
dbcc showcontig(incident)
DBCC SHOWCONTIG scanning 'Incident' table...
Table: 'Incident' (2018418560); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 189531
- Extents Scanned..............................: 23755
- Extent Switches..............................: 27544
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 86.01% [23692:27545]
- Logical Scan Fragmentation ..................: 2.43%
- Extent Scan Fragmentation ...................: 35.21%
- Avg. Bytes Free per Page.....................: 777.6
- Avg. Page Density (full).....................: 90.39%
As long as Onyx has had a big index fragmentation issue.