Memory Issues --sql server 97%

Question

Post reply

Memory Issues --sql server 97%

bala2

SSCertifiable

Points: 5547
More actions
June 18, 2012 at 1:47 pm

#258193

HI,
We have production server in which SQL server is consumming high Memory about 97%.
We are having a Production server with Windows 2008 server on which SQL server 2008 -64 bit prod is running.
Ram---8GB
max and min sql server setting is done for the server.
Max--6GB and MIN ---0
Can any one help me how to troubleshoot the memory issue.
From which parameter i have to start.
Please give me a step by step troubleshoot steps
Many Thanks....

Viewing 7 posts - 1 through 6 (of 6 total)

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

buckbova SSC-Addicted Points: 428 More actions · Answer 1

There is no issue. This is the behavior of SQL Server.

What I have done in the past to make sql give up it's memory it has built up:

Run this to make it give up all but a gig.

EXEC sys.sp_configure N'show advanced options', N'1'

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'min server memory (MB)', N'0'

EXEC sys.sp_configure N'max server memory (MB)', N'1024'

GO

RECONFIGURE WITH OVERRIDE

GO

Run this to let it build it back up to all available.

EXEC sys.sp_configure N'min server memory (MB)', N'0'

EXEC sys.sp_configure N'max server memory (MB)', N'2147483647'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0'

RECONFIGURE WITH OVERRIDE

GO

This is really not necessary of course.

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 2

Maybe you have a non-SQL Server process consuming CPU cycles?

Or a session consuming CPU (run away job :unsure: exectued by SQL Agent) have you checked sp_who2

Something must be running for a bit.

Or maybe a backup tool (if using 3rd party client) attempting to run/and is hanging but hitting the SQL Server?

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

manus4u Ten Centuries Points: 1238 More actions · Answer 3

-- Find memory util with the below query

SELECT total_physical_memory_kb/1024 TotmemMB, available_physical_memory_kb/1024 as AvailPhymemMB,

total_page_file_kb/1024 as TotpagefileMB, available_page_file_kb/1024 as AvailPagefileMB, system_memory_state_desc,

(select memory_utilization_percentage from sys.dm_os_process_memory)as memory_utilization_percentage

FROM sys.dm_os_sys_memory;

--Look at the values for these parameters; sply PLE

SELECT object_name,counter_name,cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name = N'Page life expectancy' and OBJECT_NAME = N'SQLServer:Buffer Manager'

or counter_name like ('Free pages%') and OBJECT_NAME = N'SQLServer:Buffer Manager'

or counter_name like ('Free list stalls/sec%') or counter_name like ('Page reads%')

or counter_name like 'Index%';

-- Also, you could check the top cpu consuming queries and if any ways 2 optimise them !!

-- Easy way is to add more memory; but that might not solve memory issues most time as sql will eat up whatever memory is allocated to it.

-- http://sqlskills.com/blogs/jonathan/post/Finding-what-queries-in-the-plan-cache-use-a-specific-index.aspx

manus4u Ten Centuries Points: 1238 More actions · Answer 4

-- Monitoring Memory Usage

http://msdn.microsoft.com/en-us/library/ms176018(v=sql.105).aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Performance-issues-from-wasted-buffer-pool-memory.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Nomal, expected behaviour. If SQL is using too much memory, reduce max server memory slightly. Maybe to 5.5GB.

Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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

bala2 SSCertifiable Points: 5547 More actions · Answer 6

THanks a Lot for replying and giving me an idea.

Can any one tell me if i want to run the Perfmon counters,

how much Time i have run to verfiy the data.