High Memory is 70% & growing Fast

Question

Post reply

High Memory is 70% & growing Fast

praneethydba

SSC-Addicted

Points: 498
More actions
March 5, 2014 at 7:46 am

#286314

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

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

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

Lempster SSCoach Points: 15651 More actions · Answer 1

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

Regards

Lempster

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

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

praneethydba SSC-Addicted Points: 498 More actions · Answer 3

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

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

Lempster SSCoach Points: 15651 More actions · Answer 5

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

praneethydba SSC-Addicted Points: 498 More actions · Answer 6

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

praneethydba SSC-Addicted Points: 498 More actions · Answer 7

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

Lempster SSCoach Points: 15651 More actions · Answer 8

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

praneethydba SSC-Addicted Points: 498 More actions · Answer 9

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

Lempster SSCoach Points: 15651 More actions · Answer 10

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/

praneethydba SSC-Addicted Points: 498 More actions · Answer 11

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

Rohan Joackhim Old Hand Points: 391 More actions · Answer 12

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 13

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

SumLasty SSCommitted Points: 1923 More actions · Answer 14

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