Memory not being used - 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

General SQL Server Forums

New to SQL Server Programming

Memory not being used

Author

Topic

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 03:11:50

I have some huge queries that I'm using and SQL suddenly stopped using all the beautiful memory it was using before.

I've enabled page locks on server 2008 x64
I've set the min\max at 25GB and I have 32gb of ram on a dedicated machine.

This seemed to happen after having to restore this machine. Any ideas?

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 03:19:42

Process/System Counts Value
Available Physical Memory 30690926592
Available Virtual Memory 8741543809024
Available Paging File 64173826048
Working Set 490463232
Percent of Committed Memory in WS 99
Page Faults 138675
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0

Memory Manager KB
VM Reserved 52934200
VM Committed 479512
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 25600000
Current Committed 479512
Pages Allocated 253840
Pages Reserved 0
Pages Free 34192
Pages In Use 291616
Page Alloc Potential 24028384
NUMA Growth Phase 0
Last OOM Factor 0
Last OS Error 0

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 03:24:17

If its relevant the DB is 65gb and the queries are large join queries with wildcards.

I also had an incident where tempDB ballooned to 450gb once. Wondering if that is related?

Windows Server 2008 R2 and SQL 2012 Developer.

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 03:32:04

Database_Page_Cache_MB Procedure_Cache_MB Misc_Memory_Usage_MB Total_Memory_Usage_MB Maximum_Workspace_MB
150.070312 26.335937 4.367187 180.773436 19042.96875

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 03:35:08

Database Name DirtyPageCount CleanPageCount TotalPageCount BufferPoolMB
Resource Database 0 3023 3023 23.62
OLD 0 1781 1781 13.91
MYMAINDB 0 1536 1536 12.00
tempdb 53 1232 1285 10.04
OLD 3 877 880 6.88
msdb 11 569 580 4.53
master 0 386 386 3.02
model 0 96 96 0.75

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-07-03 : 08:33:32

Could you run the following query and post the results?
SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory ;

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 09:56:41

quote:
Originally posted by jackv

Could you run the following query and post the results?
SELECT total_physical_memory_kb, available_physical_memory_kb,
total_page_file_kb, available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory ;

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

total_physical_memory_kb available_physical_memory_kb total_page_file_kb available_page_file_kb system_memory_state_desc
33506272 19965064 67010692 52655056 Available physical memory is high

Running at 12.9GB right now with a query that's been running all night.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-07-03 : 10:06:08

It looks like there is plenty of memory. Have you checked the execution plan of the query? Is there blocking\deadlocking?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 11:32:40

How do I check for locks?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-07-03 : 14:04:14

For blocking you have some good options from 2005 and later( I'm assuming you are using at least 2005) : DMV - sys.dm_os_wait_stats, sys.dm_os_waiting_tasks ,sys.dm_tran_locks
Trace - Blocked Process Report in SQL Trace

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-03 : 18:02:28

Those files are too wide to view properly on here so I've drop boxed them.

https://www.dropbox.com/sh/geadtvwc77oy1tt/xBRdQXdrwK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources