DMV query which pulls memory usage per session

• vsamantha35

  SSChampion

  Points: 11030

  September 24, 2019 at 8:03 am

  #3682869

  Hi All,

  Does anyone has a query which tells me how much memory is being consumed by each session along with associated query text. Looking for something which tells me , this particular session_id/spid is using 'x' number of data pages and it is occupying 'y-MB' in the buffer pool. If anyone has similar query, please share it. we are using sql 2012 and above sql instances.

  Can we do this using sp_whoisactive?

  Thanks,

  Sam

• DinoRS

  SSCrazy

  Points: 2513

  September 24, 2019 at 8:44 am

  #3682874

  Yes, google has, 4th result when searching mssql memory usage by session

• vsamantha35

  SSChampion

  Points: 11030

  September 24, 2019 at 11:05 am

  #3682896

  For some reason , those queries doesn't give me what is expected. it doesn't show up the spid as well.

  Basically, if I try to open a new ssms session and execute below 2 queries, and If I query the DMVs , I should get , this particular spid, executed this batch and took 50MB. That's what I was expecting... Can we get such detail info?

  create table emp

  (c1 int identity,

  c2 char (8000) default 'a' --- inserting 8k page

  );

  -- insert 50 MB data

  insert into emp default values

  go 6400 --50MB