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.)

Many thanks.

  • Why not just include the department Id column in the new table? If you’re replicating the table from somewhere with a known department then this should be simple. It’s hard to advice if an unknown index would be useful for an unknown query. Generally though if you have filters that will cut down the amount of rows that will need visiting in a table significantly, then indexing is usually a good idea. – Andrew Sayer Jan 12 at 20:21
  • some tables will have it, others won't. Example, in a BillHeader-BillDetail combo, the header will have the department code, but the detail won't, so the view pointing to the detail table will need "INNER JOIN BillHeader" coding. – Philippe Jan 13 at 16:58

I try to answer to all your questions:

  • If I create indexes on the CentralDB's table, will the views use it?

Yes for sure, if the indexed columns are used in the views, like the Department Code in your example.

  • Are Columnstore Indexes better choice?

Yes and No, it depends on numbers of rows and the usage of the central table. It's something you have to test.

  • Would I be better to create Indexed views?

No because you can't index views that refer to an external table.

  • Is there some other method for making sure cross-database stuff works well?

Cross database queries work well by themselves like queries not cross databases. Your way is good but in general the best is redesign the applications, especially legacy application. If you don't want to do this, you have to pay it in form of performance issues. Always relay on relational theory and normal forms.


Fisrt, you cannot create indexed views when the tables are spread over different databases.

Second, SQL Server have special DMVs with name beginning as dm_db_missing index_* that will be filled with demands of creating indexes needed for the executed queries. So let it works a lot (one month) and look at the content of theeses views to create the appropriate indexes.

Once you have created these indexes you can try a columnstore one instead, and check the difference in termes of performances.

Not the answer you're looking for? Browse other questions tagged or ask your own question.