July 11, 2005 at 3:41 pm
I am looking to buy a server to run SQL2000 Analysis Services from. My question to you is: What do you use to run your OLAP services? I could listen to the vendors but we all know they never seem to get it right.
I just wanted to hear from those in the OLAP trenches about the hardware they think works and doesn't work with OLAP.
July 12, 2005 at 7:07 am
Hi Steve,
In the past, when I worked on a CRM system, we actually didn't really need to do anything special to get detailed realtime reporting, besides good design, and using SQL Profiler. We were able to get nearly all reports to generate in under 2 seconds each.
Our server was a P4 2Ghz, 1GB RAM, 4x20GB HD in striped mirror configuration. This was about 2-3 yrs ago.
I think your requirements and design will determine the hardware needs more than just arbitrarily buying the biggest, and baddest hardware out there. However, if you need the best speed, a striped mirror is very hard to beat for read operations, which should account for around 90% of the database traffic.
These days, I would suggest possibly SATA with command queuing support on the drive, and RAID controller.
At work, we use Oracle, and since some of the requirements are different, the design is less than ideal, and other factors, some of the reports take as much as 2 minutes on the test server, but run much faster in production (test server is a single drive system, where the server uses a large striped mirror). In Oracle, full table scans are not considered a bad thing, if you need more than a small fraction of the data. You are expected to have a decent drive sub-system to support those.
I hope this helps,
John Goodwin
July 12, 2005 at 5:32 pm
You may also want to skim through the Operations guide (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx) .You may also get some ideas from the Performance Guide (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx)
From our point of view, disk space isn't always so critical re: storage of the actual cubes and aggregations, but it has caused problems when processing (ie temp space used when calculating the agregations). RAM is nearly always important with AS2K due to the service loading up dimensions into memory on startup. AS does/doesn't multi-thread across CPU's, I think on the reads (for processing it may) but in processing queries, it won't unless/until you use multiple partitions (this is nearly always a good idea anyway).
Cheers,
Steve.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply