Sql-server – SSAS – what could I monitor in SSAS to fine tune its memory settings

configurationmemorysql serversql server 2014ssas

This question is a follow up from the
SSAS – what could be a good starting point for memory settings?

What and how can I monitor the server resources in order to best identify the settings that I should apply to my SSAS instance?

In this case we're talking about sql server 2014, and the server is a windows server 2012, with the spect below

Best Answer

When trying to fine-tune your memory settings there are two things you'd want to watch:

MSOLAP Memory counters for your server in Performance monitor

Specific DMV queries over discover_object_memory_usage rowset and discover_object_activity rowset.

You can always use a third-party tool to monitor your resources. Also, you might want to check out this article – it gives a detailed description on how to achieve exactly what you are looking for.

Related Solutions

Sql-server – sql server max memory includes SSIS

(From the screenshot) I see an unusual configuration where you have SQL Server's Min and Max memory set the same. Leave min memory as default. See my answer to SQL Server Min Server Memory.

To determine ideal memory, refer - How to determine ideal memory for instance? and SQL Server Maximum and Minimum memory configuration

We have SSIS running packages on our production servers and they hardly cause any memory issues. I just leave enough memory for OS, since DTExec.exe is an external process to sqlserver.exe and so its memory requirements wont be included as part of MAX Memory. Also, SSIS is designed to process large amounts of data row by row in memory with high speed.

If you feel that there are SSIS packages that does run for hours, then I would suggest you to monitor below SSIS Performance Counters:

Buffers in use
Flat buffers in use
Private buffers in use
Buffers spooled
Rows read
Rows written

and from Top 10 SQL Server Integration Services Best Practices - SQL CAT Team

Process / Private Bytes (DTEXEC.exe): The amount of memory currently in use by Integration Services. This memory cannot be shared with other processes.

Process / Working Set (DTEXEC.exe): The total amount of allocated memory by Integration Services.

Old but still relevant : Integration Services: Performance Tuning Techniques

Sql-server – SSAS and SQL Server on the same server

Why is SQL Server on the same box?

"Perform Volume Maintenance Tasks" should certainly be granted to the service account used to run SQL Server since that will allow SQL Server to not spend time unnecessarily zero-ing files during expansion of existing files and creation of new files.

"Lock Pages in Memory" would be a good consideration if you want to guarantee SQL Server has enough memory to perform the workload assigned. If you don't care about the SQL Server workload, why is it on the same box as SSAS?

Best Answer

Related Solutions

Sql-server – sql server max memory includes SSIS

Sql-server – SSAS and SQL Server on the same server

Related Question