Solved

Estimate Memory Requirements for SQL 2014

Posted on 2015-01-28
13
196 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
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 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
 
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 300 total points
ID: 40576441
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 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 45

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 45

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

OfficeMate Freezes on login or does not load after login credentials are input.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now