Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
You really shouldn't cross-post questions, if you feel that this is a better fit on DBA, then flag your question on Stack Overflow for moderator attention to move. –  bluefeet Dec 16 '13 at 21:51
    
Okay. That is what they suggested I do. I flagged the question on that end so perhaps a moderator will fix it. –  Steven Dec 16 '13 at 22:00
1  
what do you mean, "[you] wiped the database"? –  swasheck Dec 16 '13 at 22:10
3  
Do you think SQL Server only uses memory for holding the data, and nothing else? Ever? What do things like sys.dm_os_memory_clerks and sys.dm_os_buffer_descriptors tell you? –  Aaron Bertrand Dec 16 '13 at 22:11
2  
(And SQL Server 2014 CTP2 is pretty much feature complete (it's the last CTP you'll see), so if all you're doing right now is testing, it should give a very good idea of production performance by the time you get there. If anything, there will be more optimizations by RTM, so if you are happy with performance of CTP2, the release is going to be as good or better.) –  Aaron Bertrand Dec 16 '13 at 22:15
add comment

3 Answers

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] > 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).

share|improve this answer
add comment

When you run queries SQL needs to cache the data in memory. That's the 9.3 Gigs or whatever. But as you are running your query, if the query needs it SQL will be creating hash tables in memory to help with the join, or to create any calculations that you are doing, etc. That all happens in memory.

SQL Server is very good at figuring out how much memory to use. It doesn't just eat up memory for no reason. If it's using the memory it's using it for a reason.

share|improve this answer
add comment

As a newly minted DBA, you should understand that, as a matter of policy, SQL server will consume all the memory in the system to the exclusion of all other programs. If you do not want that behavior, you need to set the MAXMEM setting for the instance. SQL server is not just an app, it has low level functionality that overlaps with things you would ordinarily expect the operating system to handle. That's not a problem, it's intended behavior.

If you are on 2012, look into "xVelocity memory optimized ColumnStore index". It's a way to get a taste of columnar performance without re-architecting your schema. You could also look into the SSAS Multidimensional and Tabular/PowerPivot/"xVelocity in-memory analytics engine" products,but those would be a significant effort. If you're really forward thinking read up Hekaton.

I'd also suggest you start reading Brent Ozar's Accidental DBA 6 month training plan.

share|improve this answer
    
"SQL server is not just an app, it has low level functionality that overlaps with things you would ordinarily expect the operating system to handle". Nope. SQL Server requests memory from the OS and returns memory to the OS just like any other application. Just in larger quantities. It doesn't do anything special. ColumnStore indexes are read only in SQL 2012 so they are useless for 99% of OLTP applications. –  mrdenny Dec 24 '13 at 9:22
    
OP said his intent was in-memory analytics, not OLTP. –  Jeff Sacksteder Dec 29 '13 at 22:28
    
As to the other point, I do not mean that SQL server has access to some special API. I mean that in anything more than a low-end environment, the expectation is that you will be managing memory and storage in a much more aggressive and hands-on way than a typical app would. It will be up to the DBA to determine what is best, not the operating system. This is not the case with something like Exchange Server. OP had an expectation of SQL server behavior different than what was observer. –  Jeff Sacksteder Dec 29 '13 at 22:51
    
If your server is setup correctly then you shouldn't need to manage the memory configuration post installation. The DBA tells the SQL Server how much memory to use. SQL Server allocates that based on pre-set algorithms to cut that money into buffer pool, plan cache, ColumnStore Engine, etc. SQL Server is just an app that allocates memory from the OS. It functions and responds just like any other application such as exchange does. –  mrdenny Jan 1 at 3:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.