MSSQL - SQL Server Memory Issue Debug

SELECT @@VERSION, SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS     

DBCC MEMORYSTATUS                -- check memory utilization of each instance

DBCC SQLPERF(LOGSPACE)           -- check file size of each DB

/* quick way to make the result of MEMORYSTATUS more useful

CREATE TABLE #tmp (row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT);

INSERT INTO #tmp EXEC ( 'DBCC MEMORYSTATUS' );

SELECT top 1 value / 1024 'KBUsed' FROM #tmp WHERE RTRIM(LTRIM(name)) = 'Working Set';

DROP TABLE #tmp;

*/

sp_who2

-- SQL memory utilization for processes

SELECT spid,status,cmd,cpu,physical_io,blocked,dbid,convert(sysname, rtrim(loginame)) as loginname,sid,hostname,program_name,spid as 'spid_sort', substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch, DB_NAME(dbid) DB

from master.dbo.sysprocesses (nolock) order by loginname

/* performance monitor counters

Performance object: Process

Counter: Private Bytes

Instance: sqlservr

Performance object: Process

Counter: Working Set

Instance: sqlservr

*/