Answered by:
memoryclerk consumes a lot of memory

-
Hi guys,
Could you explain me why memoryclerk_sqloptimizer consumes a lot of memory and how to solve it (release memory)?
Please have a look reports below.
1. Before
2. After 5 days
So we can see that sqloptimizer became to consume about 1.9Gb instead of 1Gb initially (in 1 day after server restart this value was just about 700Mb).
After some days this behavior will lead to server to be almost unavailable for application.
Please suggest what can be a cause and how I can change this situation.
Max memory 8Gb (server 32Gb).
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
- Edited by AleshaDBA Wednesday, August 22, 2012 9:02 AM
Question
Answers
-
Try the below:
/********************************************************** * top procedures memory consumption per execution * (this will show mostly reports & jobs) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY IO_Per_Execution DESC /********************************************************** * top procedures memory consumption total * (this will show more operational procedures) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY Total_IO_Reads DESC /********************************************************** * top adhoc queries memory consumption total ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,QueryText = qt.text ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY Total_IO_Reads DESC /********************************************************** * top adhoc queries memory consumption per execution ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,QueryText = qt.text ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
- Marked as answer by amber zhangModerator Thursday, August 30, 2012 6:49 AM
All replies
-
-
-
After some days this behavior will lead to server to be almost unavailable for application.
Memeory management is highly dynamic in SQL Server. BTB, Is there lots of adhoc queries running in your server?Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
-
Hi,
Please see:http://sqlmonitormetrics.red-gate.com/sql-server-memory-manager-optimizer-memory-kb/
Do you have a lot of ad-hoc queries? As you can see from Brad's post, it may not be a simple thing to fix. You may also consider turning on 'optimize for ad hoc workloads' using sp_configure.
I presume that there's other applications running on the server if you've capped it 8Gb out of 32GB?
Thanks, Andrew -
So, please have a look info below.
The data from sys.dm_exec_cached_plansCan it help?
Yep. This server has several instances, but the problem occurs only on a one.
However I think that these cached plans are kept in cachestore_cp, cachestore_obj etc. What relations exist between adhoc and memoryclerk_sqloptimizer?
I've should note for last several hours memoryclerk_sqloptimizer wasted additional 200Mb of memory
- Edited by AleshaDBA Wednesday, August 22, 2012 11:30 AM
-
It does not look like you have many adhoc queries. When did you restart the server/service last time?
SQL server uses memory for optimizing the queries. This is apart the procedure cache and data cache. memoryclerl_sqloptimizer will not have any cached plans for the queries or procs, but the memory used for its optimization.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
-
-
-
Please look at this.
Memory Manager KB ---------------------------------------- ----------- VM Reserved 33739256 VM Committed 8750764 Locked Pages Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0 (5 row(s) affected) Memory node Id = 0 KB ---------------------------------------- ----------- VM Reserved 9536 VM Committed 9248 Locked Pages Allocated 0 MultiPage Allocator 7664 SinglePage Allocator 4894536 (5 row(s) affected) Memory node Id = 1 KB ---------------------------------------- ----------- VM Reserved 33726072 VM Committed 8737976 Locked Pages Allocated 0 MultiPage Allocator 62488 SinglePage Allocator 4894536 (5 row(s) affected) Memory node Id = 64 KB ---------------------------------------- ----------- VM Reserved 2560 VM Committed 2504 Locked Pages Allocated 0 MultiPage Allocator 2416 SinglePage Allocator 4894536 (5 row(s) affected) MEMORYCLERK_SQLGENERAL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1864 MultiPage Allocator 8152 (7 row(s) affected) MEMORYCLERK_SQLGENERAL (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 120 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLGENERAL (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1984 MultiPage Allocator 8152 (7 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 33603584 VM Committed 8623744 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 416 (7 row(s) affected) MEMORYCLERK_SQLQUERYEXEC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 416 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 2091656 MultiPage Allocator 912 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 17816 MultiPage Allocator 32 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 2109472 MultiPage Allocator 944 (7 row(s) affected) MEMORYCLERK_SQLUTILITIES (node 0) KB ---------------------------------------- ----------- VM Reserved 120 VM Committed 120 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 88 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLUTILITIES (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLUTILITIES (Total) KB ---------------------------------------- ----------- VM Reserved 120 VM Committed 120 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 104 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSTORENG (node 0) KB ---------------------------------------- ----------- VM Reserved 12416 VM Committed 12416 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 2432 MultiPage Allocator 29792 (7 row(s) affected) MEMORYCLERK_SQLSTORENG (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 576 MultiPage Allocator 3608 (7 row(s) affected) MEMORYCLERK_SQLSTORENG (Total) KB ---------------------------------------- ----------- VM Reserved 12416 VM Committed 12416 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3008 MultiPage Allocator 33400 (7 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 552 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 512 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1064 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLCLR (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 128 MultiPage Allocator 544 (7 row(s) affected) MEMORYCLERK_SQLHTTP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SNI (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 80 MultiPage Allocator 16 (7 row(s) affected) MEMORYCLERK_SNI (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 64 MultiPage Allocator 16 (7 row(s) affected) MEMORYCLERK_SNI (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 16 (7 row(s) affected) MEMORYCLERK_SNI (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 144 MultiPage Allocator 48 (7 row(s) affected) MEMORYCLERK_FULLTEXT (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLXP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_BHF (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 200 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_BHF (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 128 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_BHF (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 328 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLQERESERVATIONS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 4128 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_XE_BUFFER (node 0) KB ---------------------------------------- ----------- VM Reserved 2816 VM Committed 2816 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_XE_BUFFER (node 1) KB ---------------------------------------- ----------- VM Reserved 1408 VM Committed 1408 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_XE_BUFFER (Total) KB ---------------------------------------- ----------- VM Reserved 4224 VM Committed 4224 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_HOST (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SOSNODE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 5920 MultiPage Allocator 12992 (7 row(s) affected) MEMORYCLERK_SOSNODE (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 4936 MultiPage Allocator 3648 (7 row(s) affected) MEMORYCLERK_SOSNODE (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 2336 (7 row(s) affected) MEMORYCLERK_SOSNODE (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 10864 MultiPage Allocator 18976 (7 row(s) affected) MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 192 SM Committed 192 SinglePage Allocator 0 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB ---------------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_XE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3544 MultiPage Allocator 1992 (7 row(s) affected) CACHESTORE_OBJCP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 22992 MultiPage Allocator 1336 (7 row(s) affected) CACHESTORE_SQLCP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 2678624 MultiPage Allocator 5344 (7 row(s) affected) CACHESTORE_PHDR (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 11048 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XPROC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_TEMPTABLES (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_NOTIF (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_VIEWDEFINITIONS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBTYPE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBELEMENT (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBATTRIBUTE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_STACKFRAMES (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 8 (7 row(s) affected) CACHESTORE_STACKFRAMES (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 8 (7 row(s) affected) CACHESTORE_STACKFRAMES (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 8 (7 row(s) affected) CACHESTORE_STACKFRAMES (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 24 (7 row(s) affected) CACHESTORE_BROKERTBLACS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 40 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERKEK (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERDSH (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERRSB (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERREADONLY (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERTO (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_EVENTS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SYSTEMROWSET (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 888 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SYSTEMROWSET (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 360 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SYSTEMROWSET (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1248 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_CONVPRI (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_CONVPRI (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_CONVPRI (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_FULLTEXTSTOPLIST (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 32 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SCHEMAMGR (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 7144 MultiPage Allocator 80 (7 row(s) affected) USERSTORE_DBMETADATA (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1616 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_DBMETADATA (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 96 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_DBMETADATA (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1712 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_TOKENPERM (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8384 MultiPage Allocator 528 (7 row(s) affected) USERSTORE_TOKENPERM (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 240 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_TOKENPERM (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8624 MultiPage Allocator 528 (7 row(s) affected) USERSTORE_OBJPERM (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 440 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_OBJPERM (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 144 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_OBJPERM (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 584 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 160 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 144 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 312 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LBSS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 64 MultiPage Allocator 128 (7 row(s) affected) OBJECTSTORE_LBSS (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 48 MultiPage Allocator 112 (7 row(s) affected) OBJECTSTORE_LBSS (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 112 MultiPage Allocator 240 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 880 MultiPage Allocator 56 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 872 MultiPage Allocator 56 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 56 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (Total) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 1752 MultiPage Allocator 168 (7 row(s) affected) OBJECTSTORE_SERVICE_BROKER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 424 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (node 0) KB ---------------------------------------- ----------- VM Reserved 32768 VM Committed 32768 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 14864 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16072 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (node 64) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 24 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (Total) KB ---------------------------------------- ----------- VM Reserved 32768 VM Committed 32768 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 30960 MultiPage Allocator 0 (7 row(s) affected) Buffer Pool Value ---------------------------------------- ----------- Committed 1048576 Target 1048576 Database 358127 Dirty 5398 In IO 0 Latched 8 Free 80987 Stolen 609462 Reserved 348 Visible 1048576 Stolen Potential 386337 Limiting Factor 17 Last OOM Factor 0 Page Life Expectancy 5529 (14 row(s) affected) Process/System Counts Value ---------------------------------------- -------------------- Available Physical Memory 520519680 Available Virtual Memory 8761175425024 Available Paging File 33099771904 Working Set 9043939328 Percent of Committed Memory in WS 100 Page Faults 385666186 System physical memory high 1 System physical memory low 0 Process physical memory low 0 Process virtual memory low 0 (10 row(s) affected) Procedure Cache Value ---------------------------------------- ----------- TotalProcs 9509 TotalPages 339423 InUsePages 179 (3 row(s) affected) Global Memory Objects Pages ---------------------------------------- ----------- Resource 303 Locks 3870 XDES 112 SETLS 8 SE Dataset Allocators 16 SubpDesc Allocators 8 SE SchemaManager 649 SE Column Metadata Cache 253 SQLCache 20217 Replication 2 ServerGlobal 50 XP Global 2 SortTables 3691 (13 row(s) affected) Query Memory Objects (internal) Value ---------------------------------------- ----------- Grants 0 Waiting 0 Available 344380 Current Max 344380 Future Max 344380 Physical Max 769840 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 (11 row(s) affected) Small Query Memory Objects (internal) Value ---------------------------------------- ----------- Grants 1 Waiting 0 Available 17737 Current Max 18125 Future Max 18125 (5 row(s) affected) Optimization Queue (internal) Value ---------------------------------------- -------------------- Overall Memory 6885367808 Target Memory 3754950656 Last Notification 1 Timeout 6 Early Termination Factor 5 (5 row(s) affected) Small Gateway (internal) Value ---------------------------------------- ----------- Configured Units 28 Available Units 28 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 (6 row(s) affected) Medium Gateway (internal) Value ---------------------------------------- ----------- Configured Units 7 Available Units 7 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 (6 row(s) affected) Big Gateway (internal) Value ---------------------------------------- ----------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 (6 row(s) affected) Memory Pool Manager Pages ---------------------------------------- ----------- Reserved Current 388 Reserved Limit 386715 (2 row(s) affected) Memory Pool (internal) Pages ---------------------------------------- ----------- Allocations 609820 Predicted 801948 Private Target 0 Private Limit 0 Total Target 996147 Total Limit 996147 OOM Count 0 (7 row(s) affected) MEMORYBROKER_FOR_CACHE (internal) Pages ---------------------------------------- ----------- Allocations 345263 Rate 31 Target Allocations 539493 Future Allocations 0 Overall 840499 Last Notification 1 (6 row(s) affected) MEMORYBROKER_FOR_STEAL (internal) Pages ---------------------------------------- ----------- Allocations 264169 Rate -2885 Target Allocations 458368 Future Allocations 0 Overall 840499 Last Notification 1 (6 row(s) affected) MEMORYBROKER_FOR_RESERVE (internal) Pages ---------------------------------------- ----------- Allocations 388 Rate -2365 Target Allocations 386684 Future Allocations 192460 Overall 840499 Last Notification 1 (6 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Regarding errors in log. When server was almost unavailble in error log the following errors were occuring:
The client was unable to reuse a session with SPID 55, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message. Error: 18056, Severity: 20, State: 29.
No other errors were noted before and after a restart server.
-
Another strange thing from Memory status:
Memory node Id = 0 KB ---------------------------------------- ----------- VM Reserved 9536 VM Committed 9248
Memory node Id = 1 KB ---------------------------------------- ----------- VM Reserved 33726072 VM Committed 8737976
MEMORYCLERK_SQLOPTIMIZER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 2091656 MultiPage Allocator 912 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (node 1) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 17816 MultiPage Allocator 32
The memory were allocated for node Id=1! But sqloptimizer uses SinglePage Allocator allocated 2091656Kb for node 0! What does it mean?
- Edited by AleshaDBA Wednesday, August 22, 2012 1:46 PM
-
Interested to look at the below output.
WITH Waits AS (SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER', 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES', 'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK') ) SELECT W1.wait_type AS WaitType, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage HAVING SUM (W2.Percentage) - W1.Percentage < 95;
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
-
-
For NUMA enabled system, many memory clerks will be logically on node 0. You can verify this from other divisions like cachestore memoryclerk, xp servicebrokers etc. Memory related issues are always hard to find the root cause. To me, it looks like you have some application/design issues with. I do not have a stright forward answer here, but trying to find/co-relate the root cause. It looks like you have lots of Linked server/Extended procs running on your server. RESOURCE_SEMAPHORE is the wait type related for your memory issue here.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
-
Ok, but why is memory allocated from node 0 for optimizer, when almost all memory was allocated for node 1. How does it work?
RESOURCE_SEMAPHORE is another symptom of problem.
The developers seggested me restart server once in a week as decision for now. :)
What do you think about the following which I can see on the server when I swtich on performance monitor with counters Logins and Logouts per second.
When just switched on there were just about 2 connections and this situation was stable. But after about 5 minutes I saw the following
Every second more than 40 connections logon and logout from server.
So after server minutes I saw again 0 logouts and logins. How do you think the such behavior can be a cause of problem?
-
(Login and Logout) records the logins to the server. it may be from application/DBAs/Developers/Linked server connections etc...
What are you seeing abnormal here(You look forward no connections to your server?). Probabaly I did not get your question???
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
-
-
Is your logouts/sec match logins/sec?
I suggest you should have a look at profiler and make sure that you are actually utilizing connection pooling. Grab the audit login, logout events as well as RPC:Completed and look for calls to sp_reset_connection - that will indicate at least some pooling is going on. Look at EventSubClass for Audit Login Event Class to verify the connection is pooled or non-pooled.
If you see login and logout events with no sp_reset_connections then you need to look at your application code to nail down the issue.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
- Edited by Latheesh NK Thursday, August 23, 2012 12:08 PM
-
Is your logouts/sec match logins/sec? YES
If you see login and logout events with no sp_reset_connections then you need to look at your application code to nail down the issue.
It looks ok, Audit Login, Audit Logout, RPC:Completed. All three events are subsequential.
- Edited by AleshaDBA Thursday, August 23, 2012 12:18 PM
-
Just to confirm all together did you look at my another question:" Look at EventSubClass for Audit Login Event Class to verify the connection is pooled or non-pooled."?
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
-
-
The data will show that you have a pooling in your application implemented. If it non-pool, means that the connection to the SQL server is not pooled. I guess am clear to you. I dont have a link handy. But definitely get back to you with the one soon.
You have many prepared plans in your application. Could you please get the highest top 10 adhoc queries that using high memory(granted) and look at those for its performance.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
-
Unfortunately I don't know how I can do it. How I can differ adhoc and not adhoc query. I ran the following query
select SUM(cast (size_in_bytes as bigint)) from sys.dm_exec_cached_plans where usecounts=1 and cacheobjtype='Compiled Plan'
The result was 2199420928 Bytes (about 2 Gb).
From 10000 plans in cache 8794 have usecounts=1 is it ok or strange?
- Edited by AleshaDBA Friday, August 24, 2012 8:23 AM
-
Try the below:
/********************************************************** * top procedures memory consumption per execution * (this will show mostly reports & jobs) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY IO_Per_Execution DESC /********************************************************** * top procedures memory consumption total * (this will show more operational procedures) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY Total_IO_Reads DESC /********************************************************** * top adhoc queries memory consumption total ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,QueryText = qt.text ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY Total_IO_Reads DESC /********************************************************** * top adhoc queries memory consumption per execution ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,QueryText = qt.text ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) IS NULL GROUP BY DB_NAME(qt.dbid), qt.text, OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ) T
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
- Marked as answer by amber zhangModerator Thursday, August 30, 2012 6:49 AM
-
-
Hi AleshaDBA, did you ever find a resolution? We are on sql server 2012 with a similar (same?) problem. Our query optimizer memory is ever-increasing, up to 50+GB until the server can no longer process simple queries or logins and we have to restart. We do have lots of ad hoc queries but this is not new --- we have had plenty of ad hoc queries for several years and this problem has started happening in the last few weeks. Thanks
-
-