High Memory is 70% & growing Fast

  • HI Experts,

    My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around 55% and now it is going to 70% and increasing.

    Total OS memory is 32GB and I kept cap for sql server memory upto 29GB. Dont know what to do..

    Please suggest...

    Thanks

    Praveen

  • Add more memory? Investigate WHY more memory is being used? What sort of monitoring are you doing?

    Regards

    Lempster

  • praneethydba (3/5/2014)


    HI Experts,

    My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around 55% and now it is going to 70% and increasing.

    Total OS memory is 32GB and I kept cap for sql server memory upto 29GB. Dont know what to do..

    Please suggest...

    Thanks

    Praveen

    SQL Server loves to use all the memory you assign to it. Are you experiencing any issues or memory pressures ?

    Any memory related errors in the error log ? Are you having any performance slow down ?

    --

    SQLBuddy

  • HI,

    I am checking the memory utilisation from task manager.

    My concern is how to & where to check the problem, as my db server memeory is increasing gradually..

    Thanks

    Praveen

  • praneethydba (3/5/2014)


    HI,

    I am checking the memory utilisation from task manager.

    My concern is how to & where to check the problem, as my db server memeory is increasing gradually..

    Thanks

    Praveen

    Check this

    DBCC MEMORYSTATUS

    --Host wise

    select hostname,count(spid) as Total_Connections,sum(isnull(memusage,0)) as MemUsage from sys.sysprocesses

    group by hostname order by count(spid) desc

    ----------

    --Database wise

    select db_name(dbid) as DatabaseName,count(spid) as Total_Connection,sum(isnull(memusage,0)) as MemUsage from sys.sysprocesses

    group by db_name(dbid) order by count(spid) desc

    --------

    http://www.mssqltips.com/sqlservertip/2304/how-to-identify-microsoft-sql-server-memory-bottlenecks/

    --

    SQLBuddy

  • But as sqlbuddy said, do you actually have a problem?

  • Thanks a lot Boss...

    But my biggest concern is its increasing, which parameter I need to check and what is the best count and how to change...

    Thanks

    Praveen

  • my db server memory size is increasing, now it is 75%. need to moderate it.

    my concern is if it occupies completely. I want to find the root cause.

    Please help me..

    Thanks

    Praveen

  • If I understand correctly you have set Max Server Memory to 29GB and you're seeing memory used at 75% of that figure which is 21.75GB. That is not an awful lot for SQL Server. How many instances are you running and what sort of applications are your databases supporting, e.g. busy OLTP system, Operational Data Store, Data Warehouse...?

    Have a look at the blog post below from Jonathan Kehayias.

    EDIT: Removed incorrect link

    Regards

    Lempster

  • HI Lempster,

    thanks for the reply..

    We are having only 1 instance and 35 databases & we are using sql server 2008R2.

    it is a sharepoint application...

    unfortunately, the link which you have send is not working.

    Please provide me the solution...

    Thanks

    Praveen

  • Sorry I posted completely the wrong link! :blush: The correct one is:

    http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

  • thanks a lot..

    I am going through the link. now today suddenly it went to 25%, I checked the event logs and sql logs. nothing was there to my notice.

    I want to investigate. Please share me some tips on this...

    Thanks

    Praveen

  • Did you use the activity monitor in SQL Server SSMS to see what processes are running within the SQLOS. It can give you some insight what is running.Also "Recent Expensive Queries" tab can show you if any procs are taking too many CPU cycles.Even though there is no memory information option, it can provide some insight.

    --Rohan

  • praneethydba (3/6/2014)


    thanks a lot..

    I am going through the link. now today suddenly it went to 25%, I checked the event logs and sql logs. nothing was there to my notice.

    I want to investigate. Please share me some tips on this...

    Thanks

    Praveen

    This is very unusual - SQL Server generally does not give back memory unless the OS asks for it. If the memory utilization dropped I would suspect that something else is installed on the system that needs that memory.

    SQL Server will take all of the memory - up to the max memory setting - that it needs. If you have not reached max memory and you see memory utilization going up, all that means is that SQL Server needs that memory. Note: with 32GB of memory available I would not recommend setting the max memory to 29GB. At most, I would set 28GB - and that value only if the system is dedicated to SQL Server and does not have SSIS/SSAS/SSRS installed or a lot of .NET CLR code.

    If you have any of those additional items installed - or .NET CLR code in the database, then you should drop the max memory setting.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Basically you would like to see who is using all the memory from SQL end.

    What are the max and min values for counters Page Life Expectancy and buffer cache hit ratio for a duration like 30 mins to 60 mins?

    Can you get memory usage by each database using below query ( after masking your data):

    SELECT COUNT(*)AS cached_pages_count

    ,CASE database_id

    WHEN 32767 THEN 'ResourceDb'

    ELSE db_name(database_id)

    END AS database_name

    FROM sys.dm_os_buffer_descriptors

    GROUP BY db_name(database_id) ,database_id

    ORDER BY cached_pages_count DESC;

    Glenn Berry blog would be helpful to get diagnostic data:

    http://sqlserverperformance.wordpress.com/2014/01/14/sql-server-diagnostic-information-queries-for-january-2014/

    Page life expectancy and buffer cache hit ratio could be monitored , for 60 mins, using below script. It doesn't harm your system.

    CREATE TABLE #_TBLMEMROY_PRESSURE(

    CntrNm varchar(100) ,

    Value decimal(10,2),

    CprtdDate datetime

    )

    DECLARE

    @int_PgLif INT

    , @INT_BCHR_base int

    , @INT_BCHR_val int

    , @INT_BCHR DECIMAL(10,2)

    , @time_start int = 0

    , @time_end int = 60 -- 60 MIN DURATION

    , @CprtdDate DATETIME;

    while @time_start <= @time_end

    BEGIN

    select @int_PgLif = cntr_value

    from sys.dm_os_performance_counters where object_name = 'SQLServer:Buffer Manager'

    and counter_name = 'Page life expectancy'

    select @INT_BCHR_val = cntr_value

    from sys.dm_os_performance_counters where object_name = 'SQLServer:Buffer Manager'

    and counter_name = 'Buffer cache hit ratio'

    select @INT_BCHR_base = cntr_value

    from sys.dm_os_performance_counters where object_name = 'SQLServer:Buffer Manager'

    and counter_name = 'Buffer cache hit ratio base'

    select @INT_BCHR = convert(decimal(10,2),(@INT_BCHR_val*100.0)/(@INT_BCHR_base*1.0))

    , @CprtdDate = GETDATE();

    INSERT INTO #_TBLMEMROY_PRESSURE(CntrNm ,Value,CprtdDate)

    VALUES ('Page life expectancy',@int_PgLif,@CprtdDate),

    ('Buffer cache hit ratio',@INT_BCHR, @CprtdDate)

    WAITFOR DELAY '00:02:00' -- WAIT FOR 2 MINS

    SELECT @time_start += 2;

    END

    -- GET RESULTS

    SELECT CprtdDate,CntrNm,Value FROM #_TBLMEMROY_PRESSURE

    WHERE CntrNm = 'Page life expectancy'

    ORDER BY CprtdDate ASC

    SELECT CprtdDate,CntrNm,Value FROM #_TBLMEMROY_PRESSURE

    WHERE CntrNm = 'Buffer cache hit ratio'

    ORDER BY CprtdDate ASC

    DROP TABLE #_TBLMEMROY_PRESSURE

    GO

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply