0

I do tech support for a web application. Recently, a dealer/client wanted for us to specify minimum requirements for Microsoft SQL server. I found several articles on MSDN that had barebone requirements; essentially these were the minimum to run SQL Server itself (1ghz, 512mb RAM, -ish). I could not find any kind of guides that said "If you have X users, then you need Y RAM and CPU" or anything similar.

My engineering department through out a single number with no respect to load, and said 4 cores and 8GB of RAM. I know that SQL server likes its RAM, but I'm not sure how much or precisely why.

What metrics are used to determine load on SQL server?

How can I develop a set of minimum hardware requirements for SQL Server for any random application?

As far as the application I support, we use SQL 2008 R2 and later; Standard or Enterprise (not Express).

  • Welcome to Hardware Recommendations. If you're asking to determine what the minimum requirements are, this question probably should go to SuperUser. To make this a Hardware Recommendations questions, you need to give us your requirements, whether it be "I plan on having X users" or "I need Y RAM and CPU" doesn't matter to us. – timuzhti Dec 20 '15 at 2:30
  • I'm looking to make a graph, but I don't know what makes SQL induce a load. Is it users, is it connections? Is it something else? Therefore, this question is asking about both axis' of the graph. – YetAnotherRandomUser Dec 23 '15 at 19:40
  • The only "technical support" mentioned in this question is my occupation, and has no bearing on the question itself except for apparently providing TMI. – YetAnotherRandomUser Dec 23 '15 at 19:59
  • Specifying requirements is for SuperUser. Hardware recommendations is for when you actually have those requirements, a budget and want to buy something. – timuzhti Dec 24 '15 at 3:07
1

Estimating what you will need without having a working system to extrapolate from is very, very difficult. If you must do so, you need to have a very well defined use case. From that, you should be able to extrapolate the necessary tables, queries, frequency of access, etc., and come up with ballpark CPU, disk size, and disk performance characteristics (IOPS and throughput).

Unfortunately, the way most people do it these days is they get the basic system working first, test the system with X users and then extrapolate what would be needed for Y users. Database scale is generally fairly linear if the schema and code are written well. Then when the requirements change (which seems to happen much faster than servers get depreciated), they upgrade or downgrade servers to compensate. This is where AWS and other cloud providers shine because if you're constantly changing what you need, it's much easier to switch systems if you don't own the hardware.

Here are some hints for capacity planning:

  • If the vast majority of disk access isn't writes or your cache hit ratio is less than 99.5%, you don't have enough RAM. Average Page Life is also an important metric
  • SQL server does writes in batches every minute or so. It will max out your disk IOPS no matter how many IOPS your system has during that time. The thing to look at is how long the flushing lasts relative to the period between the flushes
  • Logical disk reads often appear as CPU usage (because the pages are in cache), and may indicate the need for an index
  • Database data files should be on separate drive sets from other files and IOPS is generally the most important factor for these drives. Temp database should probably also be on it's own drive set and IOPS is also most important here. Log files are on yet another drive set, but throughout is most important for this set

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