How does MS SQL use the hardware it's running on?

I'm looking at buying a new software package for our company and I need to spec a suitable server for it.

The software uses MS SQL server to store the data.  They haven't (so far) been able to provide me with info I can use to size suitable server hardware.

The company say it can run on SQL express or the bigger versions.  I see that the non-express versions of SQL can use multiple CPU sockets and more than 1GB RAM.

Does the product/database have to be developed in such as way as to take advantage of the extra hardware?  Or does SQL server automatically do this for you?  (and therefore give you much better performance?)

If it's relevant; the software's data is not all in one big database, but is split into one database per client (of ours).

Many Thanks
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
SQL server will use what resources it needs when it comes to CPU and RAM.  If it is a critical system then stay away from Express Edition due to its limitations.  Also keep in mind as to how many users you have as it will affect licensing (core vs user).  It is also recommended to install in a virtualized environment to reduce cost.
Vitor MontalvãoMSSQL Senior EngineerCommented:
By default SQL Server will use all available resources (CPU & RAM). This values can be configured manually to fit your needs.
Since SQL Server 2008R2 Express Edition that database limit is 10GB, so you may check if this is enough for your solution.
ITPOLAuthor Commented:

OK just so I'm 100% clear;

Theoretically speaking, and using exactly the same databases;

If I get a 2-socket server with say 16Gb RAM and SQL Standard
 a 1-socket server (using same CPU), 16Gb RAM and SQL Express;

I WILL see better performance?  Regardless of the application or database design?
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Vitor MontalvãoMSSQL Senior EngineerCommented:
No, you wont.
You can use only 1 socket of 4 cores and 1GB RAM for Express Edition.
ITPOLAuthor Commented:
Hi Vitor,

You can use only 1 socket of 4 cores and 1GB RAM for Express Edition.

Sorry that's my point.  Given that the full version of SQL server can use the additional hardware, does it guarantee me better performance of the same application and databases?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh yes, that's for sure. More because of memory than CPU. Databases are a highly memory consumers.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ITPOLAuthor Commented:
excellent thankyou
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.