SQL Memory Issues

  • 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

  • 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

  • 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?

  • 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

  • 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_XchangeHeadOffice25666

    tempdb1674

    ReportServer$LIVE242

    ReportServer$LIVETempDB30

    msdb11

    master1

    CMBPGControl1

    Nod26ML_XChgHO_321

    NULL0

    TypeInfo0

    XChangeRepositorySchema060

    dev_Nod26ML_XchgHO_140

    model0

    Nod26ML_XchgHO_200

    Nod26ML_XChgHO_210

    Nod26ML_XChgHO_220

    Prepared1952853

    View2152616

    Adhoc206333

    Check403

    Trigger1042

    Proc32183705

    Overall Memory State

    85307686246660917568

    30231944

    Query Plan (sorry this is just a mess and takes up too much space)

    Thanks

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Its a 64bit server with max memory set to 38Gb

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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?

  • 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