January 28, 2009 at 4:09 am
Hi guys, I have some pretty intensive cubes processing, I don't have a shortage of memory but msmdsrv.exe seems to peak at about 1gig. How can I allocate more memory to it. Is there an AWE equivelant for Analysis Services.
January 28, 2009 at 3:19 pm
If you are running x32 OS then you can use the /3GB switch and modify the memory limits for SSAS. You really need to go to x64 with SSAS as a long-term solution.
If you are not using the memory and are CPU bound then this will not help much. You might need to look at the design of your model and/or modify the MDX queries and calculations to fix the issues that you might be experiencing.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 28, 2009 at 10:22 pm
Yes, I am running 32 bit, but how do I actually assign more memory. Where is the option?
What is the SP_Configure equivelant for Analysis services. I can't find it if I right click properties on analysis services.
January 29, 2009 at 3:26 am
In SSMS, right-click on your AS instance and select Properties. Scroll down to find the Memory options and set these as required, using the Value column. See BOL for what the options mean.
Some changes to AS properties require you to restart AS before they take effect, but I think these are activated immediately.
AS on 23-bit can use the extra memory made available by the /3GB switch in BOOT.INI, but AS is not PAE aware, so there is no equivalent to AWE in Analysis Services and it can not use any memory above the 4GB mark. If you do not have enough memory below 4GB to do what you need, the fix is to use the 64-bit version of AS.
If you do not see the Memory \ TotalMemoryLimit option or you cannot change the Value column for this option, then you do not have the correct permissions to do this work.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 29, 2009 at 3:33 am
See, I have plenty memory available. TotalServerMemory is set to 80. It is a %, but of what, it says physical memory, but is that available memory, why does it not race along at 4gig when I have more than that available.
January 29, 2009 at 4:13 am
Not that it should matter what version you are on, but assuming SSAS 2005 (w/SP3)?
By setting this value in the properties SSAS is just not going to allocate all of that memory to the process. SSAS will use up to that amount that you set. As you use SSAS and run queries you will start to notice the amount increase as values are being accessed and cached into memory. You could run some scripts once to actually warm up the cache to load the memory.
Just make sure that you have enough memory for the other processes on the server and also for the OS to function properly.
Do you feel you have a memory issue? Just wondering what type of performance issue you are running into.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 29, 2009 at 4:19 am
See, it's not climbing thats the problem.
If I run perfmon. SQL is hovering around 13gig. Which is perfect. Analysis services has a maximum of 1.6gig, that wont climb, and doesn't. Even though there is memory available. How can I get it to use 2gig or so.
I am running SQL Server 2008.
January 29, 2009 at 5:06 am
Do you have /3GB set in BOOT.INI? If not, then 1.6 GB is about all that AS will be able to get on your server.
If you do not have /3GB set, then AS is limited to a maximum of 2GB. Out of that there are common Windows services that operate in each process address space that reduces the max available to the process. Using 1.6GB for a single application on a server without the /3GB switch is near enough the best possible. You will never see an application use 4GB on a 32-bit box, because that leaves no room for Windows, anti-virus, and all the other stuff you run.
If you have the /3GB switch, then add 1GB to the best that a single appilcation can have. That all assumes that AS is the only thing running. If you have SQL Server database services also running (your post says you do) then you have to share the sub-4GB memory out between all the processes. This could mean that you have to limit AS to less memory. If you try to use more memory thaan is available you will get a lot of Windows paging, which REALLY hurts performance.
You may need to consider moving AS to a separate server, or better still moving everything to 64-bit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 29, 2009 at 5:35 am
I have the /3GB set, you are suggesting changing that to /6GB. By tomorrow afternoon my server will have 30gig RAM available. I am going to allocate 20gig to SQL. I want to specify that AS must use 5gig, it seems like this is not possible.
Moving to 64bit is not even an option. This is a datawarehouse that has a lot of SSIS packages. 64bit and SSIS is not cool, you need to run stuff from cmd, I am really not keen on changing all that in the near future.
Can anyone answer what that % for the TotalServerMemory that is set in SSAS properties actually specifies. Or is it a dummy property.
January 29, 2009 at 5:49 am
Sorry, that was a typo. I have corrected it to say /3GB.
If you have more than 16GB on the box, you must NOT use the /3GB switch. This is because Windows needs more than 2GB to manage the storage above the 16GB line. You risk that Windows will either not boot or will crash if you have /3GB set on a box that has over 16GB instaled. (None of this applies to a 64-bit box, where the /3GB switch is not used.)
Therefore on your box, AS will be limited to a 2GB address space, some of which will be taken up by common services. You are unlikely to ever see AS use more than 1.6GB on your box.
Even if you move AS to its own 32-bit box, it will never be able to use more than about 2.6 GB. If you want AS to use 5GB then you must move to 64-bit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 29, 2009 at 5:53 am
Can anyone answer what that % for the TotalServerMemory that is set in SSAS properties actually specifies
On a 32-bit box, this refers to memory below the 4GB line. On a 64-bit box this refers to the total installed memory.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 29, 2009 at 6:06 am
On a 32-bit box, the limit of 4GB is there because that is the largest number you can hold in a 32-bit integer.
32-bit Windows uses some special code called PAE that allows access to memory above the 4GB line, but this cannot be used in the same way as memory below the 4GB line. Any application that want to use memory above the 4GB line needs to be PAE-aware. SQL Server database services are and label this awareness AWE (why SQL authors did not use the term PAE I don't know but this difference in terms does cause confusion.) Application Services is not PAE-aware and cannot make use of memory above the 4GB line.
For memory above the 4GB line, Windows divides this into 4KB segments, and gives each segment a number. When an application that is PAE-aware wants to use one of these 4KB segments, Windows will copy it into 4KB of memory below the 4GB line. When the application wants a different segment, the current segment is copied back to its place above the 4GB line and a new segment is copied to below the 4GB line. It is only the memory below the 4GB line that can be directly read, and only memory below the 4GB line can be written to.
Moving memory around like this takes time, but memory moves are quick. It is just about the only way to make use of memory above 4GB on a 32-bit operating system, and lots of other OSs use the same technique as Windows.
On a 64-bit box, you can directly address many TB more memory directly, and therefore the use of PAE and the overhead it gives are not needed.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 29, 2009 at 6:12 am
Not sure what the reasoning is behind not moving to x64 because of SSIS. Sounds like you have everything running on a single server and that is definitely not a best practice. At a minimum move SSAS to its own server so that it does not impact SQL Server and vice versa.
SSIS is definitely built for x64 and there are no issues doing this. It is painless too, just make sure you precompile your script tasks if you have them and open and close them to ensure they are compiled before deployment to the server. Also make sure you have all of the necessary x64 drivers installed on the server (like if you are going to be utilizing Oracle).
If you are not seeing the memory climb then I guess I am not sure what the problem is really. SSAS is not going to consume and reserve the memory like SQL Server. It is only going to start to use it once you start caching results and values. There is some minimum memory that gets allocated once it is started up, but nothing like it was with AS 2000.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 29, 2009 at 6:47 am
Thanks for the help guys. I will check out all the options.
As for the SSIS thing, from what I understand you can't execute an SSIS package frrom a SQL job like you can on 32bit. You have to change it to execute through the xp_cmdShell 'dtexec.exe'.......
That is the pain I don't want to go through at the moment.
January 29, 2009 at 6:58 am
As far as the SSIS thing goes you have some incorrect information. There is no problem scheduling packages in either a x32 or x64 environment. If you can go to x64 you really should to leverage the hardware and resources that you have on your servers.
Good luck.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply