Selecting a View consumes 4GB memory but executing SQL statement of View only 70 MB I am facing high memory consumption on my database server. During diagnosis, I found a view consuming 4 GB memory. But when I executed it's statement (select statement of view) it's taking only 70 MB.

What can be the reason?

You can see screenshot here



Here is Estimated execution plan of the view.Execution Plan
and here is actual one Actual Eplan

I am using SQL Server 2008 R2 E2. And I am using this query to know memory utilization and other things.

    select
                db_name(sp.dbid) [DB Name],sp.spid,sp.blocked,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
                ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
                ,es.status,es.cpu_time,qmg.requested_memory_kb,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
                ,es.reads,es.writes,es.logical_reads

    from
                    sys.dm_exec_requests er
    inner join      master.dbo.sysprocesses sp
    on              er.session_id=sp.spid
    inner join      sys.dm_exec_connections ec
    on              er.session_id=ec.session_id
    inner join      sys.dm_exec_sessions es
    on              ec.session_id=es.session_id
    inner join      sys.dm_exec_query_memory_grants qmg
    on er.session_id=qmg.session_id
    cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
    cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp
    Order by cpu_time desc


"Select * from View" consumes 4GB memory but when I execute SQL statement of view. It takes 70 MB only. I am measuring requested memory and granted memory. Using above script.
The 'actual' execution plan explains the issue - the estimated number of rows coming into the SORT is 5.3 million, so Sql Server allocates a large block of memory to hold it.  However, the 'actual' number of rows is much less.



A sort operation needs to spool all the data and it estimates that there is 3.7GB to spool. Additionally the amount of memory needed to actually sort is more than the actual size of data itself.