SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find out top memory consuming queries


Find out top memory consuming queries

Author
Message
Oracle_91
Oracle_91
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1160 Visits: 1742
Hi,

Checking if anybody has a tsql script to display top 10 high MEMORY consuming queries?

Thank you.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86236 Visits: 45231
You mean queries with high workspace memory grants?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Oracle_91
Oracle_91
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1160 Visits: 1742
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.
http://social.msdn.microsoft.com/Forums/en-US/f73a8e6b-a51a-4171-a637-b3dee891a0a1/how-to-find-which-dbquery-is-consuming-most-memory-in-sql-2005

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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86236 Visits: 45231
That memory consumption isn't from the query, it's from the buffer pool, memory in the buffer pool (used to cache data) isn't associated with any particular query because it's used by all of them.

It's not specifically that the queries are consuming memory, it's that the queries are reading huge amounts of data which means SQL needs to increase the size of the buffer pool to cache that data. Only the first query that needs the data will cause the reads, later ones will read from memory.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Oracle_91
Oracle_91
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1160 Visits: 1742
Thanks Gail.

One more thing. Can I assume that queries which are doing more physical reads from disk are memory hogers? Is that a correct statement.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86236 Visits: 45231
Not necessarily because those pages read may be tossed out of cache fast. You can assume that a query with high physical reads is reading lots off the disk and may potentially be displacing lots of pages from cache.

Bear in mind though, that SQL intentionally and by design will grow its memory to the max it's allowed. It's called 'buffer pool rampup', so don't go looking for queries that are behaving correctly assuming that they are a problem. If you set max server memory to default, you're telling SQL Server that it can and should use all the memory on the server, so when it does use all the memory on the server, it's not a problem, it's doing what you told it that it could do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Oracle_91
Oracle_91
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1160 Visits: 1742
Thanks Gila for Awesome explanation.

Now, I am in a point where I was able to get insight of how sql server memory used by all queries but I feel my original question which I posted is somewhat wrong and I am convinced with what you say and always best practice is to SET max server memory to a value where sql server can play nicely.

Coming, 1 step back. Assuming , I have set the max server memory to 4GB i.e. bpool on my 8GB machine, still if I see external memory pressure. May be mtl or non-buffer pool or any other anti virus scans or any memory hog app running running on the machine might cause memory issues. How would I go further for troubleshooting External memory pressure.

Any links/resources that can shared will be great help.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86236 Visits: 45231
External memory pressure, ie something other than SQL consuming memory? Task manager to start, perfmon with the process object later if necessary.

MemToLeave is a 32-bit concept only.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Oracle_91
Oracle_91
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1160 Visits: 1742
Thanks Gail, for guiding me in the right direction. Thank you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search