SQL Server 2008 R2 Memory Hog

Question

SQL Server 2008 R2 Memory Hog

Viewing 7 posts - 16 through 21 (of 21 total)

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

DKY SSCrazy Points: 2180 More actions · Answer 1

The output of that query shows 2,680. I don't know how to check the others.

Task manager shows that sqlservr.exe is currently consuming 4,266,016k on the "Processes" tab but on the "Performance" tab I show that the server itself is consuming 15.5GB of the 16GB of memory.

When I run those NET STOP commands from the cli

NET STOP "SQL Server Agent (BRKSQL02)"

NET STOP "SQL Server (BRKSQL02)"

The 15.5GB goes down to around 800MB after about one minute (it's a slow decrease which I also find odd, I would have thought it would be immediate).

I then start the services back up again after about 10 minutes of 800MB and it starts using up to the 15.5GB again.

When I run:

SELECT

SERVERPROPERTY('productversion'),

SERVERPROPERTY ('productlevel'),

SERVERPROPERTY ('edition')

I get:

My Product version = 10.50.1600.1

My Product Level = RTM

My Edition = Standard Edition (64-bit)

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

Keith Tate (3/10/2014)

It is true for both cases. When the Lazy Writer task runs it checks the value for Max and Min and adjusts the targets and the notifies the clerks to adjust their memory accordingly.

Sometimes it's not immediate .. This involves lot of factors.

--

SQLBuddy

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

DKY (3/10/2014)
The output of that query shows 2,680. I don't know how to check the others.
Task manager shows that sqlservr.exe is currently consuming 4,266,016k on the "Processes" tab but on the "Performance" tab I show that the server itself is consuming 15.5GB of the 16GB of memory.
When I run those NET STOP commands from the cli
NET STOP "SQL Server Agent (BRKSQL02)"
NET STOP "SQL Server (BRKSQL02)"
The 15.5GB goes down to around 800MB after about one minute (it's a slow decrease which I also find odd, I would have thought it would be immediate).
I then start the services back up again after about 10 minutes of 800MB and it starts using up to the 15.5GB again.
When I run:
SELECT
SERVERPROPERTY('productversion'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
I get:
My Product version = 10.50.1600.1
My Product Level = RTM
My Edition = Standard Edition (64-bit)

This means SQL Server is using 4GB. Something else on the server is using that memory.

In the task Manager, sort the processes by the Memory and see which process is using the most.

--

SQLBuddy

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 4

You could run dbcc memorystatus to check the Target and the Current Committed values, but it looks like the server is only using 4GB.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

DKY SSCrazy Points: 2180 More actions · Answer 5

That's a huge results set, anything specific that I should pull in or is the target and committed in here somewhere?

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 6

Couple of things to look for.

1st Result set:

Available Physical Memory

System physical memory high

System physical memory low

2nd result set:

Target Committed

Current Committed

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

DKY SSCrazy Points: 2180 More actions · Answer 7

Available Physical Memory 523730944

System physical memory high 1

System physical memory low 0

I can't find Target Committed or Current Committed. I have

Committed 524288

Target 52428