SQL Server Memory Management - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Server Memory Management

Author

Topic

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-16 : 14:13:24

I am running SQL server 2005 (64 Bit) on a Windows 2003 server. In the last couple of weeks I have noticed that memory usage has increased dramatically. SQL server used to use about 5 to 6 GB of RAM but now it spikes up to 18GB of ram. How can I determine what part of SQL is using the RAM? I have tried using sys.dm_exec_cached_plans, sys.dm_exec_sql_text, sys.dm_exec_query_plan and others to try and isolate the procedure that is causing the bump in RAM. I'm pretty sure its something that its a problem created by a DB admin, but I can't seem to track down which newly changed item is the root of the problem. Is there a way to view memory usage in a detailed way that will point me to an item (or items) that are consuming memory?

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-16 : 15:11:39

You can use ..

SELECT TOP 10 type,
SUM(single_pages_kb) as [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

I don't know all the types of head but it gives you an idea of what is being used. I would also recommend checking the ram settings on SQL Server to make sure it is not set to default of unlimited memory usage (aka a really large number in max value). The value is MB not KB so you probably want to limit it to your needs :).

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-19 : 14:29:46

I will give this a shot.

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-19 : 15:20:53

I ran the query you suggested it returned the following:

It seems to indicate that the greatest usage is in cached procedures.
I also looked at the memory settings. I didn't change this from the install so its still at the default:

The fact that the server is using 18GB of the 32 GB of ram isn't really a problem if thats what its supposed to do--but I think something is wrong. We have seen some odd performance issues that seem to indicate that SQL server is running out of memory. Sporadic misfires on websites that use this server. The other issue is that prior to a couple of weeks ago the server would average about 10GB of ram. SQL is using the bulk of that ram (as expected) but now the server seems to stay between 18 and 25GB of Ram:

The other intresting fact is that the ram usages seems to come all at once. I can reset SQL server and the usage drops instantly. Then it will hover at the normal levels for hours. Then without warning and with no dicernable pattern SQL will jump up to 15 or 16GB. This is not a progression over hours but over a few minutes. I have poured over all recent code changes and I can't seem to find the culprit. I have reviewd system logs and security extensively looking for any possible outside influences--but everything seems normal.
What I need is to look at SQL server usage the same way you can look at memory usage on a server. Sort of a SQL task manager that would break down by KB everything occupying memory on the server. If I could do that then maybe I could find which item is giving me greif. It seems to be a problem we created but I just can't seem to find what's causing it to eat up memory.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-19 : 15:44:40

Few comments from your configuration:

1) Why do you have AWE enabled? On a 64-bit system awe is not needed. AWE takes up memory to manage large memory sets. Please turn this off.
2) The default memory setting for SQL Server is 0MB min and 2PB Max; since you have 32gb. I would set max setting so your OS doesn't chock.
3) Your memory clerk don't seem out of order; I would want to inspect the code that is being executed:
- I performance tuned the SQL Server recently where developer was doing Select * from a 10Million row table; so the memory was being chewed up by that because the pages are transferred to memory before processing.
- So question is any new releases? Major reports? Or bad query being executed?

You can look at the top I/O queries in SQL Server 2005, look at the following article:
http://www.databasejournal.com/features/mssql/article.php/3737936/Top-Queries-in-SQL-Server-2005.htm

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-19 : 16:54:57

Thanks for the quick reply. I did not know that I didn't need awe I will turn it off and change the memory config as you have suggested.

I ran:

select top 100 * from sys.dm_exec_cached_plans order by size_in_bytes DESC

Which shows:

816 2 2 532480 0x0CBBA0C0 Compiled Plan Adhoc 0x0600090000439B12B8A1BB0C000000000000000000000000

Does the plan handle tie back to something specific or do you know?

I will review the article you have suggested and get back with.
Thanks very much for your advice.

-Eric

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-19 : 17:06:57

Yeap that plan handle tells you the SQL statement; following SQL Statement can get you the information:

SELECT QS.last_execution_time AS [Time],
SUBSTRING(ST.TEXT,
(QS.statement_start_offset/2) + 1,
( ( CASE QS.statement_end_offset WHEN -1 THEN
DATALENGTH(ST.TEXT)
ELSE
QS.statement_end_offset
END - QS.statement_start_offset ) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
ORDER BY QS.last_execution_time DESC

Ref: http://sqllearnings.blogspot.com/2009/03/sysdmexecquerystats-dmv-and.html

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-19 : 20:28:09

SQl Server is a memory hog. It consumes whatever it needs to. Remember Procedure cache can eat up lots of memory if it is not efficiently written .

Post the results of DBCC memorystatus. Analyze the unnecessary proc cache plans

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-20 : 13:41:06

I haven't changed the meory settings yet, so AWE is still enabled and the max memory is still as it was. I will probably wait until our next maintainence window to correct those. Here is the result of DBCC memorystatus. I waill also post some other intresting info.

Memory Manager KB
------------------------------ --------------------
VM Reserved 33722928
VM Committed 10867756
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 5696
VM Committed 773504
AWE Allocated 0
MultiPage Allocator 5552
SinglePage Allocator 1825344

(5 row(s) affected)

Memory node Id = 1 KB
------------------------------ --------------------
VM Reserved 33698672
VM Committed 4730712
AWE Allocated 0
MultiPage Allocator 27760
SinglePage Allocator 1825344

(5 row(s) affected)

Memory node Id = 2 KB
------------------------------ --------------------
VM Reserved 5824
VM Committed 726088
AWE Allocated 0
MultiPage Allocator 5688
SinglePage Allocator 1825344

(5 row(s) affected)

Memory node Id = 3 KB
------------------------------ --------------------
VM Reserved 6976
VM Committed 4631768
AWE Allocated 0
MultiPage Allocator 6616
SinglePage Allocator 1825344

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 12920
MultiPage Allocator 4368

(7 row(s) affected)

MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17904
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLGENERAL (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8664
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLGENERAL (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 11848
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51336
MultiPage Allocator 4368

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 33603584
VM Committed 4636184
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 264

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 767840
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 720296
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 4624936
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 33603584
VM Committed 10749256
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 264

(7 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 400
MultiPage Allocator 128

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 400
MultiPage Allocator 128

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 640
VM Committed 640
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1512
MultiPage Allocator 32

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 856
MultiPage Allocator 296

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 640
VM Committed 640
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2376
MultiPage Allocator 328

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1104
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1104
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1064
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1152
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4424
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 304

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 304

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 136
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_SNI (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 136
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_SNI (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_SNI (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 136
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 560
MultiPage Allocator 64

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4896
MultiPage Allocator 11600

(7 row(s) affected)

MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 5312
MultiPage Allocator 5040

(7 row(s) affected)

MEMORYCLERK_SOSNODE (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4896
MultiPage Allocator 5360

(7 row(s) affected)

MEMORYCLERK_SOSNODE (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 5168
MultiPage Allocator 6024

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 20272
MultiPage Allocator 28024

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_OBJCP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 53200
MultiPage Allocator 16

(7 row(s) affected)

CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 53200
MultiPage Allocator 16

(7 row(s) affected)

CACHESTORE_SQLCP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1659912
MultiPage Allocator 10720

(7 row(s) affected)

CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1659912
MultiPage Allocator 10720

(7 row(s) affected)

CACHESTORE_PHDR (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 7040
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 7040
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_STACKFRAMES (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_STACKFRAMES (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_STACKFRAMES (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 32

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1128
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1128
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1824
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1824
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 5712
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 5712
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 672
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 672
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 816
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 816
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 48
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 56
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 256

(7 row(s) affected)

OBJECTSTORE_LBSS (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 256

(7 row(s) affected)

OBJECTSTORE_LBSS (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 256

(7 row(s) affected)

OBJECTSTORE_LBSS (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 224

(7 row(s) affected)

OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 256
MultiPage Allocator 992

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2312
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2408
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2376
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2408
MultiPage Allocator 48

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 9504
MultiPage Allocator 192

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 272
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 65536
VM Committed 65536
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1288
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1096
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (node 2) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1192
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (node 3) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1160
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 65536
VM Committed 65536
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4736
MultiPage Allocator 0

(7 row(s) affected)

Buffer Distribution Buffers
------------------------------ -----------
Stolen 9975
Free 1026887
Cached 218193
Database (clean) 56299
Database (dirty) 4747
I/O 0
Latched 4

(7 row(s) affected)

Buffer Counts Buffers
------------------------------ --------------------
Committed 1316105
Target 2815972
Hashed 61050
Stolen Potential 2899807
External Reservation 0
Min Free 512
Visible 2815972
Available Paging File 1976501

(8 row(s) affected)

Procedure Cache Value
------------------------------ -----------
TotalProcs 11663
TotalPages 216371
InUsePages 292

(3 row(s) affected)

Global Memory Objects Buffers
------------------------------ --------------------
Resource 241
Locks 595
XDES 175
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 226
SQLCache 6302
Replication 2
ServerGlobal 49
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 2103295
Maximum (Buffers) 2103295
Limit 2103295
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 2116095

(11 row(s) affected)

Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 12800
Maximum (Buffers) 12800
Limit 12800

(5 row(s) affected)

Optimization Queue Value
------------------------------ --------------------
Overall Memory 18490761216
Target Memory 15819857920
Last Notification 1
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway Value
------------------------------ --------------------
Configured Units 64
Available Units 64
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000

(6 row(s) affected)

Medium Gateway Value
------------------------------ --------------------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 12

(5 row(s) affected)

Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

(5 row(s) affected)

MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 218197
Rate 18
Target Allocations 2139449
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 9971
Rate -70
Target Allocations 1931135
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate -612
Target Allocations 2257173
Future Allocations 525823
Last Notification 1

(5 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-20 : 13:43:14

SELECT TOP 10 type,
SUM(single_pages_kb)/1000 as [SPA Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

RETURNS:

type SPA Mem, Mb
------------------------------------------------------------ --------------------
CACHESTORE_SQLCP 1774
MEMORYCLERK_SQLGENERAL 54
CACHESTORE_OBJCP 54
MEMORYCLERK_SOSNODE 21
OBJECTSTORE_SNI_PACKET 9
CACHESTORE_PHDR 8
USERSTORE_DBMETADATA 5
OBJECTSTORE_LOCK_MANAGER 4
MEMORYCLERK_SQLCONNECTIONPOOL 4
MEMORYCLERK_SQLSTORENG 2

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-20 : 13:50:01

SELECT TOP 100 (size_in_bytes)/10000 as [Size MB],* FROM sys.dm_exec_cached_plans ORDER BY size_in_bytes DESC

Returns:

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-20 : 13:57:02

I did this query to find out where memory is allocated but it doesn't see to add up to 18GB:

select
type,
sum(virtual_memory_reserved_kb)/1000 as [VM Reserved (MB)],
sum(virtual_memory_committed_kb)/1000 as [VM Committed (MB)],
sum(awe_allocated_kb)/1000 as [AWE Allocated (MB)],
sum(shared_memory_reserved_kb)/1000 as [SM Reserved (MB)],
sum(shared_memory_committed_kb)/1000 as [SM Committed (MB)],
sum(multi_pages_kb)/1000 as [MultiPage Allocator (MB)],
sum(single_pages_kb)/1000 as [SinlgePage Allocator (MB)]
from
sys.dm_os_memory_clerks
group by type
Order by sum(single_pages_kb) DESC

RETURNS:

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-20 : 14:23:34

These dynamic management and dynamic management view queries are great--but I still can't seem to find any one thing that sticks out as the culprit for the increased memory usage. Maybe the fact that I can't point to a single cached plan using a large amount of memory means I should be looking elsewhere.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-20 : 14:41:53

Hmmm I wonder why? I tested this on multiple SQL Servers just now and in all cases it added up (almost). But it was sure the reserved memory for the SQL Server exceeded the memory task manager was showing. It was closer to the SQLServer:Memory Manger/Target Server Memory(KB) counter.

SELECT OBJECT_NAME
,Counter_Name
,( cntr_value / 1024 ) AS [Counter Value (MB)]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE 'SQLServer:Memory Manager%'
AND counter_name LIKE 'Target Server Memory (KB)%'

SELECT type
,SUM(virtual_memory_reserved_kb + awe_allocated_kb
+ shared_memory_reserved_kb + multi_pages_kb + single_pages_kb)
/ 1024 AS [Total Memory Reserved by Clerk (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY [Total Memory Reserved by Clerk (MB)] DESC

SELECT SUM(((size_in_bytes/1024)/1024)) as [Size MB]
FROM sys.dm_exec_cached_plans

When you saying 18GB; are you basing that on the counter or at Task Manager? Thanks

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-20 : 17:16:03

18GB in task manager. Thats total memory usage--but SQL is using like 11GB. It used to use 3-4GB

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-22 : 13:49:42

Opps Sorry for late reply; I didn't get alert for your last post.

Hmm I'll try to dig into a bit more; as I am a bit lost. If your cache buffer memory usage doesn't match up I am not sure where the memory is going. For me the Task Manager always shows less then what SQL Server is actually consuming, the task manager never adds up. If I find something in research I'll let you know .. Sorry wasn't help :(.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-23 : 09:20:18

To the contrary, the problem may not be resolved but you've been a big help in pointing me to the routines that help me explore all of the meorm management stuff. The top SQL queries will be particularly useful.

Thanks for all of your input. I will continue to plug away at the problem. If I discover the answer I will post whatever I find here.

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-23 : 09:47:22

According to this article:

[url]http://www.sqljunkies.ddj.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk[/url]

It looks like there is a distinct possibility that nothing is wrong. In the process of tracking down the memory issue we did rewrote 50 or so stored procedures. Some of the procedures saw performncae gains of over 1000%. It seems that the server is no longer lagging like it was. The article has a section "Why Does SQL Server Memory Usage Grow and Not Shrink?" which looks like it may apply here.

It's possible that the memory issue was completely unrelated to the performance issues. A little more research is warranted, but its looking like we may be out of the woods on this one.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 13:52:12

Heh thanks for article :).

I'll add it to list of links I'll be releasing on my blogs with other useful links I been collecting over last month or two. But that is right about SQL Server; my concern was why did you memory started spiking from 5-6GB to 18GB.... that was a bit weird to me. But if in your new store procs you are doing a lot sorting, large joins, wide selects it can chew up the memory.

Mohit.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

ericallenpaul
Starting Member

13 Posts

Posted - 2009-03-23 : 14:47:27

According to the docs SQL is designed to take as much ram as possible:

[url]ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f3c7e4e-3270-4c11-839b-32cc0f40eef7.htm[/url]

I managed to come accross some other good info on the topic too:

[url]http://blogs.msdn.com/slavao/archive/category/9005.aspx[/url]

On Slava Oks's blog he talks about the book "SQL Server 2005 Practical Troubleshooting: The Database Engine" which he contributed to, but was writtem by a few people on the support side of SQL server at Microsoft. The book has a chapter devoted to how SQLOS makes use of memory.

The bottom line here is that focusing on the fundamentals was really what I should have been doing. The memory thing was a bit of a distraction. The Top Queries sp quickly pointed out queries which needed to be optimized. Doing that seems to have resolved the actual performance issues. I still can't explain why the sudden jump in memory occurs but it does appear to be normal.

Mohit - thanks again for all of your help. Even though I didn't get the solution I was looking for I still managed to solve the problem and I couldn't have done it without your input. Knowing the right terms to search for is what its all about and you led me down a path that helped me find my answers.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-23 : 19:50:17

I been doing SQL Server for 4 years only; even though I spend most of my free time keeping my head in SQL Technical papers I far from expert. Thanks for another great link ;-). More reading to do LOL, urg seems I'll never catch up. Hehe probably for the best keeps me focused :D.

Thanks and *cheers*

Mohit.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources