Memory related

Question

Memory related

Laxmi 61

If we allocate memory for sql server then services like analysis , reporting,, integeration will consume memory from the same else we can allocate separate memory?

0 comments

Answer accepted by question author

Erland Sommarskog 134.7K MVP Volunteer Moderator

The memory configuration you do with sp_configure 'max server memory' applies only to the Database Engine. In this context, Analysis Services, Reporting Services and Integration Services are different programs just as Explorer, Word or whatever you would be running on the machine.

If you are running SSIS, SSAS and/or SSRS on the same machine, you need to account for that when you set max server memory-

0 comments

1 additional answer

Your answer

Answer 1

Laxmi 61

Thanks then in my server., daily at particular time we are receiving memory alert, while logging into the server in task manager it's showing analysis service consumes more when compared to sql engine, and it immediately dropped after 5 mins.. But not able to view what is happening by that time? Can you pls tell me is analysis only consumes more e? If yes then how to analyse that?

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-02-20T11:25:00.547+00:00

If your question is about what Analysis Services is up to, you should tag the question with sql-server-analysis-services. I believe that there is a separate tag for SSAS.

Myself, I have never worked with SSAS or know anything about it, so I can't speak to that.

When it comes to the Database Engine, its strategy is to read data into the buffer cache, and keep it as long as possible. That is, it is never releases memory just because "it is not used any more". However, if the operating system signals that there is memory pressure, SQL Server will indeed trim its caches and release memory in order to be a good citizen. But that may take a little time if there are dirty pages that needs to be written to disk first.

As I said, I don't know SSAS; but it is not unlikely that it works in a similar fashion.

One way to avoid these memory alerts is to limit the memory for each service, so that the total does not exceed the available RAM in the machine. (As far as a service has a setting for this, that is.) But if these spikes are only occasional, this could also mean that services are generally running with less memory than needed. And at least for the Database Engine, lots of memory is usually a good thing.

Share via

Memory related

1 additional answer

Your answer