Ram size vs speed
OceanDeep via SQLMonster.com
configuring a new server for reporting purpose. The reporting is based a
subscriber database. There are two configuratons we are considering. One
has 24 Gbytes of Ram and the speed of the ram is 1332. The other one has 32
Gbytes but slower ram speed 1066. Which one is more preferable?
Also, a server with 12 hard drives 10,000 RPM vs 6 drives with 15,000 RPM
under raid 5, would the 12 drivers be preferable as it can handle more
simultaneous read and write even though the hd speed is a bit slower for a
reporting server?
Wing
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-performance/201005/1
Erland Sommarskog
> We are using SQL 2008 std 64 bit on Windows 2008 64 bit std. We are
> configuring a new server for reporting purpose. The reporting is based
> a subscriber database. There are two configuratons we are considering.
> One has 24 Gbytes of Ram and the speed of the ram is 1332. The other
> one has 32 Gbytes but slower ram speed 1066. Which one is more
> preferable?
That depends. The reason you have lot of memory in an SQL Server machine
is to have as much data in cache as possible. Reading from RAM is so
much faster than reading from disk. Thus, 32 GB is better than 24 GB,
even if it is a tad slower. But, if you never fill more than 20 GB of
cache - because the database isn't bigger, or queries and indexes are
well-tuned - then the extra 8GB are not needed, and you are better
served by the faster RAM.
> Also, a server with 12 hard drives 10,000 RPM vs 6 drives with 15,000 RPM
> under raid 5, would the 12 drivers be preferable as it can handle more
> simultaneous read and write even though the hd speed is a bit slower for a
> reporting server?
Most of all, use RAID 1+0, not RAID-5.
Again, the usage pattern of your application matters here. If your
users run crazy queries over a 10 TB database, no cache in the world
will help you, but the disk will matters. But if the data to be queried
is fully cached, the speed of the disk is not so important.
As for 12 slow vs. 6 fast, this is a more difficult one. Again, the usage
pattern depends. As does how the data is laid out. But you also have to
consider that the controller channels are saturated etc. There are people
who this better than me, so I will not give any recommendations.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
OceanDeep via SQLMonster.com
is about 80 Gbytes. It is not a huge one. The biggest table we have is
about 9 Gbyes in size. We have 6~8 tables like that but ranging from 3 G to
6 G. The rest of three hundreds some tables are relatively small.
As far as data caching, we don't expect one single query will fetch 20 Gbytes
of data into the RAM. Some of our big queries could fetch 1~2 gbytes of data.
However, please correct me if I am wrong on this, we have many many query
transactions running and each will fetch data size ranging from small to
large. If we allocate 20 Gbytes of RAM space to SQL, SQL memory manager will
utilize the cache as much as possible meaning data in cache will stay there
until freeing up the cache is needed for more current queries. So
theoretically the allocated 20 Gbytes of RAM for cache buffer should be
always filled as more queries are being executed and more current data are
putting in buffer. It will gradually reach 20 Gytes bytes limit. SQL memory
manager will then manage the buffer as supply and demand. So when you say
'If I never fill with 20 Gb of cache, the extra 8 gbytes are not needed', I
would say 'yes, we will fill it up to 20 Gb of cache but not at one time
instead gradually'. Am I correct in this thinking? If so, this should
apply to 32 Gb. Is it true that besides the ram speed consideration,
choosing 32 gbytes over 20 Gbytes is mainly a decision of keeping the data in
cache longer and allow more room to handle some very large result set queries?
Please let me know what you think.
OD
--
Erland Sommarskog
> database is about 80 Gbytes. It is not a huge one. The biggest table
> we have is about 9 Gbyes in size. We have 6~8 tables like that but
> ranging from 3 G to 6 G. The rest of three hundreds some tables are
> relatively small.
> are not needed', I would say 'yes, we will fill it up to 20 Gb of cache
> but not at one time instead gradually'. Am I correct in this thinking?
> If so, this should apply to 32 Gb. Is it true that besides the ram
> speed consideration, choosing 32 gbytes over 20 Gbytes is mainly a
> decision of keeping the data in cache longer and allow more room to
> handle some very large result set queries?
Yes, from what you say here, I would definitely recommend you to go for
32 GB of RAM, even if it is a tad slower.
You may still have a usage pattern where this is not the best option,
but the general rule of thumb is certainly the more memory the better.
OceanDeep via SQLMonster.com
suggest I should use Raid 1+0 for our new reporting server. Normally what I
hear for configuring a read intensive server (such as reporting), people
usually recommend raid 5. I am curious of your reasoning.
od
Erland Sommarskog wrote:
>> Just want to clarify SQL memory manager how it manages cache. Our
>> database is about 80 Gbytes. It is not a huge one. The biggest table
>[quoted text clipped - 9 lines]
>> decision of keeping the data in cache longer and allow more room to
>> handle some very large result set queries?
>
>Yes, from what you say here, I would definitely recommend you to go for
>32 GB of RAM, even if it is a tad slower.
>
>You may still have a usage pattern where this is not the best option,
>but the general rule of thumb is certainly the more memory the better.
>
--
Erland Sommarskog
OceanDeep via SQLMonster.com (u46587@uwe) writes:
> You suggest I should use Raid 1+0 for our new reporting server.
> Normally what I hear for configuring a read intensive server (such as
> reporting), people usually recommend raid 5. I am curious of your
> reasoning.
other SQL Server MVPs. My own expertise is more with system development
than hardware configuration.
I found this interesting blog post from MVP Linch Shea, and there are
some more pointers in the post, and a long discussion at the end.
http://sqlblog.com/blogs/linchi_shea/archive/2007/02/07/is-raid-5-really-that-bad.aspx