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....

• buckbova

  SSC-Addicted

  Points: 428

  More actions

  June 18, 2012 at 1:48 pm

  #1502423

  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

  June 18, 2012 at 1:50 pm

  #1502424

  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

  June 18, 2012 at 6:24 pm

  #1502535

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

  June 18, 2012 at 6:28 pm

  #1502538

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

  June 19, 2012 at 3:11 am

  #1502637

  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

  June 19, 2012 at 8:16 am

  #1502879

  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.