November 27, 2013 at 2:15 pm
Hi,
Checking if anybody has a tsql script to display top 10 high MEMORY consuming queries?
Thank you.
November 27, 2013 at 11:24 pm
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
November 28, 2013 at 12:41 am
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.
November 28, 2013 at 12:52 am
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
November 28, 2013 at 1:32 am
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.
November 28, 2013 at 2:00 am
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
November 28, 2013 at 2:46 am
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.
November 28, 2013 at 3:22 am
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
November 28, 2013 at 7:34 am
Thanks Gail, for guiding me in the right direction. Thank you.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply