-1

My SQL Server process memory usage show more than 3 GB on 64 bit machine.

I tried to find the problem with

SELECT 
   [object_name], [counter_name],  
   [instance_name], [cntr_value] 
FROM sys.[dm_os_performance_counters] 
WHERE [object_name] = 'SQLServer:Buffer Manager'

and result was:

Buffer cache hit ratio 6368
Buffer cache hit ratio base 6376
Page lookups/sec 438640376
Free list stalls/sec 182
Free pages 215468
Total pages 442368
Target pages 442368
Database pages 196000
Reserved pages 0
Stolen pages 30900
Lazy writes/sec 1510
Readahead pages/sec 1204816
Page reads/sec 1384292
Page writes/sec 765586
Checkpoint pages/sec 129207
AWE lookup maps/sec 0
AWE stolen maps/sec 0
AWE write maps/sec 0
AWE unmap calls/sec 0
AWE unmap pages/sec 0
Page life expectancy 119777

As this seems to be too much page lookup then I executed this query

SELECT 1.0*cntr_value /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec') 
AS [PageLookupPct]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page lookups/sec'

result for this query was more than 350.

One more metric was tried as

SELECT (1.0*cntr_value/128) /
(SELECT 1.0*cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name like '%Buffer Manager%'
AND lower(counter_name) = 'Page life expectancy')
AS [BufferPoolRate]
FROM sys.dm_os_performance_counters
WHERE object_name like '%Buffer Manager%'
AND counter_name = 'total pages'

result was 0.029.

I picked this from Tim Ford blogs.

What could be the possible cause of memory usage? Is it bad query plan or should I look on some other area?

[Update]

MEMORYCLERK_SQLBUFFERPOOL is using 3573824 (VM reserved) 3573824 (VM committed)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chaturvedi Dewashish
  • 1,469
  • 2
  • 15
  • 39
  • 2
    What exactly is the *problem* you're seeing? SQL Server uses all the memory available, and you should make available way way way way more than just 3GB. – Remus Rusanu Aug 26 '14 at 13:10
  • have a look at this question on DBA http://dba.stackexchange.com/questions/47431/why-is-sql-server-consuming-more-server-memory – Oliver Aug 26 '14 at 13:12
  • I understand, I can set max memory for SQL server. But there are not too much activity on server databases so it's kind of weird. – Chaturvedi Dewashish Aug 26 '14 at 13:34
  • If given a normal load SQL server usage goes to 8 GB. According to performance metrics I provided it seem to some page fault. I also watched through perfmon.exe there is high no of page faults/sec for given process – Chaturvedi Dewashish Aug 26 '14 at 13:36
  • SQL Server is greedy. It takes all available memory and gives it back to the OS if it needs it. So it may look like, at the OS level, that SQL server is super busy. It's, for the most part, just holding it. – sam yi Aug 26 '14 at 15:05

1 Answers1

0

Check your setting under Properties \ Memory \ Maximum Server Memory.

See the answer on this post: Seeing High Memory Usage In SQL Server 2012 which outlines (roughly) the fact that SQL Server will claim what it can based on the maximum available memory. Unless you need this memory for something else, do not worry about it.

Community
  • 1
  • 1
JLo
  • 3,207
  • 3
  • 20
  • 36
  • Its 2147483647 MBs. I mean its maximum possible. I understand that SQL server will claim all memory if required, but my concern is to analyze if some bad query plan in some stored procedures hitting the memory. For example using temp table instead of table variables will optimize the query execution. Along with this it seems that page fault/sec is too high. So it seems to be some kind of problem in some of the databases. Can we deduce some area needs to be diagnosed based on provided performance counter or we need some more? – Chaturvedi Dewashish Aug 27 '14 at 06:57
  • 1
    It sounds like your question may be a little misleading? Are you actually asking "How do I tell what my SQL Server memory is being used for?" In which case, I don't know if there is a specific answer. The question I might ask is, is it starved and does it need more? The first place I would turn (other than StackOverflow) is Brent Ozar's website. It's chock full of great SQL Server advice, these two might be useful to you: http://www.brentozar.com/archive/2014/05/much-memory-sql-server-need/ and http://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/ – JLo Aug 27 '14 at 13:54