SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 R2 Memory Hog


SQL Server 2008 R2 Memory Hog

Author
Message
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1024 Visits: 979
Are there other instances installed on this box?



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
DKY
DKY
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 158
Actually, yes and no. I inherited this install without the password for the initial instance. I've since stopped the service (a long time ago) and am unable to log in to completely remove the instance but it shouldn't be taking up any memory if it's not running, right?
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2372 Visits: 2243
What are your Memory Settings right now ? How much memory is SQL Server consuming in the Task Manager ?

What is the SQL Server Build number ?

Check the o/p of this query ..

select [Memory Used KB] = (pages_allocated_count * page_size_in_bytes)/1024 from sys.dm_os_memory_objects where type = 'MEMOBJ_RESOURCE'



Check the Values of these counters

Total Server Memory
Target Server Memory
Available Mbytes

Performance object: Process
Counter: Private Bytes
Instance: sqlservr

Performance object: Process
Counter: Working Set
Instance: sqlservr


--
SQLBuddy
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2372 Visits: 2243
DKY (3/10/2014)
Actually, yes and no. I inherited this install without the password for the initial instance. I've since stopped the service (a long time ago) and am unable to log in to completely remove the instance but it shouldn't be taking up any memory if it's not running, right?


Right. When did you stop the instance and when did you start getting this issue ?

--
SQLBuddy
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1024 Visits: 979
sqlbuddy123 (3/10/2014)
GilaMonster (3/10/2014)
sqlbuddy123 (3/10/2014)
And did you restart the SQL server after you changed the Max Memory to 4 GB ? This is to release excessive memory grabbed by SQL.


Max server memory does not require a restart. It is effective immediately.


Yeah, I know. This is true when going from lower value to higher value and not necessarily Vice Versa. That's why I mentioned
This is to release excessive memory grabbed


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.



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
DKY
DKY
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 158
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
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2372 Visits: 2243
Keith Tate (3/10/2014)
[quote]

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
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2372 Visits: 2243
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
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1024 Visits: 979
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
DKY
DKY
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 158
That's a huge results set, anything specific that I should pull in or is the target and committed in here somewhere?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search