Jaa

SQL Server Memory per database

  • Artikkeli

Recently we experienced some issues with not having enough memory in some of our lab machines where we were running tests in a Single Box.

This configuration has many components on it like SQL Server engine, Analysis Services, Reporting Services and SharePoint and we identified that SQL Server engine was the component using more memory (a quick check in task manager show that was the process using more memory), as we only use SQL Server for hosting the SharePoint Catalog and the Reporting Services catalog we were surprised that the engine was the one actually using most part of the memory, we were expecting that Analysis Services would use most part of the memory as is being used in tabular mode as it does use the xVelocity In-Memory Analytics Engine (vertipaq) which as it names indicates uses memory for the analytics

I took one of the machines an poke around, the first thing I did was to run a query to figure out how much memory was used by database

SELECT count(*)AS cached_pages_count,(count(*) * 8) as KB
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;

The important dmv here is sys.dm_os_buffer_descriptors which returns the information about the data pages in the SQL Server buffer pool , the buffer pool is the place where SQL Server cache any information read from disk, so when you do a query to a database SQL Server reads the data from disk (the data is stored in a structure called page of 8KB each one) and move it to memory , after that it does all the needed processing and return it to you.

So I saw that one of the databases used to populate one of our In-memory models was using most part of the memory, what was happening here was the database was read completely to create the AS model, and as xVelocity has compression algorithms the memory used by AS was smaller meanwhile the data from SQL was not compressed at all.

The other important thing here is that once some portion of memory is allocated by SQL Server it won’t release it until it receives a memory pressure from the Operating System, so for our case from the moment SQL received the notification our test already failed, you can monitor those notifications using the dmv sys.dm_os_ring_buffers with the ring_buffer_type 'RING_BUFFER_RESOURCE_MONITOR'  , detailed information about it is available in How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

As this data is static and we already have multiple components racing for memory in the box , we decided to move the relational database to an external and dedicated only to SQL reference Server where we actually take advantage of the caching of the relational database from multiple consumers.

This is one of the cases were my previous experience with SQL Server troubleshooting becomes useful.

Comments

  • Anonymous
    September 10, 2012
    `Nice blog post, keep them coming