0

I have some SQL Server instances used to support an app with single tenant DBs, so each instance has roughly 50-80 databases, ranging in data from 500-600GB for all databases combined. This is running on SQL Server 2016 standard. I have a server with the maximum 128GB RAM provisioned, and it still appears to be insufficient. Performance suffers with a lot of concurrent requests and there's a lot of I/O on the server. I can't say I'm surprised given the ratio of ram to data is roughly 1:5.

The one consideration is that a lot of the data is old, and I doubt that customers really need to access this data. So I'm trying to determine if there's any way to reduce the amount of RAM SQL Server needs if it's going to only be storing the actual data accessed. I'm not a DBA, so not sure how SQL Server will load data into memory. Is it safe to assume that if there's a table scan or index scan performed, it will load all that data into memory? So if I have a large table that I search through and for whatever reason the query plan decides a scan is better, even though it will not return all the data, will the table scan load everything in memory?

With the above in mind, all I can think of as a solution is to archive data to a separate table that does not get frequently accessed so the data does not get loaded to memory. So when running a search, it would only be accessing a table with 2 years of data, if they wanted to search older data there would be a checkbox to allow for it, which people would likely not use most of the time. Is that something that would help reduce the memory pressure? We could also consider shipping data to a completely separate data store. Are there other strategies people use besides data archival to reduce memory pressure given a database that may have 10+ years of data where clients are mostly interested in recent years?

Improve this question
2
  • 4
    Your plan to archive data is just going to mask your real issue, which is that the queries used perform badly and need to be tuned. Tuning queries (including better indexes) will prevent them from trying to access unneeded data and thus not need to be scanned.
    – Jonathan Fite
    Commented Jul 11, 2022 at 16:01
  • Also you if you add more memory to one of these servers you can install a second SQL Server instance to take advantage of it.
    – David Browne - Microsoft
    Commented Jul 11, 2022 at 21:19

1 Answer 1

Reset to default
2

Is it safe to assume that if there's a table scan or index scan performed, it will load all that data into memory?

So if I have a large table that I search through and for whatever reason the query plan decides a scan is better, even though it will not return all the data, will the table scan load everything in memory?

Yes and Yes. That's why having the proper indexes is so critical. And that's the other strategy for dealing with older data: make sure you don't have to scan through it except in rare cases.

Common approaches include:

  1. Providing nonclustered indexes to support common search patterns.

  2. Using partitioning to enable scans to skip over partitions containing data that doesn't match a where-clause criteria on the partitioning column.

  3. Using Clustered or Nonclustered Columnstore Indexes to compress data, and enable column-wise scanning and caching of data.

  4. Implementing Page Compression possibly in conjunction with partitioning to reduce the size of row-based (non-Columnstore) tables.

Improve this answer
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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