We currently have an SQL Federated DB split over 10 shards in roughly equal portions of data, filtered by a Client ID.
At the moment we are experiencing performance problems executing filtered queries, for example, running a query for a specific Client can take over 3 minutes to return 4000 rows in some shards. However, running exactly same query in an unfiltered connection on the same shard returns within a timely 4 seconds. The one noticable aspect is that the shards experiencing the slow down tend to contain more Clients albeit with less data. The most likely performance inhibitor (I believe) would be indexing and something that ties into the Filtered / Unfiltered connection.
Having a search around I haven't found much information on query performance across shards / specific Indexing strategies on shards (apart from Azure apparently doesn't support Indexed Views). My impression (and hence need for clarification) is that Indexes are applied to all members of a shard and not on a member by member basis.
If the former then we're in a bit of a pickle, apart from resharding this particular shard which doesn't make sense considering the only difference is the number of clients, not the size of the data. A couple of things we're about to try are explicitly adding the filter to the Indexes or even adding the filter to each query. Safe to say, we're not happy moving away from a Filtered connection.
Has anyone else experienced this problem or could possibly provide some direction that an unfiltered connection significantly outperforms a filtered connection?
Thanks in advance...
Indexes in federations are applied on a Federation Member-by-Member basis. If you started with a single indexed member and performed a SPLIT operation, then the indexes are automatically applied to the products of the SPLIT. But if you have applied indexes after multiple members were created, you need to explicitly add indexes to each member.
So hopefully you are not in a pickle.
You probably want to consider alternatives to federations moving forward since the feature is not supported by the new SKUs announced in April.