I'm trying to perform some housekeeping activities on a very large SQL 2016 database, it's total size was about 10TB. This database has been running for many years without any actual housekeeping done. Apps guys have several jobs which will move datas from tables in this database to another database for archiving purposes. Fun Fact, both database sits on the SAME box!
Ok, so initially I ran blitzfirst(v8.21) and I notice there's findings of Forwarded Fetches/Sec high with 46,000 forward fetches recorded. Then I proceeded to execute blitzindex & the top priority from this shows Index Hoarder: Unused NC index with High Writes, which all referring to NC indexes belonging to a single table. As I am not part of the team which design and create this database from the beginning, I'm unable to confirm if this indexes are indeed not required. For this indexes will there be any issue if I just proceed to disable it?
Also for heaps, based on blitzindex's result, there's quite a number of heap tables & heap with NC index table found. Will it be helpful if I suggest the DB owner to consider creating clustered index to it?
Asked by Jason Oon
(13 rep)
Oct 15, 2024, 07:50 AM
Last activity: Oct 15, 2024, 11:17 AM
Last activity: Oct 15, 2024, 11:17 AM