memory allocation - 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)

memory allocation

Author

Topic

Chewy
Starting Member

3 Posts

Posted - 2011-12-06 : 04:59:02

Hi Guys,

SQL Server 2005

From the perfmon, I can see that sql server is using 16GB of memory <perfmon, total server memory>.

From the below URLs, I have managed to find out the memory used by data (buffer) and query plans (cache).

Buffer:8.2GB
Cache: 1.5G

The above adds up to near 10GB. May i know what is utilizing the remaining 6GB? Anyway to find out?

Appreciate your help.

http://www.sqlteam.com/article/what-data-is-in-sql-server-memory
http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory

thanks

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-12-06 : 11:07:49

This may help - uses RING_BUFFER_RESOURCE_MONITOR

DECLARE @ts_now bigint;

SET @ts_now = (SELECT cpu_ticks / CONVERT(bigint, cpu_ticks_in_ms)
FROM sys.dm_os_sys_info);

SELECT
record_id,
Memory_Utilisation,
Physical_Memory,
Available_Memory,
Reserved_Memory,
Committed_Memory,
Cached_Memory,
AWE_Memory,
Shared_Memory,
TotalPageFile,
AvailablePageFile,
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
--- DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/MemoryRecord/MemoryUtilization)[1]', 'int') AS [Memory_Utilisation],
record.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'int') AS [Physical_Memory],
record.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'int') AS [Available_Memory],
record.value('(./Record/MemoryNode/ReservedMemory)[1]', 'int') AS [Reserved_Memory],
record.value('(./Record/MemoryNode/CommittedMemory)[1]', 'int') AS [Committed_Memory],
record.value('(./Record/MemoryNode/CachedMemory)[1]', 'int') AS [Cached_Memory],
record.value('(./Record/MemoryNode/AWEMemory)[1]', 'int') AS [AWE_Memory],
record.value('(./Record/MemoryNode/SharedMemory)[1]', 'int') AS [Shared_Memory],
record.value('(./Record/MemoryRecord/TotalPageFile)[1]', 'int') AS [TotalPageFile],
record.value('(./Record/MemoryRecord/AvailablePageFile)[1]', 'int') AS [AvailablePageFile],
[timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'
AND record LIKE '%<MemoryRecord>%') AS x

) AS y
where (@ts_now - timestamp) > 0 and
(@ts_now - timestamp) < 999999999

Chewy
Starting Member

3 Posts

Posted - 2011-12-07 : 03:03:01

Hi,

Can advise on how to do inteprete it and sum up to 16GB?

thanks

record_id Memory_Utilisation Physical_Memory Available_Memory Reserved_Memory Committed_Memory Cached_Memory AWE_Memory Shared_Memory TotalPageFile AvailablePageFile Event Time
----------- ------------------ --------------- ---------------- --------------- ---------------- ------------- ----------- ------------- ------------- ----------------- -----------------------
4 58 37737992 35382852 37980760 223840 2920 0 0 86991944 85444888 2011-12-05 23:48:45.383
3 58 37737992 35382676 37980760 223840 2776 0 0 86991944 85444872 2011-12-05 23:48:40.383
2 62 37737992 35356664 37980760 234848 12568 0 0 86991944 85418256 2011-12-05 23:48:40.280
1 37 37737992 35440112 37980696 222496 1744 0 0 86991944 85481740 2011-12-05 23:47:39.290
0 20 37737992 35494024 37976320 216720 1416 0 0 86991944 85569712 2011-12-05 23:47:33.953
5 58 37737992 35382852 7424 15928 2936 0 0 86991944 85444888 2011-12-05 23:48:45.383
4 58 37737992 35382676 7424 15928 2624 0 0 86991944 85444872 2011-12-05 23:48:40.383
3 62 37737992 35356664 7424 31544 4352 0 0 86991944 85418256 2011-12-05 23:48:40.280
2 37 37737992 35440112 7104 15408 2632 0 0 86991944 85481740 2011-12-05 23:47:39.290
1 20 37737992 35493964 5568 5536 656 0 0 86991944 85569636 2011-12-05 23:47:33.953
0 20 37737992 35494068 5568 5536 656 0 0 86991944 85569744 2011-12-05 23:47:33.953

(11 row(s) affected)

Chewy
Starting Member

3 Posts

Posted - 2012-01-07 : 23:58:49

Hi Guys,

I still have difficulty interpreting the output after reading up the dbcc memory status relevant documents. Can anyone help me with this? Appreciate your time on this.

dbcc memory status output below

thanks!

My question:
From the perfmon, I can see that sql server is using 16GB of memory <perfmon, total server memory>.
From the below URLs, I have managed to find out the memory used by data (buffer) and query plans (cache).

Buffer:8.2GB
Cache: 1.5G

The above adds up to near 10GB. May i know what is utilizing the remaining 6GB? Anyway to find out?

http://www.sqlteam.com/article/what-data-is-in-sql-server-memory
http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory

DBCC MEMORY STATUS:

Memory Manager KB
------------------------------ --------------------
VM Reserved 44366000
VM Committed 18374464
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 51840
VM Committed 6011016
AWE Allocated 0
MultiPage Allocator 50680
SinglePage Allocator 3045224

(5 row(s) affected)

Memory node Id = 1 KB
------------------------------ --------------------
VM Reserved 44308400
VM Committed 12357772
AWE Allocated 0
MultiPage Allocator 22864
SinglePage Allocator 3045224

(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 21776
MultiPage Allocator 4528

(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 15672
MultiPage Allocator 16

(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 37448
MultiPage Allocator 4544

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 37830656
VM Committed 11930264
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 24

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 5959936
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 37830656
VM Committed 17890200
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 24

(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 288
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (node 1) 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_SQLQUERYEXEC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 296
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 864
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 864
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 120
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 120
MultiPage Allocator 0

(7 row(s) affected)

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

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2216
MultiPage Allocator 448

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 9408
VM Committed 9408
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4304
MultiPage Allocator 480

(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 1664
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 1712
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 3376
MultiPage Allocator 0

(7 row(s) affected)

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

(7 row(s) affected)

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

(7 row(s) affected)

MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 6313024
VM Committed 263292
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1920
MultiPage Allocator 43032

(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 80
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 80
MultiPage Allocator 304

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (node 1) 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 184
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 168
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 352
MultiPage Allocator 32

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (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_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 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
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 176
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 384
MultiPage Allocator 0

(7 row(s) affected)

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

(7 row(s) affected)

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

(7 row(s) affected)

MEMORYCLERK_HOST (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_HOST (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_SOSNODE (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 18848
MultiPage Allocator 11896

(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 19024
MultiPage Allocator 6368

(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 37872
MultiPage Allocator 18264

(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 196336
MultiPage Allocator 4832

(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 196336
MultiPage Allocator 4832

(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 2534288
MultiPage Allocator 568

(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 2534288
MultiPage Allocator 568

(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 31200
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 31200
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 104
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 104
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 168
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 168
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 (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 16

(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 1) 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_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
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_CLRPROC (node 1) 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_CLRPROC (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_SYSTEMROWSET (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2176
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 2176
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 6664
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 6664
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_DBMETADATA (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 9800
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 9800
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 29776
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 29776
MultiPage Allocator 0

(7 row(s) affected)

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

(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 3616
MultiPage Allocator 32

(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 808
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 776
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 1584
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 128
MultiPage Allocator 480

(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 128
MultiPage Allocator 528

(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 1008

(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 3024
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 3312
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 6336
MultiPage Allocator 96

(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 131072
VM Committed 131072
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 67760
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 67448
MultiPage Allocator 0

(7 row(s) affected)

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

(7 row(s) affected)

Buffer Distribution Buffers
------------------------------ -----------
Stolen 10902
Free 709626
Cached 369753
Database (clean) 1011473
Database (dirty) 91448
I/O 0
Latched 1

(7 row(s) affected)

Buffer Counts Buffers
------------------------------ --------------------
Committed 2193203
Target 4076566
Hashed 1102922
Stolen Potential 4100605
External Reservation 126
Min Free 512
Visible 4076566
Available Paging File 8341347

(8 row(s) affected)

Procedure Cache Value
------------------------------ -----------
TotalProcs 36953
TotalPages 345916
InUsePages 260

(3 row(s) affected)

Global Memory Objects Buffers
------------------------------ --------------------
Resource 666
Locks 16904
XDES 246
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 831
SQLCache 4147
Replication 2
ServerGlobal 49
XP Global 2
SortTables 2

(12 row(s) affected)

Query Memory Objects Value
------------------------------ -----------
Grants 1
Waiting 0
Available (Buffers) 3050462
Maximum (Buffers) 3050590
Limit 3050590
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 3063390

(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 26768351232
Target Memory 22448848896
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 369755
Rate -3
Target Allocations 3099204
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 10893
Rate -7
Target Allocations 2740338
Future Allocations 0
Last Notification 1

(5 row(s) affected)

MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate -38
Target Allocations 3267621
Future Allocations 762647
Last Notification 1

(5 row(s) affected)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources