Is it OLTP (frequent writes) or analytics?

If OLTP the best advice is create a reasonable load tester and use it for all future schema migrations. This will find all unexpected table locks due to DDL commands like CREATE INDEX, ALTER TABLE etc.