SSAS is a file-based database engine.  It reads data from different files, makes the joins, and services the requests.  All the files requests are going the System File Cache so the overall used memory may not be necessarily found only in the SSAS process.  I have already blogged about how file reading and memory usage is done at the OS level. The same applies for SSAS – it makes a file a request to the OS for file; the OS checks to see if the file is in the Cache.  If it’s not, the file is retrieved from Disk, placed in the Cache, and returned back to the SSAS (aka hard fault).  If it’s found in the Cache, it’s returned back to the process (aka soft fault).  Hence, when analyzing SSAS memory usage, we need to consider the System File Cache.   There is an excellent tool “RAMMap.exe” from Sysinternals, which gives you a list of all cached files.  You can use the tool to view what SSAS files are cached at the OS level. Note: If you run the exe with a text file name as a parameter (i.e. “RAMMap.exe output.txt”), the tool will export all the file paths into a text file; we considered embeding the tool and giving you this list into our app, but we gave up on the idea due to the size of the output file (300Mb on 450GB cube) and the limited value delivered by the report.

I am going to use a server with 1TB of physical memory in the examples to follow. It makes it easier to see the difference in memory behavior when events happened.  Speaking about System File Cache, there is an option in the msmdsrv.ini file called “LimitSystemFileCacheSizeMB”, which controlls how many of the SSAS files will be cached; the cache usage limitation is at the OS level and affects other processes. In the Companion snapshot below, we set the option to “0” which means, that there is no limit; the OS will cache as much as it can (note: this may affect other processes; on the same server, there is a SQL instance running with 100/300GB configured for Max Memory, which is dynamically changed during the ETL).  

So let’s start with several common scenarios, displaying the memory usage and its variations in SSAS:

1. Min/Max Memory: The SSAS properties allow the configuration of the Min and Max memory settings. This is a bit confusing if you apply common sense. The Minimum Memory does NOT mean that this is the minimum amount of memory that will be used by SSAS.  It means that SSAS will start trimming its cache and start taking some other actions if this value is reached!  in my experience, the SSAS will stop accepting new connection when its memory consumption goes above this value.  Compared to MS SQL, there is not YET a concept of an administrator connection in SSAS, so if the server decides not to take new connection requests, you are out of luck. (Note: inside our service, we do maintain an open connection, which we call Admin connection, so we can be sure that we can talk to the server, when new connections are NOT accepted.  This allows us to perform management tasks if needed.)   So when the “LowMemoryLimit” is surpassed by the current usage, the server will stop accepting new connection; in other word, it will NOT talk to anyone new.   It continues to execute its currently running queries, and as far as the outside world is concerned, the service is simply NOT responding – so the usual approach is: let’s kill the process and restart it! Note: Our software handles this condition automatically, by terminating the most expensive queries until the server becomes “responsive” – please, see the blog on Resource Governor for SSAS, which we introduced as a concept back in 2004! When the “HighMemoryLimit” is reached, the SSAS is supposed to start automatically terminating queries…but the exact details about the server behavior with this setting as well as the “TotalMemoryLimit” one are not very clear.  Anyway, we have in production our software, which successfully prevent the “non-responsive server condition” and automatically handles it! Put it differently, without our software, the SSAS is restarted multiple times a DAY due to clients complains; with our software, the server is NEVER restarted due to non-responsiveness!  So if your server is not responding, most likely it’s processing expensive queries…check your PerfMon counters.    

    

   Here is an example of when the sever becomes non-responsive but in reality, it simply continues to process the current requests, while refusing new work due to lack of resources. The “Active Requests” chart show “bubbles/requests”, which represent current actively-processed requests with diameter representing their duration times (i.e. the bigger, the longer the duration of the request). The drop in the Memory usage below the “Limit Low” represents our software taking proactive actions and terminating expensive requests until the server is back to a responsive state! Please, notice that we are talking about 900GB of physical RAM; check also the “In Use” System Memory, “Standby Cache”, and “Free” Memory.  

    

    

   
   

   The High and Low memory setting can be changed dynamically without a server restart and as you can see from the snapshot below, the changes will take effect immediately:

    

    

    

   
   
   
   

2. Normal Memory with a Query: below is a snapshot of a single query and the IO footprint it causes.   Let me point you to the interesting facts.  a) The CPU usage is high which most likely means that data is retrieved from the Storage Engine (since it’s a multi-threaded data access); b) The “System Files/IO” chart shows high Logical IO but NO physical IO, which means that the data is coming from the System File Cache and the process memory containing cached data!

1. Backup:  Below is an example of Memory and IO usage while a Backup is taking place. There are several interesting observations to point out.   Let’s start with the “Stanby”(593GBs) and “In Use”(293GBs) memory in the System Memory chart, and a total of 193GB in "Shrinkable,Nonshrinkable, and Other" memory used by SSAS in the "Usage[GB]" chart. 100GBs are allocated to MS SQL so we get a good match between what’s reported at the OS level and at the SSAS level (293GBs vs. 193GBs).   At this point, the logical question from an MS SQL professional will be: why only 193GB when the cube is close to 880GBs?  Check out the 592GBs of Standby cache; further analysis with the “RAMMap” tool will reveal that the OS has cached all the SSAS file, and the reported “592GBs” of Stanby cache is actually used for caching SSAS files! This is very helpful if you need to restart the SSAS instance  or reprocess of the SSAS files; the cached files are still cached (i.e. 592GBs) after the restart! Note: another interesting observation is the central chart “Logical IO by Process” and its neighboring chart “System IO”. The “Logical IO by Process” chart displays what processes are causing the Logical IO on the System; hovering the mouse over the chart will give you this dynamic information. In our case, we have SSAS causing the most of the IO (i.e. SQL is the other process on this server, which may be responsible for high IO).  The “System IO” chart allows us to compare the “Logical” vs. “Physical” vs. “Cached” IOs on the system; in this case, we ca see that the Logical and the Physical IOs are overlapping, which is common when we are doing a Backup of the cubes. The Cached IO is of little use in this case.         

1. The Famous Storage Engine (SE) vs. Formula Engine (FE) processing times:  This is a common topic worth covering.  Below are  a couple of snapshots with SE and FE queries. Here is a brief review. The first chart represents an SE query. Characteristic: Multi-threaded data retrieval from the storage engine (i.e. higher CPU utilization), high IO, and not much processing of the data (i.e. calculated cells are low!).  As you can see, the CPU is relatively high on a 64 CPU system! The Logical IO is also high (even though it’s coming from Cache!).   The second snapshot represents a FE query.  Characteristics: Single-threaded data processing (i.e. Lower CPU utilization), lower IO, and high “Calculated Cells” count (see Total Cells Calculated/sec)!  What is important to point out here is the fact that the SSAS service is using very little physical memory (i.e. Zero = 100Gb Total – 100Gb SQL usage) and yet, there is NO physical IO on the system! All data is coming mostly from System File Cache!

    

Storage Engine Query:

Formula Engine Query:

1. Processing Command and Memory Behavior:  Below is a snapshot of the SSAS server behavior during multiple-partition processing. Here are some highlights: a) The SSAS process memory usage will drop since most of the cached data and aggregations will be invalidated (note: two Usage charts are placed next to each other to show the change in memory usage). b) the bottom left "Raw Data" chart shows the connection between the number of rows retrieved from SQL and the number of rows processed by the SSAS sever when processing the partitions – as you can see they match exactly! When you issue a ProcessData/ProcessFull/ProcessDefault command against partitions, the number of SQL/data source/ records will match this counter. When the aggregations are processed, the number of records read from the cube will be shown in the chart next to the right - "Aggregatioins".  This will give you a sense of the processing command progress.   Note: the software contains the “Partition Processing” node, which will give you a visual progress on the raw data/records retrieval from the data source as well as estimate completion times. There will be a separate blog on the topic.

The above examples shows some of my observations.   How can you draw your own conclusions?   We give you the tools to run your own tests.  There are a couple of features of our software that can assist you with these tasks. The first one with the “Stress Test”; it allows you to select queries, parameterize them (optional), and execute them in parallel against the server while watching its behavior (note: there is a separate blog on this task). Second, the “Cache Warmer” allows you pre-warm the SSAS and System File Cache with commonly used data; the tool also allows you to empty either  the SSAS or/and the System File cache, so you can analyze queries behavior under different conditions and scenarios (Note: there is a separate blog on this task)  Both features give you the option to empty either the SSAS or/and the System File Cache.

Memory is an interesting and common topic. I am going to start it with the famous question “Who is going to need more than 64K of RAM?”  My response is that I am able to cause an OutOfMemory exception on a box with 1TB of physical RAM.  Luckily, the operating systems were written when memory was a very scarce resource.  As a foreign student, I did not have the right to work off campus so I was making a good 2.50$/hour after taxes.  Spending 800$ on 16Mb memory chip from the Gateway outlet was a major investment, which eventually paid off with good dividends.  The OS was working with very little memory, attempting to make all the processes happy and satisfy their memory allocation requests.

So how is memory working in Windows? When a process is started, it sees a flat directly addressable memory space with addresses ranging from 0 to the max of its bit-ness (i.e. 32-bit gives 2^32 or 4GBs; 64-bit gives 2^64, etc.). This means that at any time the process executing code can request the content from any of these addresses. This is also known as virtual address space since in reality there are only a few MBs behind it.  So how the OS can trick the process that this space exists?   The main smoke and mirrors are Physical Memory (aka Random Access Memory – RAM) and disk files.  There is the concept of Memory Mapped Files (MMF), which basically map disk files into the process virtual address space.  What that means?  The OS create a map between a starting memory address (say 101) and the first byte of the file; if the file is 50bytes long, then then address 150 will correspond to the end of the file. Every time the process requests the content of an address between 101 and 150, the OS will retrieve the corresponding byte(s) from the file.  Hence it’s called a map – i.e. when you request “a”, it’s looked up in the map, “b” is returned. 

What is the need for this complexity? There are a few reasons:

1.  The OS does NOT have to read the whole file. Have you wondered how a file that is 1GB in size can be opened by some processes instantly, while Notepad chokes on it?  The 1GB can be mapped into the process address space, and ONLY what the process need will be requested by the file (with issuing a memory read request which will be mapped back to the offset within the file). Hence, the OS only needs to make an instant metadata entry, creating the map, and the process is ready to access the file. Alternatively, physically reading the file and copying it into memory will take a while…think Notepad.

2. The same file can be shared among multiple processes.  The same file/or already cached bytes/ can be mapped into multiple processes. A good example is a Terminal Server with a 100 active clients using MS Word.  Without the MMF, the server needs to load 100 copies of MS Word binaries; instead only ONE copy is loaded and it’s mapped to 100processes. As you can see, this makes a very efficient use of the available memory, not to mentioned excellent performance and user experience.       

3. The mapped file is used as an extension to the physical memory, to which the process has access.  Think of it as a page file (indeed it’s an extension the system page file; it uses the same concepts).   Instead of loading the file into physical memory, it mapped to the process and can be access as if it was a physical memory.

Now that we have our 100 TS clients starting MS Word at the same time, what will the OS do? Read a 100 times the same file bytes?  It doesn’t sound very efficient. Instead the OS uses a buffer using the physical memory (RAM), aka Cache, to store the bytes so it will physically read only once from the disk, cache the bytes and on subsequent requests, it will avoid the need to access the disk.  Why avoiding the disk sounds like a big deal?  Memory access is a 1000+ times faster than disk access so there is considerable performance difference. (Note: SSD’s are shrinking this difference.)  The used buffer/cache/ is called File System Cache (i.e. it caches read bytes from files).  Since the Cache is using physical memory, at one point the system may decide to take away the physical memory (which may be needed by a process MemAlloc request). Hence it’s called “Stand-By Cache”.

Let take some of this concept, put them together and map them to the more common terminology used in Windows.  The process has Virtual Memory but only some of this memory is actually backed up by Physical Ram or Disk Files.  When a file can be mapped to one or more processes, then the memory it consumed is called “Shared”, since it is NOT specific to the given process.  Some of the data is specific to the process; hence it's called "Private". An example will be a user Word document.  When the user makes modifications to the document, then the memory, storing the document, is called “Modified” (as well as Private).  The combination of Shared plus Private memory is known as a process Working Set.  When a file/bytes/ is requested, it is first checked if it’s present in the File System Cache. If it’s not found, it is physically read from disk, placed in the Cache, and returned to the process.  This is also known as Hard Fault – when a memory page cannot be found in memory and needs to be retrieved with physically access to the disk.  When a requested file is found, then it’s returned from the Cache – this is also known as Soft Fault.

With this in mind, when analyzing memory, we need to be aware of the flow of bytes in the OS.   To summarize the concepts event at a higher level, multiple processes make concurrent requests, resulting in Logical IO. Out of this requests/IO, some of it will be used from the Cache, and some of it will be retrieved from Disk.   There are Performance Monitor Counters that can give us the current status of the system. The “Process->Data Bytes/sec->Total” will give you the Logical IO. The “Physical Disk->_Total” will give you the Physical IO. The “Cache->Copy Reads/sec” (i.e. copy cached bytes into the process address space) will give you the Cached IO.   Of course, this is a simplified view of the actual flow but it makes concepts easier to digest. The Internet is full with in-depth analysis of the topics above so feel free to dig deeper.  “Windows Internals” is an excellent book to read on the topic.

There are also some common topics about configuration options.  The “3GB” and “AWE” boot.ini option was a hot topic in the 32bit world. When a process is started the OS splits the virtual address into two and it gives 2GB to the user process and 2GB to the kernel.  With some application, such as MS SQL server, there was  a need to access as much memory as possible, so while 2GB are not enough for the process, the other 2 are sitting mostly unused.  The “3GB” basically tells the OS to give an extra 1GB of addressable virtual memory to the process, and leave only 1GB to the kernel…but the “SAN’s” came out with their memory hungry kernel drivers and at one point the 1GB of kernel memory was not enough, so there was a need to further tune the memory “split”…  At the same systems usually has more than 4GB, given than 4GB is per-process, the SQL server need physical memory, which is available but cannot be addressed to the virtual address space limitation of the 32bit OS.  Hence, the Addressable Windows Extensions (AWE) was introduced, which basically allowed windows to be created in a process and mapped to physical memory beyond the 4GB boundary using offsets… Not a new concept, it was heavily used with DOS (Disk Operating System) and the “himem.sys” file (if anyone remembers it).  The topics keep going but alas, most of them are obsolete, the 64-bit OS took care of them.  The extra memory also created other issues.  I have done over 300 performance tuning labs. When I am asked what equipment I need, I go for the slowest machine with the least resources.  This type of underpowered machines makes the performance and other issues appear immediately. So a table scan, a badly written TSQL or MDX, or a memory leak show up pretty early in the stress tests.   An “IIS” worker process will be restarted at 1.3GB of memory usage…so it was easy to isolate problematic areas and fix them. The introduction of more memory and faster disk drive makes this task very difficult.  The OS resource limits were forcing developers to pay more attention to how apps are written and create better apps…but those days are gone.    

Now that we know everything about the OS Memory and how it works, let‘s look briefly at the Disk subsystem.  There are a lot of advance topics (i.e. Scatter/Gather Win32 APIs) on how requests are made to the Disk, and more specifically, how to fill up the 64K buffer so it a single request we can get more work done.  There are also the concepts of IO queuing and Queue depth. Multiple Performance Monitor Counters further complicate the discussion.  Over the year, I found a simple approach to analyzing the Disk IO usage.  First, if you have physical access to the server, look at its disk LEDs – if they blink like a Christmas tree, then there is a very good chance that there is an opportunity for tuning.  Again with more memory, a lot of data is cached and the issues are not as easy to spot.  Second, check the Disk sec/Read and Disk sec/Write Performance Monitor Counters.  They are an excellent indication of how the Disks are performing and whether if they are overwhelmed.  This really simplifies the analysis and is very consistent with its results no matter what technology is behind the disk subsystem.  The counters represent how milliseconds are needed for single disk IO – this also matches the disk manufacture definition for disk performance.  For disk with spindles: a) for transactional systems: 2-4msec are excellent; 4-10msec is Ok, and above 15-20msec is bad; b) for Decision Support Systems (DSS)/Reporting: 15-20msec is excellent; 25-30 is good; above 50-80msec is bad.  These are rough guidelines, which worked for me over the years.

In the spindle-disk world, there are also some other good ideas.  The most common one is: “How to keep the physical head from moving between tracks?” Some of the common answers are: a) don’t place files on the same drive that are used in random-access fashion; SQL transaction log placement discussion fits here; b) use only 50% of the disk and use defrag software which pushes the data to the outer rims of the disk (thus achieving maximum performance since the heads are least moved and the most data can physically fit on a track); c) make sure that the disk partitions are properly aligned (W2003 Server and before were creating default partitions with 512byte off the boundary causing misalignment; W2008+ fixed that); Note: our software checks and reports on partition alignments.

The Solid State Disks (SSD’s) appears was a game changer.  In a few months, all the heavy investments in SANs and spindle-based disks were quickly eroded.   This technology is far superior and becomes the de-facto standard for the computer/data industry.   And again, having faster hardware makes more difficult to optimize systems.   

Prerequisites and Deployment Checklist

Prerequisites

The following prerequisites are required for the proper operation of the Companion for MS Analysis Server:

o  Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

o  Microsoft SQL Server 2008 Native Client

o  Microsoft Data Access Components (MDAC) 2.8 SP1

Most likely you will not have to install them since these Microsoft products usually are installed with the SQL Server installation, however each prerequisite is provided with a hyper link to its downloads page.

Deployment Checklist

Database location and size:

Select a centralized SQL Database server that will be used for storing collected data from all services.

The database size will vary based on your settings. The software is self-trimming, so old data (usually over 90 days) will be trimmed automatically. However please allow at least 1GB for the database.

Services Security:

Determine how the installed services will connect to the database server. If you are going to use SQL server authentication you can create the necessary SQL account with the Database Connection Wizard inside the ASDC Manager. If you are going to use windows integrated security, please pre- create an AD resource account which will be used by the installed services. If you are installing the software on one server and will be storing the data in a local database, then you can use Network service or LocalSystem. All of the security settings are configurable in the ASDC Manager.

Self-Updating Configuration:

If you are going to use the Enterprise Deployment scenario, please contact us and decide which IIS

server inside your network will be used for deployment and decide on the FQDN and URL path.

Firewalls:

The services run on the SSAS server itself. The clients establish secured communications authenticated by Active Directory on ports 10800 + 1 for each installed Instance. So a server with 10 installed SSAS instances will use ports 10800-10809. You can also custom configure the ports by setting up the default ServerID=0  tcp channel to port =0, and then you can manually assign each installation tcp channel.