Okay, this is what I am doing Gail. This is a TEST to identify queries which are consuming more memory and seeking for help to get those queries which are consuming or bloating up the memory.
Step1: I have created a table where each row is 8K size and it contains 1132761 rows in it.
Step2: stopped sql server. this is my TEST machine
Step3: started sql
Step4: open a new query window in ssms and started executing
use testdb
go
SELECT * FROM t1; --- 1132761 rows
go
Step5: open another query window and executed below query
use testdb
go
SELECT * INTO t2 FROM t1
go
Once I started above 2 sessions, sql server started consuming memory like crazy. I can clearly see the memory utilization growing and growing in Task mgr performance tab almost eating up all of the memory. I haven't set my max server purposefully. I wanted to catch these user_process = 1 queries which are consuming my memory.
Environment details
-------------------------
SQL Server 2008 sp3 DEVELOPER Edition x64
Windows 7 SP1 x64
8GB Physical Memory(RAM)
End Goal
------------
I want to see the spids of the 2 select statements which are eating up my memory, is_user_process =1, sql text, xml plan, memory consumption by those queries in my output, which I am not able to :-(. I don't know whether I am going in the right direction to catch these high consuming queries or there is any indirect way of fetching these queries causing high memory consumption.
--my repro statements
use master
go
create database testdb
go
use testdb
go
create table t1
(c1 int identity,
c2 char (8000)
);
go
insert into t1(c2) values(REPLICATE ('f', 8000))
go 1000; --- I have done this multiple times and inserted total 1132761 rows
-- My research.
I have to below queries but it is giving wrong memory size, i.e. it is giving me , probably if i am not wrong , the size of the query or query plan in memory but not the memory it has consumed pulling all the data from disk to memory which caused high memory consumption on my box.
test-1
--------
SELECT
a.session_id,
b.kpid,
b.blocked,
db_name(b.dbid) dbname,
b.open_tran,
b.status,
b.cmd,
a.memory_usage * 8192 / 1024 as [Memory Use (KB)] ,
LEFT([sql].[text], 1000) as [text]
FROM sys.dm_exec_sessions a with (nolock)
INNER JOIN sys.sysprocesses b with (nolock) on a.session_id = b.spid
OUTER APPLY sys.dm_exec_sql_text (b.sql_handle) sql
WHERE a.is_user_process = 1
and a.session_id <> @@SPID
test-2
-------
Next, I came across this forum thread. But this is giving me the plan size.
select TOP 100
objtype,
usecounts,
p.size_in_bytes/1024 'IN KB',
LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY 3 DESC
test-3
-------
Looked at sys.dm_exec_query_stats but wasnt able to get the required output. Moreover, In book online, it states that it holds info of currently executing queries.
From BOL :
------------
Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
Finally i tried , test-4
---------------------------
select * from sys.dm_exec_query_memory_grants;
I kept my sql statement running in one window and open a new windows and ran the above query, It didn't work for me. No rows selected.
BOL says sys.dm_exec_query_memory_grants;
-Queries that do not have to wait on a memory grant will not appear in this view.
That's where I am stuck now.
My end goal is to display all the user_process = 1 queries along with memory consumption in mb's, sqltext, sqlplan.
Please help !
Thank you.