We, too, are on prem sql server. We've created a few indexes. We created a naming convention that did not conflict with the existing indexes. We created a daily SQL job that checks to see if the indexes are missing and will create them if they are. If you do a drop and recreate of the DW, like we do periodically in our test environment, the daily sql job automatically takes care of it. I can't recall if other modifications will drop all indexes, but we have this script running daily in prod and test environments just in case. I'd just be careful not to add too many so you don't impact loading.
