?
Solved

Estimate Memory Requirements for SQL 2014

Posted on 2015-01-28
13
Medium Priority
?
229 Views
Last Modified: 2015-02-01
We are planning to migrate a SQL 2008 R2 server to SQL 2014 server. How do we estimate memory requirements for new SQL 2014? What are the specifics which need be considered to estimate the future memory usage? Is there any guidelines available to estimate future usage from existing server usage?
0
Comment
Question by:Subsun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 300 total points
ID: 40576127
That totally depends on your database and application. If you want to put few tables to memory, you of course need more RAM. If you are planning to build a new database server, then I would preferably buy a hardware that supports up to 1TB
0
 
LVL 3

Expert Comment

by:roycbene
ID: 40576208
To add to this, your limitations are not restricted only to your database and application. If you have Windows 2008 or 20012R2 standard, you are restricted to 32GB of memory - regardless of how much your hardware will support. An HP DL380 Gen9 will support up to 768GB of memory, but your OS may not. Long story short, you need to be mindful of every aspect regarding the foundation of the install. Take a look here:

https://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx#physical_memory_limits_windows_server_2008_r2

Just scroll to the top of the page. That will be a good starting point for you to consider memory. Typically, I find a Windows Server Enterprise license with 128GB of memory does nicely, regardless of the size of deployments.

Regarding the data, how big is your current database? Have you used something such as Tree Size, or Logic Monitor to keep track of trending as far as data growth thus far over a certain time period? Are you expanding in the next year? Two? Three? Again, there are a lot of things to consider. I DO NOT recommend keeping your databases local on the machine. Are you clustered? If so, what network location type do you use to store the data now? Is it a SAN? NAS? Some other file server type?

The more of these blanks you can fill in for me, the better I can assist. :)


-Roy
0
 
LVL 40

Author Comment

by:Subsun
ID: 40576263
This sql is a backend server for Symantec enterprise vault application. It has around 8 databases attached to it and each database have around 300 GB in size. This is not a cluster, it's a standalone server. We have Scom to monitor the server which will have historical data.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 3

Expert Comment

by:roycbene
ID: 40576277
Here's what you need to ask the business:

What time period do we want to project growth for? In this example, let's say 12 months.

During that time, will we be archiving data, or keeping all data?  In some businesses, you're allowed to (or required to) only keep a certain amount of data, like the last 12 months. In that case, you'll need to figure out the data growth (which the subsequent questions will answer) but then back down to the last rolling 12 months. You can't just say, "Right now that amount of data is 100GB," because if your data volume is growing, then the last 12 months is growing too. The time amount might be constant, but the data is not.

Will we be adding additional users? For example, the business might be growing into new territories or acquiring new customers. If they double the user base, then in some cases, the data will start doubling as well.

Do we expect the business volume to grow? If you're adding new PC's and new 'types' of scans, or storing packages for Symantec, this will affect the projected size.

What do you do with items in your quarantine? If the app suddenly starts storing images, this will dramatically affect database size.

Will we be adding data from another source, or logging new data? If you start capturing web site clicks, or in a data warehouse, adding additional sources, then data volume will grow.

Will developers or DBAs be performance tuning indexes? If you're going to let people create indexes, you can easily double (or triple, or quadruple) the size of your data depending on how overzealous they get.

As long as you're asking these questions, you should also ask if performance is expected to stay the same, degrade, or get better. This article will help you with your trending with SCOM

http://blogs.technet.com/b/momteam/archive/2010/04/29/reporting-scenarios-more-samples.aspx


Let me know what else you need.

-Roy
0
 
LVL 40

Author Comment

by:Subsun
ID: 40576412
So how can we relate the database size to memory consumption? is there any formula to calculate the same?
0
 
LVL 3

Accepted Solution

by:
roycbene earned 900 total points
ID: 40576441
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 300 total points
ID: 40576973
What are your actual server specifications?
Do you think is running with a bad performance?

8 DB x 300GB = 2400TB what I think is too much to have them all in memory. Even that you need to have only a database in memory you'll need 300GB of RAM and that's also a lot.

What's the size of the largest table in all those databases?
0
 
LVL 9

Expert Comment

by:schmiegu
ID: 40577136
2012 Standard supports all memory  the Server supports (the only difference between Standard and Datacenter is the number of allowed virtualisations - 2 vs. unlimited)
0
 
LVL 3

Expert Comment

by:roycbene
ID: 40577168
Yeah there's a link I posted above that explains memory restrictions for all versions.
0
 
LVL 3

Expert Comment

by:roycbene
ID: 40577173
Also @ Vitor, there is a link above that explains exactly what is recommended for best practices regarding a Symantec Enterprise Vault environment; including hardware requirements.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40577179
@roycbene

It's always good to follow the supplier recommendations but I didn't and won't read those documents. You could at least post here the information for what are their recommendations for a MSSQL server. After all is what's really matter here.
0
 
LVL 3

Expert Comment

by:roycbene
ID: 40577200
Well, sir, I apoligize that you are not into reading best practices documentation. That's rather unfortunate. However, we can't all be absolute geniuses like you. Some of us regular folk need to stick to things like...you know...what the vendor of a product we are buying recommends. :)

Moving on, however, Subsun asked specifically for a calculation regarding a determination of DB size to memory - which the documentation above provides. Considering the calculation in the documentation is exactly for the environment  that Subsun has (as the requirements for SQL vary GREATLY depending on your environment and the application you are using (ie connections, connection types, temp DB's, backup requirements, etc.)), I would highly recommend that the research, which has been fully documented out and provided by the vendor, be done in accordance with what they recommend. This will make supporting the product (not to mention overall performance) much easier in the long term. As a consultant, it would be an injustice to my clients to not be fully aware of requirements and standards regarding best practices. So, I make it a habit to ensure they are fully educated, myself included, regarding vendor requirements and recommendations. That, sir, is the 'least' I can do.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40577219
Sorry but I think you got me wrong. I said I won't read these documentation. First it's because I'm not work with Symantec and second is because they are so extensive and I don't have time for it. Should be for the author reading not me, I'm afraid.
And if we are trying to help, we should point the relevant points apart from posting only the link to the documentation and that's what I tried to do in my last post. Just tried to call your attention and nothing else between the lines.
Cheers.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question