Memory related

Question

Hi

In my environment, total physical memory of the server is 16 gb in that 7 gb has been allocated as Sql memory. In that same server even analysis engine and intergeration services are also installed. When we receive memory alert in task manager I could see sql database engine consumes 5 gb and analysis service consumes 6 gb. In that case Analysis consumes from physical memory else it will take from sql memory.

Answer 1

Limit set for analysis is 80 as high limit and 65 as lower limit.

So the 80 means that SSAS can use upto 80% of the 16Gb which is roughly 12.8Gb (as the assumption for these defaults is that SSAS is the only service on the machine). But if SQL and SSAS are only using 11Gb out of 16Gb that still leaves you with 5Gb free which should be fine. This probably means that whatever alert you have set has too low a threshold for a database workload. Typically you want SQL and SSAS to use as much memory as possible as this allows whatever process is running to be completed as fast as possible.

If you are running other services on this machine and they are getting starved for memory that is not really a good idea. Both SQL and SSAS require a great deal of resources and it is better to not mix them with other workloads if you can avoid it.

Since you are only seeing this spike in memory for 10 minutes it probably means that SSAS is doing a processing operation at this time. During processing SSAS will load the new data into a new copy of the data model and only once the transaction commits successfully will it drop the original copy of the data. This allows the server to keep processing queries from the original dataset and if there are any errors during processing it just drops the temporary copy of the data. But this does come at the expense of requiring extra memory during this operation.

From what you have described I don't see anything particularly concerning (although there are not a lot of specific details). So you could maybe just adjust whatever system is throw the memory alerts to a higher threshold. And I know this is probably easier said than done, but adding more memory to the server is probably another cheap fix as 16Gb is not a lot for a server hosting both SQL and SSAS.

Answer 2

Hi @Laxmi ,

thank you for your post.

Which model did you install, tabular or multidimensional model? The memory is so important to Tabular Model. We should monitor how much memory is used and then onfigure memory usage. SSAS using memory during two phares, Processing and Quering. During processing, SSAS needs memory to load data and create dictionaries and related data structures before it flushes them to disk. In addition, if the database being processed already contains some data, it must hold the previous version of the database until the transaction commits and the new database is ready to query. During a query, SSAS sometimes needs memory to hold temporary data structures that are needed to resolve the query. Depending on the database size and the query shape, these data structures might be very big, sometimes much bigger than the database itself.

For more information, please refer to Memory properties and Introduction to SQL Server Analysis Services (SSAS) monitoring.

Best regards,
Carrin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.