SQL Server not properly using memory? Ihave a database with two tables in it. One table has approximately 20 million rows and another has approximately 70 million rows.

When I run sp_spaceused on these two tables I find that the total size of the data is ~6.7GB and the total size of the index is ~2.6GB. Combined this is a total of ~9.3GB.

This is the only data on this server.

I am a SQL novice so I would assume that the amount of RAM these two tables will eat up would be somewhere close to the 9.3GB after the entire data set and indexes have been loaded into memory. But I am apparently woefully wrong as after running queries for a few minutes the memory taken up by the SQL Server process will hit 30+GB and continue to climb. I've even tested running the same query over and over again and the amount of memory being taken continues to climb.

The reason for the excessive amount of RAM is that the eventual size of the data will be 5-6 times this size and this will be used for data analytics.

My question is why is SQL Server using so much memory? If the entire table and index are loaded into memory what is it continuing to load into memory? Is there a way I can control this? Is there a different type of index I can be using besides nonclustered indexes if my goal is in-memory analytics? I have been reading into columnstore indexes but after testing on a smaller dataset I was actually able to achieve better performance by removing indexes altogether than I was with a columnstore index.

Overall, I'm confused as to what is going on with this server. Can anyone chime in with some guidance? At this time I'm trying to test out SQL Server 2014 but it's still in CPT2 so it isn't a solution until release.

I've also looked into column based storage technologies such as Amazon Redshift but their service is not HIPAA compliant.

On this question I found a few queries to run.  The cached size of my database right now is 32230.390625.  This is greater than 3 times the total size of the database.

db_buffer_pages = 4125490

db_buffer_MB = 32230


I also wanted to do a bit of testing and decided to completely wipe the database, restart the SQL service, and reload the data.  When I did that it was using ~20 GB of RAM.  I can't understand why.  That is over two times the size of the raw data and index.
As stated in the referenced answers, SQL Server will use as much memory as you let it use. Additionally, SQL Server uses memory for a variety of things that are isolated to the domain of "data storage." In addition to the things that mrdenny noted, it also caches the query plan of a query 

In SQL Server, memory use is tracked by Memory Clerks. Take a look and see how many there are:

SELECT * 
    FROM sys.dm_os_memory_clerks;


These can be roughly broken down into four categories:


General ("Default") - These are the ones that start with "MEMORYCLERK_" (buffer pool is tracked in here so that's where your "buffer pages" query is looking)
CACHESTORE - "CACHESTORE_" objects. This is where query plans are monitored and stored.
USERSTORE - "USERSTORE_" objects. Database metadata and permissions are monitored and stored.
OBJECTSTORE - "OBJECTSTORE_" objects. Internal operations are handled here.


How SQL Server coordinates requests for memory is through what's called a Memory Broker. 

SELECT * 
    FROM sys.dm_os_memory_brokers;


These brokers are in charge of managing the memory. The CACHE broker will be the first to release memory to system when pressure is encountered. What this means is that your query plans will be evicted from memory before your data pages are flushed from the buffer pool. (Most of the above taken from my notes from Bob Ward's memory presentation at PASS Summit 2013). 

This is just a brief overview. There's more to it than just this, but the reality is that there are so many things going on in your system that it's not quite correct to assume that SQL Server isn't handling memory properly just because the size-on-disk value of your database isn't less-than-or-equal-to the value of buffer pages*8/1024.

Here's another query that lists buffered objects, just for fun.

        SELECT 

            object_sysname = OBJECT_NAME(p.[object_id]),
            index_id = p.index_id, 
            buffer_size_mb = COUNT(*)/128.,
            buffer_count = COUNT(*),
            compression_type = p.data_compression_desc,
            allocation_type_desc = a.type_desc, 
            rows = p.[rows]
        FROM sys.allocation_units AS a 
            INNER JOIN sys.dm_os_buffer_descriptors AS b 
                ON a.allocation_unit_id = b.allocation_unit_id
            INNER JOIN sys.partitions AS p 
                ON a.container_id = p.partition_id
        WHERE b.database_id = CONVERT(int,DB_ID())
            AND p.[object_id] &gt; 100
        GROUP BY p.[object_id], p.index_id, 
            p.data_compression_desc, 
            a.type_desc, p.[rows]
        ORDER BY buffer_count DESC  


(A modified version of Glenn Berry's DMV query).

To see what you're allowing SQL Server to use for the buffer pool (including all page allocations) and CLR allocations, you can run this query:

SELECT * 
FROM sys.configurations
WHERE name = N'max server memory (MB)';


OR

sp_configure N'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure N'max server memory (MB)';
GO


A config_value of this (2147483647) means that you're allowing SQL Server to use everything it can get. This may lead to resource starvation on the OS which will, in turn, start taking memory back and swapping to the page file - a nasty scenario. Glenn Berry (again) has a good recommendation for choosing an appropriate setting for max server memory (though it's for up to 2008 and I've not found good guidance for >= 2012).

Additionally, since you've noted that you're testing in SQL Server 2014, there is also the possibility of extending the buffer pool to disk (only if you have SSD storage attached).