Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What would be a good way to calculate the recommended minimum hardware configuration for a Windows 2008 R2 64bit that will be running SQL Server 2008 R2 Analysis and Reporting Services? The database server is on another machine.

The current configuration is :

Virtual Machine running on a vmware7 host

2 vCPUs
4GB of RAM
80GB of Hard Disk space.

4GB of RAM appears to be insufficent.

No one has the answer to this question? Perhaps I need to provide more information? Is there a white paper on minimum requirement for SSAS and SSRS?

What started this question was essentially the customer saying, hay the RAM on my machine is all used up. I checked out the processes and noticed owstimer.exe. After a quick search I found that owstimer.exe is a sharepoint component. I say that with some doubt because I have not verified that information.

So I have a Windows 2008 R2 64bit, 4Gb of Ram, 4vCPUs on a machine with SSAS and SSRS. Among the processes I find one called owstimer.exe which might belong to sharepoint. I read somewhere that sharepoint should be installed on a system with a minimum of 8Gb of RAM.

http://technet.microsoft.com/en-us/library/cc262485.aspx

But sharepoint isn't on this machine just SSAS and SSRS.

The databases for this client are stored on a dedicated SQL Server 2008 R2.

More information. I can at limit SSRS and SSAS via their configuration files.

For SSRS

Control SSRS according to memory pressure

http://msdn.microsoft.com/en-us/library/ms159206.aspx

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config.

MemorySafetyMargin 80 /MemorySafetyMargin -- % of memory reserved for
the system

MemoryThreshold 90 /MemoryThreshold --  If Report server memory use
reaches this value, the report server slows down request processing
and changes the amount of memory allocated to different server
applications

Min and Max Memory control in SSRS

WorkingSetMaximum 2097152 WorkingSetMaximum>  
WorkingSetMinimum 1048576 WorkingSetMinimum> 

So we're going to try this out on SSRS and see how it goes.

share|improve this question
2  
So would 80GB disk for anything serious in SSAS. If it is serious, why virtual? If SSRS only, this is basically a .net web application that renders data: think web server specs. –  gbn Dec 5 '11 at 20:01
2  
And how much data, how many users etc –  gbn Dec 5 '11 at 20:11
    
Hello gbn, thank you for your response. I sent the questions along to our client. We're essentially the man in the middle. As far as why virtual, it's because that's what they've chosen as a solution for their infrastructure. The VM with SSAS and SSRS is one of four that are being used in preproduction for an application that will eventually be used by over 100 stores. –  Craig Efrein Dec 6 '11 at 10:10
    
gbn, the client just got back to me. There will be a maximum of 20 concurrent users. They're using .NET, Reporting and Business Intelligence. The client is also telling me that with SSAS and a couple of people connected, they are maxing out the machine's RAM . They don't yet know the size of the data they'll be storing but we're essentially talking about 100 stores plus the website. –  Craig Efrein Dec 6 '11 at 10:21
add comment

1 Answer

up vote 2 down vote accepted

Intuitively, if I was doing an OLAP solution for a retail chain, I'd say your infrastructure is really inappropriate for a system with substantial data volumes. This sort of kit has trouble with the data volumes you get in insurance, which is probably a couple of orders of magnitude smaller than I would expect to see in retail.

As gbn states in the comments, SSRS is just a web application. You can set up a farm - start with one server with a few GB of RAM and a couple of virtual CPUs. Monitor it, and expand it if it's overloaded.

The amount of disk space used by SSAS depends on the volume and the aggregations you put on the cubes. The storage format is quite compact - more so than SQL Server, but if you have large volumes of data it will start to get quite big. If it's getting into the 100+GB range then you should also look at partitioning.

A surprisingly applicable generic solution

Now, your client probably doesn't want to hear this, but VMs are not my recommended hardware configuration for any business intelligence solution. They work OK for transactional applications or anything that is not too computationally intensive. BI for a retail chain is probably a bit aggressive for this sort of infrastrucutre.

As a generic 'starter for 10', My recommended configuration is a bare metal 2-4 socket server like a HP DL380 or DL580, and direct attach SAS storage. The principal reason for this is that a machine of this sort is by far the best bang for buck as a B.I. platform and has a relatively modest entry price. If you put a HBA on the machine then you can mount a LUN off the SAN for backups.

IOPS for IOPS, this sort of kit is an order of magnitude cheaper than any SAN-based virutal solution, particularly on sequential workloads like B.I. The entry level for a setup of this configuration is peanuts - maybe £10-20,000 - and it's a lot cheaper and easier to get performance out of something like this than a VM based solution. For a first approximation, the only situation where this kit is inappropriate for B.I. work is when the data volumes get too large for it, and you need something like Teradata or Netezza.

What can you do with your VMs, though?

  • A good start would be more RAM - try 32GB. SSAS is a biblical memory hog, and you've got slow infrastructure. If you're stuck with a VM, put as much RAM on it as possible. Disk access is slow on VMs.

  • A second avenue is to partition the cube over multiple LUNs, where the LUNs are on separate physical arrays. Not all SANs will give you this much control, though. 80GB is getting into the range where you might get a benefit from parititioning, particularly if you've got a platform with slow I/O.

  • Tune the buggery out of your cube aggregations - try usage based optimisation. The more hits you can get from aggregations the more efficient the server will be.

Without measuring your workload, I doubt anyone here is in a position to make recommendations that are any more specific than that. Although generic, the pointers above should be a reasonable start if you haven't implemented them yet.

share|improve this answer
    
Thank you for the very detailed response. Because of limited space at our data center, VM's are all that we can offer our clients. So the goal is to optimize everything we do for either Xen or Vmware server. –  Craig Efrein Dec 9 '11 at 10:43
    
Can you not find 4U for a two-socket xeon and a 2.5" SAS array? –  ConcernedOfTunbridgeWells Dec 9 '11 at 11:20
    
So essentially that would be dedicating all the cpu and memory ressources from one vmware server to one virtual container. I will let the client know that based on the responses I've gotten from the forum, that he will probably want to rethink his SSAS installation. Unfortunately I don't really know enough about OLAP to offer any useful advice. At least for the moment, I can get away with doubling the RAM for SSRS from 4 to 8Gigs. –  Craig Efrein Dec 9 '11 at 11:36
    
@CraigEfrein - Note that I'm making an assumption that you have large data volumes based on the client being a retail chain. –  ConcernedOfTunbridgeWells Dec 9 '11 at 12:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.