Do I need to match RAM size with DB size? and how to only cache based on date?

Hi

I have a 25GB database
We have web app which savings vehicle tracking data
We are on boarding lots more customers, and with this, the database sets to grow into 100's of gig's

Thats really expensive when it comes to RAM with the data centre

The main reason for the growth is we need to keep the last 12 months data, so this really makes the DB grow in size
The reality is, most people only care about the last 1 month, but we do need to keep the last 12 months for accident data etc

So my question is in relation to RAM and DB size and just caching the last months worth of data from 1 massive table
I've looked into SQL server 2016 stretch DB, but we aren't there yet, so its not an option
Someone mentioned partitioning on dates, but not really sure what that means
Others also mentioned SQL is clever and does this for you, but we do notice a slow down when the memory on the server is on the roof.
And we are seeing growth rate of 200MB per day

we've just run out of ram on the server again
Do i just need to endlessly throw RAM and an ever growing database, or is there a more efficient way to only cache the latest data in memory?
we are using sql server 2016
websssCEOAsked:
Who is Participating?

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

x
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server as any DBMS it's an high memory consumer. Ideally we should give to SQL Server has much memory has the SUM of the databases in the instance but this doesn't work when you have many large databases because there are limits for the maximum RAM you can have in a server.
Other option is to give the same memory as the largest table so at least one working with that table at the limit you can have it all in memory.
In your case, since you have 12 months of data but only the last month is "live" data, you may have memory enough to support that month of data.
Partitioning should really fits on your database by what you explained. You can create 12 partitions (one by month) and after that it will be transparent for SQL Server (it will only work with the necessary partitions to answer a query and it will insert the data in the correct partition automatically). I understand that this feature is new to you so it might be good to start with some readings about it.
1

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
Mal OsborneAlpha GeekCommented:
Not sure if you have "run out of RAM" per se. If SQL reads some data from disk, it will keep it cached in memory for ever, provided RAM is available. If a heap of data was read in March, it will still be in RAM in April, unless SQL is restarted, or another application needs RAM. Thus, if you have a 25Gb database, once every record has been read at least once, it will try to keep the whole lot cached.

This makes sense; hard drive is MUCH slower than RAM, so if RAM is not needed for anything else, SQL might as well use it. You need to make a measure of performance, and decide on a "sweet spot". If most of the data read is in the last month, then enough RAM to cache a month of data should be adequate. The rest is not really going to do anything useful.
0
websssCEOAuthor Commented:
Thanks Guys

Actually the "Partitioned Table and Index Concepts" is very helpful

In my case, the users are usually just interested in the 30 days of data, so does that mean if sql is running for 3 months, its going to pointlessly be caching month 3, and using valuable RAM when it should all be allocated to the last 30 days of data?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
In my case, the users are usually just interested in the 30 days of data, so does that mean if sql is running for 3 months, its going to pointlessly be caching month 3, and using valuable RAM when it should all be allocated to the last 30 days of data?
Correct.
You can create a "live" partition to store the last 30 days, then, for example, a "quarter" partition to store the last 90 days, a "year" partition to store the last 12 months and an "archive" partition to store data older than one year.
1
websssCEOAuthor Commented:
also, with the above in mind, should i be restarting SQL to clear its cache, and then run something that caches the last 30 days data?

Open to suggestions on how to do this properly!!
0
websssCEOAuthor Commented:
Hi Vitor

Thank you for this info, i've been reading a lot, and seems quite an involved subject

So the Live, quarter and year partition are all Table Partitioning?
We constantly new data coming in, about 10 records a second, but this could scale to 100 a second.

Users will also want to see the latest record for where their ASSET is
Do you foresee this as being and issue?
0
Mal OsborneAlpha GeekCommented:
No need to restart SQL. It is usually pretty good at figuring out what data to keep in memory. If, most of the data accessed is recent. then that data will almost always be cached when needed. Unless you set a cap, or some other process wants RAM, SQL just keeps everything read from disk in memory. A slowdown when RAM is nearly all in use could be due to other processes having less to play with. Maybe peg use at half of total RAM and see how that goes?
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
So the Live, quarter and year partition are all Table Partitioning?
Yes. That's my suggestion based on what you've been told us in this question so do not take it as granted but you can start working from this suggestion and tweak it as you need.

Users will also want to see the latest record for where their ASSET is
 Do you foresee this as being and issue?
No issues at all. The data it will be there in one partition or other and this is very transparent for the user. What partitioning does is to separate physically the records so the database engine will deal only with the information that matters improving the performance.
1
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.