moving to multiple servers?

Hi,
When you've upgraded your server (faster CPU, faster disks, more memory)
and when you've improved all queries and indexes, what's the next thing
to do when you're database server gets performance problems?
I've tried out spreading data and using multiple servers, but it seems
using 1) join queries on tables residing on different servers and 2)
using distributed transaction have negative impact on performance.
I wonder how large firms, storing terrabytes of data, running websites
with thousands of simultanuous users inserting, deleting and updating
data continuously, manage to stay out of performance troubles?
Is it just well prepared development, saying data is spread over
multiple servers in a way that the necessity of the servers have to
speak with each other is minimized?
Thanks in advance,
Kind regards,
Peter Van Wilrijk.The multiple server scenario only really gives a performance boost if you're
using distributed partitioned views, and using those requires a very specific
table design. Check out BOL for basic info on DPVs, plus there are plenty of
articles and whitepapers out there on their use.
You're correct, in most cases simply moving a table to another server and
linking to it will actually decrease performance. If you've done your
hardware tuning, OS and SQL Server tuning, you really need to dig down into
not only the database design, but the application design on top of that.
Companies with huge OLTP databases spend a great deal of time on optimizing
database and application design.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
processors.
"Peter Van Wilrijk" wrote:
> Hi,
> When you've upgraded your server (faster CPU, faster disks, more memory)
> and when you've improved all queries and indexes, what's the next thing
> to do when you're database server gets performance problems?
> I've tried out spreading data and using multiple servers, but it seems
> using 1) join queries on tables residing on different servers and 2)
> using distributed transaction have negative impact on performance.
> I wonder how large firms, storing terrabytes of data, running websites
> with thousands of simultanuous users inserting, deleting and updating
> data continuously, manage to stay out of performance troubles?
> Is it just well prepared development, saying data is spread over
> multiple servers in a way that the necessity of the servers have to
> speak with each other is minimized?
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
>|||James, Joseph,
Thanks a lot for your advice.
I inventarised all database references with free trial of Diana, found
on the web. Now, I started changing database structure, duplicating
static tables, user defining functions and adapting database references
in stored procedures, so the most often used stored procedures don't
have to link.
Of course this requires table synchronization when those "static" tables
have to be updated. I'm writing routines for it. Perhaps it's not the
best solution ... but ... We're a small company with small budgets. So,
no much money for special hardware setups or for expensive training
courses on features as replication or federated sql servers.
Kind regards,
Peter van Wilrijk.
JosephPruiett wrote:
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
>>Hi,
>>When you've upgraded your server (faster CPU, faster disks, more memory)
>>and when you've improved all queries and indexes, what's the next thing
>>to do when you're database server gets performance problems?
>>I've tried out spreading data and using multiple servers, but it seems
>>using 1) join queries on tables residing on different servers and 2)
>>using distributed transaction have negative impact on performance.
>>I wonder how large firms, storing terrabytes of data, running websites
>>with thousands of simultanuous users inserting, deleting and updating
>>data continuously, manage to stay out of performance troubles?
>>Is it just well prepared development, saying data is spread over
>>multiple servers in a way that the necessity of the servers have to
>>speak with each other is minimized?
>>Thanks in advance,
>>Kind regards,
>>Peter Van Wilrijk.|||Thanks Joseph,
I thought clustering was there to handle (backup) hardware faillure, not
to improve performance. so your answer made me curious and I started
searching more info.
I found out ...
1) SAN = an array of disks (storage devices)
2) multiple servers can be accessed as 1 virtual SQL server
May I conclude that ...
1) clustering can be used as a kind of load balancing, meaning CPU and
RAM work (eg execution of stored procedures) is spread (at random?) on
the multiple servers in the cluster?
2) clustering always requires a shared disk system
3) since you work with a shared disk ... performance issues due to high
disk access isn't solved with clustering?
4) or can SAN be used as a load balancer for storage access, meaning SAN
can read and write simultaneously on multiple disks and act as if it's
virtual one disk?
I'm really curious about this.
Thanks in advance,
Kind regards,
Peter Van Wilrijk.
JosephPruiett wrote:
> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8
> processors.
> "Peter Van Wilrijk" wrote:
>
>>Hi,
>>When you've upgraded your server (faster CPU, faster disks, more memory)
>>and when you've improved all queries and indexes, what's the next thing
>>to do when you're database server gets performance problems?
>>I've tried out spreading data and using multiple servers, but it seems
>>using 1) join queries on tables residing on different servers and 2)
>>using distributed transaction have negative impact on performance.
>>I wonder how large firms, storing terrabytes of data, running websites
>>with thousands of simultanuous users inserting, deleting and updating
>>data continuously, manage to stay out of performance troubles?
>>Is it just well prepared development, saying data is spread over
>>multiple servers in a way that the necessity of the servers have to
>>speak with each other is minimized?
>>Thanks in advance,
>>Kind regards,
>>Peter Van Wilrijk.|||> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
No. One SQL Server instance is only executing on one node (server) which at that moment is the only
node that can access the shared disk. Cluster is only fail-over. All types of load balancing takes
some work, whether it is using replication, distributed partitioned views or having several
instances and manually partitioning the information amongst them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message news:42C10BF2.2020205@.koopjeskrant.be...
> Thanks Joseph,
> I thought clustering was there to handle (backup) hardware faillure, not to improve performance.
> so your answer made me curious and I started searching more info.
> I found out ...
> 1) SAN = an array of disks (storage devices)
> 2) multiple servers can be accessed as 1 virtual SQL server
> May I conclude that ...
> 1) clustering can be used as a kind of load balancing, meaning CPU and RAM work (eg execution of
> stored procedures) is spread (at random?) on the multiple servers in the cluster?
> 2) clustering always requires a shared disk system
> 3) since you work with a shared disk ... performance issues due to high disk access isn't solved
> with clustering?
> 4) or can SAN be used as a load balancer for storage access, meaning SAN can read and write
> simultaneously on multiple disks and act as if it's virtual one disk?
> I'm really curious about this.
> Thanks in advance,
> Kind regards,
> Peter Van Wilrijk.
> JosephPruiett wrote:
>> Setup clustered servers in SAN enviornment with 8 gig of memeory and 8 processors.
>> "Peter Van Wilrijk" wrote:
>>
>>Hi,
>>When you've upgraded your server (faster CPU, faster disks, more memory) and when you've improved
>>all queries and indexes, what's the next thing to do when you're database server gets performance
>>problems?
>>I've tried out spreading data and using multiple servers, but it seems using 1) join queries on
>>tables residing on different servers and 2) using distributed transaction have negative impact on
>>performance.
>>I wonder how large firms, storing terrabytes of data, running websites with thousands of
>>simultanuous users inserting, deleting and updating data continuously, manage to stay out of
>>performance troubles?
>>Is it just well prepared development, saying data is spread over multiple servers in a way that
>>the necessity of the servers have to speak with each other is minimized?
>>Thanks in advance,
>>Kind regards,
>>Peter Van Wilrijk.sql