Friday, March 23, 2012

Joins and performance

Can anyone tell me what type of hardware upgrade will make the biggest
different in join performance?
thanks,
Aaron
"Aaron Prohaska" <REMOVE_THISmohaaron@.gmail.com> wrote in message
news:%23rhBA4usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> Can anyone tell me what type of hardware upgrade will make the biggest
> different in join performance?
>
Before going further, I'll warn you that query analysis, tuning and
application design have a much greater potential to increase performance
than hardware.
Keeping that in mind, there are a couple of things to look at.
First, there are 2 kinds of databases. Those whith enough ram to cache the
entire active portion of the database, call these memory-sized databases.
And servers whose active database is much larger than the server's ram, call
these larger-than-memory-sized databases.
So a couple of factors:
-A memory-sized database is much faster than a larger-than-memory-sized
server.
-Configuring and tuning a memory-sized database is vastly easier.
-A memory-sized database scales very will with increases in CPU speed and
number.
These factors are what's driving database servers to 64bit platforms, where
you can throw gobs of memory at them. Anyway this means: If you have have a
memory-sized database server by adding ram, do it.
Anyway, for a larger-than-memory-sized database server, you have to consider
the details of the server workload, and consider how much physical IO will
be occuring during your server's workload. If your queries require physical
IO-intensive scans, you might benefit from faster disks, cleverly deployed
in fast RAID configurations.
David

No comments:

Post a Comment