I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server.
I have a newly created central database where data is replicated to from another system. I have department-specific databases on the same server containing department-splitted views to the data from the central database for legacy reasons. Replication used to be directly into thoses.
So the new structure is:
CentralDB holds Table1, Table2, Table3.
Dept1DB holds a view to Table1 with a WHERE clause to filter by Dept1.
Dept2DB holds a view to Table1 with a WHERE clause to filter by Dept2.
Dept3DB holds a view to Table1 with a WHERE clause to filter by Dept3.
Etc for each department and each table that used to be in the legacy version.
Some views are straightfoward, but others are more complex because the department column used for filtering is not directly in the table. So some INNER JOINs are in order.
Now, the question is what is the best way to get some performance out of this situation. Some queries can be heavy, and while most table only have a few tens-of-thousand rows, others can have millions. CentralDB will be somewhere around 500Go to give perspective. Is there a better solution than just monitoring closely the indexes on CentralDB? I am looking for avenues and possibilities to explore more in-dept.
(Legacy reasons: there are a ton of in-house legacy applications that used the DeptDBs directly that we don't wish to re-write to point directly to the new CentralDB at this time, so we need this kind of "spoofing" to fool them by accessing the views.)