I have a server that is primarily used for SSAS, heavy processing, however I have SQL Server on it alongside SSAS, but I don't want sql server to hold ssas back.
I have set the SQL Server MAX memory to only 4 GB, and I don't have any large user databases on it.
TempDB also has been kept to 4 files, low profile.
Should I enable the server features that I normally do, like
lock pages in memory and Perform Volume Maintenance Tasks for sql server?
As you can see on the picture below
CPU, memory and disk space are relatively OK, but the processing will also be high.
I am really looking for some technical info, so that I can evaluate things up and apply to each situation.
This Windows policy determines which accounts can use a process to
keep data in physical memory, preventing the system from paging the
data to virtual memory on disk. Locking pages in memory may keep the
server responsive when paging memory to disk occurs. The SQL Server
Lock Pages in Memory option is set to ON in 32-bit and 64-bit
instances of SQL Server 2014 Standard edition and higher when the
account with privileges to run sqlservr.exe has been granted the
Windows "Locked Pages in Memory” (LPIM) user right.
These are examples of problems I had while managing SSAS from another server:
Linked Server from SQL Server to SSAS
routine to backup ssas databases
How to verify a SSAS Backup and how to perform Integrity Checks in SSAS
Best Answer
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?