November 25, 2009 at 3:59 am
My SQL Server 2005 has 38Gb allocated to it and its currently using 42Gb, problem is it never frees up any of this memory for other processes and the app I have running on SQL is now becoming unusable...whats the best way to free up the memory, the system used to run on only 24Gb and started to have issue so we slowly increased the memory availbale until it was at 38Gb, each time I have increased the memory the app works fine for a day and by the next morning it has eaten up all the memory. I have 48Gb in the server but don't want to give out any more.
Can I set the memory to 24Gb again (without a restart) and go through the process of slowly increasing the memory again.
Cheers
Craig H
November 25, 2009 at 4:20 am
If you want to clear down the CLR and QUery plan caches you can use:
dbcc freeproccache
DBCC FREESYSTEMCACHE ('ALL')
DBCC DROPCLEANBUFFERS
But you really need to find out whats using your memory. Have you recently added a database or system which may be using CLR heavily? Are you actively monitoring you memory usage (buffers etc).
What have you done so far (have lots of scripts which may help, but need more info).
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 25, 2009 at 4:25 am
Initially I thought it was an IIS problem so I started to recycle that every hour but this made no difference.
SSRS also runs on this server and I stopped that to see if it helped but nothing here either.
The app is loading one screen (stock control screen) and it works fine on smaller stores (smaller stock/less transactions) but times out after 33 seconds on the bigger stores, increasing the memory by 2Gb a day helped this but only until I ran out of memory.
The server is mirrored and if I fail the server over to the passive mirror and then fail it back immediatley everything starts working again, but the memory usage is still the same?
November 25, 2009 at 4:47 am
Does sound like a buffer issue. I'm not sure if failing a mirror over, truncates the caches and query plans but if it did that would be why you failover test works.
This will show you the memory used by each database:
--Buffer usage by database
select db_name(database_id) as dbName, count(*)*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
This will show you an overall memory status:
With VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,
CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
SELECT
SUM( multi_pages_kb +
virtual_memory_committed_kb +
shared_memory_committed_kb +
awe_allocated_kb ) AS [Used by BPool, KB]
FROM
sys.dm_os_memory_clerks
WHERE
[type] = 'MEMORYCLERK_SQLBUFFERPOOL'
And this shows your query plan statistics:
SELECT TOP 6
LEFT([name], 20) as [name],
LEFT([type], 20) as [type],
[single_pages_kb] + [multi_pages_kb] AS cache_kb,
[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC
select TOP 100
objtype,
usecounts,
p.size_in_bytes,
[sql].[text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
--ORDER BY usecounts DESC
ORDER BY size_in_bytes DESC
Let me know what you find.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 25, 2009 at 5:11 am
Thanks Adam, I have just dropped the memory down to 28Gb let it settle and then up'd it to 30Gb and my system has started working (not sure if this is bad practice or not as I'm not a DBA just lumped with this server to look after)
this is the output
live_XchangeHeadOffice 25666
tempdb 1674
ReportServer$LIVE 242
ReportServer$LIVETempDB 30
msdb 11
master 1
CMBPGControl 1
Nod26ML_XChgHO_32 1
NULL 0
TypeInfo 0
XChangeRepositorySchema06 0
dev_Nod26ML_XchgHO_14 0
model 0
Nod26ML_XchgHO_20 0
Nod26ML_XChgHO_21 0
Nod26ML_XChgHO_22 0
Prepared 195 285 3
View 215 26 16
Adhoc 206 33 3
Check 4 0 3
Trigger 1 0 42
Proc 321 83 705
Overall Memory State
8530768624 6660917568
30231944
Query Plan (sorry this is just a mess and takes up too much space)
Thanks
November 26, 2009 at 4:23 am
craighenderson (11/25/2009)
My SQL Server 2005 has 38Gb allocated to it and its currently using 42Gb, problem is it never frees up any of this memory for other processes and the app I have running on SQL is now becoming unusable
SQL's a memory hog and will not willingly release memory (unless the OS tells it to)
What's the max memory set to?
32 bit or 64 bit?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2009 at 4:27 am
Its a 64bit server with max memory set to 38Gb
November 26, 2009 at 4:36 am
The max memory controls the buffer pool. There's always a little memory more that SQL uses - thread stacks, backup buffers, CLR, some other stuff. It's not usually big. If it's up at 10GB, then you may have other problems than memory pressure for other apps. Take a look through the various memory-related DMVs, see if you can see where the memory's been used. You're not looking for memory used by the buffer pool, that's constrained to the max memory setting. You're looking for non-buffer related memory.
If you're sharing the box with other stuff, I'd personally set SQL's max to maybe 30GB and just leave it unless there's evidence that 30GB is not enough for the buffer pool.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2009 at 4:58 am
I only have the one app on SQL and SSRS (but this will be moved soon - hopefully)
according to a DMV query CACHESTORE_SQLCP, USERSTORE_TOKENPERM and OBJECTSTORE_LOCK_MANAGER are using the most memory - but they are only using 466Mb, 323Mb and 291Mb (singlepage allocator)
Which doesn't suggest any issues with memory, does it?
November 27, 2009 at 8:58 am
1) use dbcc memorystatus to see what is really going on with memory usage. search online for info on that undocumented functionality.
2) As Gail says, lots of stuff outside the buffer pool.
3) there was a memory leak discovered recently that affected many flavors of sql server. search for information on that. Perhaps your system is affected by it?
4) have you really tuned your system so that it can run more efficiently on less ram? have you done waitstats or file IO stall analyses?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply