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.