I have a SQL Server 2008 R2 Standard (x64 bit). It has 64GB of memory, but memoryclerk_sqloptimizer consumes about 36 GB. How should I clear it and identify what cause the growth of memoryclerk_sqloptimizer. I also have high stolen pages too. Thanks in advance.

Hi,

Sqloptimizer is place where SQL Server optimizer prepares plans for queries the basic cause behind the huge memory consumption for memoryclerk_sqloptimizer is poor query going for which optimizer has to prepare costly plans. Other factor can be lot of ad hoc queries and lack of parametrization.

Below query will give you SQL query using most memory and query plan as well. Have a look

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
go

Have a look at This link

IMO this points to fact that SQL Server queries needs tuning. Proper index without fragmentation, updated stats would be to start with.

Can you upload DBCC MEMORYSTATUS output on skydrive. Stolen pages is pages taken from buffer pool for miscellaneous purpose I don't think it has direct relation to Sqloptimizer

PS: Is it the Single page allocator taking more memory or Multipage allocator

what is output of below

select SUM(single_pages_kb)*1.0/1024 as total_single_pages_MB,
SUM(multi_pages_kb)*1.0/1024 as total_multi_pages_MB
from sys.dm_os_memory_clerks where type='MEMORYCLERK_SQLOPTIMIZER'

Is your SQL server patched to SP2 ?

No, it is not patched to SP2, it is RTM. I just restarted my sql server, but I got the following result couple days ago.

type	Single Pages(KB)	Multi Pages(KB)
MEMORYCLERK_SQLOPTIMIZER	30949272	992

Here is the link for the result of DBCC MemoryStatus

https://onedrive.live.com/?cid=7155BDD78D321650&id=7155BDD78D321650%21145

Thanks!

• Edited by Grace09 Thursday, June 26, 2014 11:02 PM

Please patch to latest level... A high percentage of stolen pages relative to target is an indicator of the internal memory pressure.

Have you checked what is running on the instance ?

Try downloading this proceude and delploy it in DBA maintanance DB : sp_whoisactive,  from this link : http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

This will gives you the complete details what is running and what is the resrouce detials, memory CPU etc..

And check these below link too...

http://social.technet.microsoft.com/Forums/sqlserver/en-US/5cff7258-9995-434e-888b-791bc743edca/why-does-memoryclerksqloptimizer-take-lot-of-memory-and-how-to-clear-it?forum=sqldatabaseengine

http://social.technet.microsoft.com/Forums/sqlserver/en-US/995c22d7-3470-47e2-819d-b993dc94fbbf/high-memory-utilization-sql-server-2008-r2?forum=sqlkjmanageability

No, it is not patched to SP2, it is RTM. I just restarted my sql server, but I got the following result couple days ago.

type	Single Pages(KB)	Multi Pages(KB)
MEMORYCLERK_SQLOPTIMIZER	30949272	992

Here is the link for the result of DBCC MemoryStatus

https://onedrive.live.com/?cid=7155BDD78D321650&id=7155BDD78D321650%21145

Thanks!

Hi,

Apart from bad queries executing you also may have memory pressure because of following

Page Life Expectancy   552 ( which is very less)

Process physical memory low 1( which mans SQL server is facing memory crunch)

Page Faults                              1435771612( very high)

I used word 'may be'  because this could also be caused by poor queries running on your system utilizing all memory, leaving other processes devoid of memory and ultimately leading to poor behavior.

At first go 64 G memory seems Ok to me  but how big is your system ? how big is largest database ? what is total size of database how many databases you have ? I would first find out costly queries and tune them if still issue is not solved I would add more ram in increase SQL Server buffer pool.

PS: Please apply SP2 ASAP RTM is unsupported, so you are basically running unsupported version

We are scheduled to apply SP2 soon. It is shared server, there are 55 databases, the database size ranges from 4 MB to 330 GB. How do I find out the queries that consume most memory? Thanks.

• Edited by Grace09 Friday, June 27, 2014 1:37 AM

Try downloading this proceude and delploy it in DBA maintanance DB : sp_whoisactive,  from this link : http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

This will gives you complete details and helpful to find the queries and more details

We are scheduled to apply SP2 soon. It is shared server, there are 55 databases, the database size ranges from 4 MB to 330 GB. How do I find out the queries that consume most memory? Thanks.

Hi,

Did you ran the first query posted by me in first reply it seems you are not reading my post clearly. The output would have text column which will include query text. That will help you in getting query taking more memory.Whats is total cumulative size of all databases ? Do you use adhoc queries ?

Like I said please keep options open for increasing more memory.

I ran the following query ,

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
go

Only one result returned. which is the query itself.

My tempdb grow to 150GB a few days after the sql server reboot. Here is the link of new dbcc memorystatus result

https://onedrive.live.com/?cid=7155BDD78D321650&id=7155BDD78D321650!146

Thanks.

I ran the following query ,

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
go

Only one result returned. which is the query itself.

My tempdb grow to 150GB a few days after the sql server reboot. Here is the link of new dbcc memorystatus result

https://onedrive.live.com/?cid=7155BDD78D321650&id=7155BDD78D321650!146

Thanks.

I will have a look at MEMORYSTATUS  but did you ran the query after restart or before SQL server restart. I giess you ran it after restart. Do you use lots of sort operations SQLOPTIMIZER using high memory with tempdb getting filled points to fact that your query uses lost of sorts and hash joins.

Did you found out reason why tempdb grew to 150 G. From all these it seems you need to tune your query specially that uses sorts and temp tables

I just ran the query. it is after the restart.

I ran the following query against the tempdb

SELECT
SUM (user_object_reserved_page_count)*8 as user_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

Here is the result

user_obj_kb    internal_obj_kb    version_store_kb    freespace_kb    mixedextent_kb
2304                858688                   7232                      147834752       152256

• Edited by Grace09 Monday, June 30, 2014 2:50 PM

I just ran the query. it is after the restart.

I ran the following query against the tempdb

SELECT
SUM (user_object_reserved_page_count)*8 as user_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

Here is the result

user_obj_kb    internal_obj_kb    version_store_kb    freespace_kb    mixedextent_kb
2304                858688                   7232                      147834752       152256

Thats the wrong thing you did restart flused the cache and information about queries taking more memory in SQLOPTIMZER is gone. So we are back to square one. Please wait for a couple of days may be week. Run memorystatus to see whether sql optimizer memory consuption is increasing or not when you find out it has taken enough ( like it did before) run the query.

Again i would like you to speak with your develpoer about queries which caused Tempdb to grow( i guess it was not normal for your surrounding) I have feeling that quries having too much sort and hash joins are running which is taking memory as well as same time causing tempdb issue.

Please refer to section monitoring and troubleshooting in below MS link to look for queries filling tempdb

http://technet.microsoft.com/en-gb/library/cc966545.aspx#EDAA

Thank you. I will look at my tempdb and see why it fill up so quickly.

I set the max server memory to 51 GB, how come the memoryClerk_SQLBufferPool has 54 committed and Task Manager shows 58.2 GB of memory is used? Thanks.

It is shared server, there are 55 databases, the database size ranges from 4 MB to 330 GB.

Well, if the db's average 150gb that's about 8tb you're trying to squeeze into 50gb of RAM, and multiple databases have worse locality than a single large database, so I'm not surprised at these numbers.  I'm also not surprised at a 150gb tempdb.

So, do you have a performance problem or not?  The response times your users get, average and max, will have to answer that question.  One thing for sure, you are going to be making the hardware work hard!

Josh

Yes, some users mention about the slowness.

Thank you. I will look at my tempdb and see why it fill up so quickly.

I set the max server memory to 51 GB, how come the memoryClerk_SQLBufferPool has 54 committed and Task Manager shows 58.2 GB of memory is used? Thanks.

First please dont use tak manager to See sql server memory consumption. It will not show correct value if SQL Server has locked pages in memory privileg instead always use below query to check SQL Server memory consuption

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

memoryClerk_SQLBufferPool has 54 committed because it includes both single page as well as multipage memory allocated. Multipage is memory for large pages.

Also please note SQL server can consume memory beyond its buffer pool restriction. Max server memory only restricts buffer pool whcih caters to memory requirement for <= 8 KB pages andy requirement gretaer than this would be done by win API and would count under SQL Server memory allocation.

More about SQL server memory here

http://social.technet.microsoft.com/wiki/contents/articles/22316.sql-server-memory-and-troubleshooting.aspx

PS: Also consider providing more memory to your SQL server but after you have found out rouge queries and tuned it

Hi Shanky,

The memoryclerk_sqloptimizer grows to 8GB now. I ran the very first query you gave, here is the result,

https://onedrive.live.com/edit.aspx/Public/Documents/query.xlsx?cid=7155bdd78d321650&id=documents&app=Excel

Thanks.

The memoryclerk_sqloptimizer grows to 8GB now. I ran the very first query you gave, here is the result,

https://onedrive.live.com/edit.aspx/Public/Documents/query.xlsx?cid=7155bdd78d321650&id=documents&app=Excel

Query optimizer taking 8 G is not *really* a issue IMO. Its normal, 36 G was an issue but since you restarted we cannot find out which was query taking more memory in terms of memory grants. Query you uploaded seems pretty fin to me. Lets have a look for couple of more days and see its growth if it stays around this value its normal for optimizer to take 8 G on complex environments.

Thank you, Shanky. I will wait for a little big longer then.

Hi Shanky,

The Query optimizer has grown to 24 GB. I sometimes run the following query, it mostly return the query itself.  How should I identify which query in the cache use the most memory? Thanks in advance.

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Hi,

Can you please see output of below query. it might take few seconds to give output

select top 20
(ce.pages_allocated_count)*8 as Memory_Utilized_in_KB,
ce.type,
cp.cacheobjtype,
cp.size_in_bytes as [No of Bytes consumed by Cache Obj],
qp.query_plan as [Query Plan],
st.text as [SQL Server Query]
from 
sys.dm_exec_cached_plans cp
cross apply  sys.dm_exec_query_plan(cp.plan_handle) AS qp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
join
sys.dm_os_memory_cache_entries ce
ON cp.memory_object_address = ce.memory_object_address 
where cp.objtype in('proc','prepared','adhoc','Trigger','View')
order by Memory_Utilized_in_KB desc

If possible paste the output.

Here is the result,

https://onedrive.live.com/?cid=7155BDD78D321650&id=7155BDD78D321650!159

Thanks!

Here is the result,

https://onedrive.live.com/?cid=7155BDD78D321650&id=7155BDD78D321650!159

Thanks!

Can you post result properly I cannot understand it in text file. Paste queries separately and screenshot of output separately.

Also please show me how you find out optimizer using 24 G.

Sorry about that. I have output in the excel file, query plan and sql server query are in separate tabs)

https://onedrive.live.com/edit.aspx?cid=7155BDD78D321650&resid=7155BDD78D321650!162&app=Excel

The screenshot is uploaded is

https://onedrive.live.com/?cid=7155BDD78D321650&id=7155BDD78D321650!164&v=3

I used this query to get the query optimization usage,

select SUM(single_pages_kb)*1.0/1024 as total_single_pages_MB,
SUM(multi_pages_kb)*1.0/1024 as total_multi_pages_MB
from sys.dm_os_memory_clerks where type='MEMORYCLERK_SQLOPTIMIZER'

Thanks!!

• Edited by Grace09 Friday, August 01, 2014 3:29 PM

Hi Shanky,

Are you able to see the result? Please let me know if I save the result in a wrong format which make you hard to see. Thanks!

Hi Shanky,

Are you able to see the result? Please let me know if I save the result in a wrong format which make you hard to see. Thanks!

Apologies Grace I was busy. Query was readbale but unfortunately i could not find any good information. Can you please mail me output of dbcc memorystatus to mail id mentioned in my profile I would like to take this off from here we can discuss it on mail. Please run all queries again and post output once again.

Thank you

My SQL Server has a weired problem. It seems it happens with the same matter. It always restarts after running a period of time(about one month long). And I used 'dbcc memorystatus' to check the problem. I found MEMORYCLERK_SQLOPTIMIZER singlepage allocator ocuuping a lot of memory. I guess it could be why my SQL server restarted.

So I used sp_WhoIsActive to find which query script holds memory. After I collect some data, I don't know how to distinguish the relationship between sp_WhoIsActive  result and  'dbcc memorystatus'. Could you help me and give me some hints to solve the problem. The following is some information about my SQL Server. Server: CPU: intel E5645*2, RAM 64GB, HD: 10krpm SAS SQL Server: 2008r2 with failover cluster feature enabled, max memory:52GB, instance:1, No of db: 1700(Don't be surprised. There are 1700 dbs in my SQL Server.) The attach file can be downloaded with the url. www.dropbox.com/s/96k4x4ghenw26b4/att.zip?dl=0
After unzipping, there are 3 files. att1.txt  The status before server is going to restart. att2.txt  The status that the server has run for two weeks after restarting. whoisactive.bak  The result for sp_WhoIsActive. You can restore it in the SQL server2012.