Home > Performence Tuning, SQL server DBA administration > Memory setting in SQL 2005 ,2008 64 bit environment if SSIS and SSAS resides of the same server.

Memory setting in SQL 2005 ,2008 64 bit environment if SSIS and SSAS resides of the same server.


As per business requirement it is pretty much important to make proper memory setting to avoid memory pressure. As per the Microsoft recommendation  on a dedicated X64 SQL database server, where DB engine only running then we have to set 20GB as a max memory if total RAM is  24GB . If SSIS and SSAS resides of the same database  server the same database server then we have to rethink the MAX memory parameter. Although SSIS and SSAS from part of the SQL Server stack they have completely different memory architectures so we  have to monitor their peak memory usage by reducing SQL Server’s max memory settings to ensure SSIS and SSAS have enough room and to see how high their memory usage goes under peak loading.

In my experience on servers where we had to run SSAS/SSIS alongside SQL Server  the SSIS/SSAS process will peak at a certain level depending on the workload thrown at them and we would adjust SQL Server’s memory settings accordingly, or just add additional memory if the peak uses of the SSIS/SSAS processes took up too many resources. I’d also highly recommend adjusting SQL Server’s max degree of parallelism settings otherwise SQL Server will saturate the CPUs denying SSIS/SSAS cpu time when any e.g. data loading/transformation is taking place.

 

In this kind of environment we  have to set 16GB as a max memory if total RAM is  24GB and remaining 8 GB for Operation system so SSIS and SSAS can get some memory. SSIS and SSAS can take the memory which occupied by SQLSERVER.exe.

Advertisements
  1. January 17, 2013 at 9:51 pm
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: