What are the limits to scaling Sql Server Database using Distributed Views?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bbc_mailbox1@yahoo.ca - 24 Jul 2008 16:43 GMT

Hello;

I am designing a large Sql Server 2008 Database system that would
include Distributed Views. The system would consist of a growing list
of Linked Sql Servers, where each server has it's own database, but
all databases use the same schema. I would like to employ a
Distributed View so I can query all Servers using a single Query.
However, I am concerned about the limits of scalability. How many Sql
Servers could I expect to realistically link and query using
distributed views? I realize that the answer to this question would
depend on the amount of data in each db, and hardware configuration of
the servers. However, all I'm looking for now is some general "ball
park" way to quantify the realistic limits of the Distributed View
approach. I've googled the internet, and searched Microsoft, but I
haven't found an answer to this question. Thank you in advance for
any advice.

bbc

Reply to this Message

Eric Russell - 24 Jul 2008 19:36 GMT

As for how many servers may be included in a distributed view before
performance issues start emerging, I expect it would depend on how many
servers a single query will span. Generally speaking, when you examine the
execution plan, you will find that SQL Server is smart enough to only select
from a single server, if the query includes the partition key column.

For example, let's assume that you are partitioning 10 servers by ClientID.
This may require a seek on all 10 servers:
select ... from vCustomers where CustomerID = 37721

However, this may require a seek on one table:
select ... from vCustomers where ClientID = 4 and CustomerID = 37721

Here is an article that explains how to examine an execution plans for
federated queries:
http://www.fotia.co.uk/fotia/FA.02.Sql2KPartitionedViews.02.aspx

If you already have multiple servers setup in your development environment,
then try it out and select the menu option Query.. Include Actual Execution
Plan.. to confirm if that's the case.

> Hello;
>
[quoted text clipped - 14 lines]
>
> bbc

Reply to this Message