June 21, 2010 at 2:46 pm
I am receiving memory bottleneck messages and I need to know what is causing them. Does anyone have any advice on how to determine what is cauing the memory bottleneck. This is sql 2005 64bit installed on windows 2003 R2 server 64bit server with 8gig of memory. There are user 8 databases all having logshipping configured. AWE is not enabled. Can sql profiler be used to trace the memory issues?
Thanks.
Patti
June 22, 2010 at 4:49 am
AWE does not do anything on a 64-bit system (enabled or not). Have you run any scriptps to show the memory usage per database?
SELECT CONVERT (varchar(30),
GETDATE(), 121) as runtime,
DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - a.[Record Time]),
GETDATE()) AS Notification_time, a.* , sys.ms_ticks AS [Current Time]
FROM (SELECT x.value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type], x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %], x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB], x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB], x.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB], x.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB], x.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB], x.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB], x.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id], x.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB], x.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB], x.value('(//Record/@id)[1]', 'bigint') AS [Record Id], x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type], x.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators], x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)) a CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] DESC
--dbcc memorystatus
or something like this to show the memory allocation:
--Buffer usage by database
select db_name(database_id) as dbName, (count(database_id) * 8) / 1024 as BufferPoolMB
from sys.dm_os_buffer_descriptors
group by db_name(database_id)
order by 2 desc
--Identify if the adhoc sql cache is bloated
SELECT objtype,
count(*)as number_of_plans,
sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as size_in_MBs,
avg(usecounts)as avg_use_count
from sys.dm_exec_cached_plans
group by objtype
Also if you have any 3rd party tools. We use Idera DM, its quite good and you can get a trial!
Good luck. 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 22, 2010 at 5:59 am
Thanks Adam. I will run these sql scripts and see what happens. I have DBArtisan and I am trying to figure out how to use it at this time. We just got it, or should I say it was purchased for another DBA that left the company and they gave the license and software to me to use. I hope it helps me figure out what's wrong. I will keep you posted on what I find out after running the sql scripts you provided. Thanks again. Patti
June 22, 2010 at 6:37 am
--Buffer usage by database:
BESMgmt 794
OperationsManager 726
BosBesMgt 245
rtc 81
MSDB 52
rtcdyn 43
tempdb 42
BMSStoreEx 37
NULL 14
BMSStore 6
master 1
rtcconfig 1
model 0
--Identify if the adhoc sql cache is bloated
Obj Type #Plans Size MBs Avg Use Cnt
UsrTab 30 895
Prepared 5863 2316 40265
View 540 51 987
Adhoc 1503 92 18187
Check 81 3 3377
Trigger 168 49 302848
Proc 574 240 377624
I have the output from the other --dbcc memorystatus but it is too much to post
Does anyone see anything glaring?
June 22, 2010 at 6:40 am
EDITED: Sorry, I'm talking sh*t.
The top two databases are using the most memory but you said you had 8GB so this doesnt come near. What are your other memory stats? Whats the macimum memory set to? And what does task manager report?
Also look to see how many connections BES has as it has a tendancy to strangle a server with connections.
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 22, 2010 at 6:44 am
Do you mean the adhoc sql cache is bloated or the Buffer usage by database?
June 22, 2010 at 6:48 am
Patricia Johnson (6/22/2010)
Do you mean the adhoc sql cache is bloated or the Buffer usage by database?
dont worry, i have edited now. Read last.
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 22, 2010 at 6:50 am
Colleage of mine just made a suggestion: Have you locked pages in memory? This done through gpedit.msc and has to be enabled for the sql service user.
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 22, 2010 at 7:01 am
I'm sure I haven't locked memory.
Here is what I snapped yesterday from task manager. I added a few comments just so it makes sense (somewhat) to me.
Commit Charge K (When Total is close to Limit then page file maybe running low)
Total 8031168
Limit 20501988 Limit indicates the max amount of memory that can be committed without extending page file
Peak 8491972
Physical Memory K
Total 8386168
Available 290492
System Cache 626284
Kernel Memory K
Total 896694
Paged 205184
Nonpaged 691552
Buffer Counts Buffers
------------------------------ --------------------
Committed 689221
Target 707316 (8kb pages)
Hashed 277457
Stolen Potential 589538
External Reservation 0
Min Free 256
Visible 707316
Available Paging File 1560387
Thanks for being patient with me! 🙂
June 22, 2010 at 7:28 am
Also, I looked in SQL Server - Min Server Memory set to 0
Max Server Memory set to 2147483647
Processors - 8
Memory - 8190MB
June 22, 2010 at 8:03 am
Ok a few ideas from my colleague:
1. Set max memory to 5GB (you'll need to run sp_reconfigure or restart), this leaves 3GB for OS + Apps.
2. Lock pages in memory (important on 64-bit systems)
3. Run this code and report results:
SELECT
physical_memory_in_bytes
,virtual_memory_in_bytes
,bpool_committed AS 'Number of 8KB buffers in buffer pool'
, bpool_commit_target AS 'Number of 8KB buffers needed by the buffer pool'
,CASE
WHEN bpool_commit_target > bpool_committed THEN 'Extra memory needed from OS for Buffer Pool'
WHEN bpool_commit_target < bpool_committed THEN 'Memory may be released from Buffer Pool to OS'
END AS 'Status of Dynamic Memory'
, bpool_visible AS 'Number of 8KB Buffers in Buffer Pool that are directly accessible in the processes VAS.'
FROM sys.dm_os_sys_info
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 22, 2010 at 8:10 am
Hey Adam, do I run this sql after I run sp_reconfigure and lock memory, or run it now?
June 22, 2010 at 8:15 am
Yes the code is a seperate query.
Set the max memory and then run sp_reconfigure.
Then verify lock pages in memory (if you have to set i t it will require a sql restart).
Then run the query.
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 22, 2010 at 8:20 am
Adam,
I figured that it wouldn't matter if I ran it now before making any changes just in case it sheds more light on the problem.
Here is the output from the sql:
physical_memory_in_bytes
------------------------
8587436032
virtual_memory_in_bytes
-----------------------
8796092891136
Number of 8KB buffers in buffer pool
------------------------------------
676220
Number of 8KB buffers needed by the buffer pool
-----------------------------------------------
690638
Status of Dynamic Memory
-----------------------------------------------
Extra memory needed from OS for Buffer Pool
Number of 8KB Buffers in Buffer Pool that are directly accessible in the processes VAS.
---------------------------------------------------------------------------------------
690638
June 22, 2010 at 8:38 am
Not sure really, it depends on whats been done. To be 100% sure, make all the suggested changes (max size and lock pages in memory), then restart the entire server (Start-->Shut Down-->Restart) and then run the query.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply