January 30, 2008 at 2:39 pm
Hello,
We have 5TB DB on a 8-way 64GB RAM Server. DB has tons of tables with over 200 million rows.
In the existing design Transaction logs are on local 10K drives while DATA/INDEXES and Temp DB are on SAN.
We are thinking of moving this DB on a New Server with 16-way Processor and 128 GB of RAM. On this Server we will over 3TB of Local Space (MSA's) so we are thinking of moving Temp DB and Indexes on this space.
Do you think it will give us nice performance gains?
I will really appreciate comments and any other suggestions.
Thank You,
Jay
January 30, 2008 at 7:44 pm
You'll only get big performance gains if your performance is constrained in some way on your current system. If you move to an IO subsystem that can provide faster reads then you may see some performance gains - it really depends.
As for more memory, unless you're constrained at present, you may not see any benefits.
There's a good whitepaper - Physical Database Storage Design - that may help you - see http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
This is a pretty vague reply, but it's hard to make generalizations without a *lot* more info about your workload and current performance characteristics.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 30, 2008 at 8:28 pm
We join multiple tables with over 300 million records to create other tables for reporting purposes. That's why we are thinking of moving Indexes and Temp DB on Local drive hoping that will improve I/O performance and save us time. Any Ideas?????
Thanks.
January 30, 2008 at 8:34 pm
It depends - if the IO bandwidth available from the local drives is more than that from the SAN, and your joins are IO bound, then you *may* see an improvement in performance. It could be that you'll see lower performance because you lose the benefits of whatever readahead capabilities the SAN controller has. The only way you can tell is to try it on a test system - it's not a question we can answer definitively for you I'm afraid.
Hope this helps
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply