Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find out top memory consuming queries Expand / Collapse
Author
Message
Posted Wednesday, November 27, 2013 2:15 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
Hi,

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

Thank you.
Post #1518213
Posted Wednesday, November 27, 2013 11:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
You mean queries with high workspace memory grants?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1518261
Posted Thursday, November 28, 2013 12:41 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
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.


Post #1518278
Posted Thursday, November 28, 2013 12:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1518281
Posted Thursday, November 28, 2013 1:32 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
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.
Post #1518286
Posted Thursday, November 28, 2013 2:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1518287
Posted Thursday, November 28, 2013 2:46 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
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.
Post #1518299
Posted Thursday, November 28, 2013 3:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1518302
Posted Thursday, November 28, 2013 7:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
Thanks Gail, for guiding me in the right direction. Thank you.
Post #1518362
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse