When you start using Analysis Services with larger dimensions and cubes, you may notice that your SQL Server is performing poorly if it’s on the same server. By default, Analysis Services, SQL Server, Integration Services and Reporting Services (and perhaps even Full Text Search) are all fighting for valuable memory. Setting the caps on each of those services, and ensuring that other services aren’t chewing up memory is important for a well-performing SQL Server.
Greg Galloway points out a great tool above from Sysinternals called RAMMAP. Looks like a great utility for finding out just what is gnawing at your memory, and whether there’s spyware hijacking your system.
I can’t count the number of times I’ve seen a new client’s server in the following state. The server has SQL and SSAS on the same box. Both are set to the default memory limits (which is no memory cap for SQL and 80% of server memory for SSAS). By the time I see the server, SQL and SSAS are fighting each other for memory, causing each other to page out, and performance degrades dramatically. (Remember what I said about disk being a million times slower than RAM?)
What’s further confusing with SQL configuration, is SQL uses “KB” as the default max memory setting, while Analysis Services has just “80” which means use up to 80% of physical RAM. Typing in a number > 100 will allocate an absolute value of the “Bytes” of RAM Analysis Services will use. So much for consistency.
Don’t forget to clear out the event viewer if you have a mysterious “services.exe” eating up memory. I have seen a large security log take up 700MB of RAM.
No comments:
Post a Comment