Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server 2008 R2 Memory Hog Expand / Collapse
Author
Message
Posted Monday, March 10, 2014 10:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:53 AM
Points: 597, Visits: 937
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
Post #1549383
Posted Monday, March 10, 2014 11:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 16, 2014 7:27 PM
Points: 20, Visits: 60
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?
Post #1549388
Posted Monday, March 10, 2014 11:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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



Post #1549395
Posted Monday, March 10, 2014 11:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1549397
Posted Monday, March 10, 2014 11:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:53 AM
Points: 597, Visits: 937
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
Post #1549398
Posted Monday, March 10, 2014 11:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 16, 2014 7:27 PM
Points: 20, Visits: 60
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)
Post #1549406
Posted Monday, March 10, 2014 11:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1549408
Posted Monday, March 10, 2014 11:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1549410
Posted Monday, March 10, 2014 11:49 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:53 AM
Points: 597, Visits: 937
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
Post #1549413
Posted Monday, March 10, 2014 11:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 16, 2014 7:27 PM
Points: 20, Visits: 60
That's a huge results set, anything specific that I should pull in or is the target and committed in here somewhere?
Post #1549421
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse