CODEWITHSUNDEEP
sql-servermemorymemory-leaks
Arif YILMAZ
Arif YILMAZ

Reputation: 5876

sql server eats up memory but doesnt let free after it is done

In Sql Server Management Studio, I run a complex SELECT statement which takes 10 minutes to run. It consumes 10gb memory in our server. The problem is when the SELECT statement is finished, it doesnt release the memory. We can set the MAX SERVER MEMORY to a certain memory, but what we want is that it can consume all the memory available in the server, when it is done with the process, it should release the memory.

I believe it is a memory leak but I couldnt believe there is such a problem in SQL SERVER. Is there an explanation for that?

Upvotes: 1

Views: 163

Answers (3)

user_0
user_0

Reputation: 3363

The only way to limit this is set max memory. It is not a memory leak. It is the way SQL Server works. It use memory for cache.

It is not so wrong if you think that often SQL Server runs on a dedicated server.

I have not tried, but problably not even DBCC FREEPROCCACHE will free your memory (and it is dangerous).

Upvotes: 1

davek
davek

Reputation: 22925

When you query SQL Server, it reads information from the data pages into the buffer cache so that subsequent reads can go direclty to memory and avoid the disk read: that's the design. To find out what is in the buffer cache and to confirm that that is the cause of what you are seeing you can issue:

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind 
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

(taken from this answer: https://dba.stackexchange.com/a/43574/30216)

Upvotes: 2

Tristan
Tristan

Reputation: 1024

SQL server tends to hog all the memory available up to the configured max memory and only then releases parts of that to itself. The only way to "deal with this" that i've found is to set the max memory to the maximum amount I want the engine to use.

Upvotes: 1

Related Questions