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

weird memory usage on sql server Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 12:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
Hi Folks,

I got this weird issue on sql server.

I am running window 2008 r2 enterprise 64 bit with 8 gb of ram and running SQL Server 2008 10.0.4000 64 bit standard edition. The maxium server memory is set at 5 gbs. When I look at task manager the sql server process's working set barely gets anymore than this setting. This is a dedicated sql machine.

Anyway occasionally the avialable memory on the machine gets below 200mb (97% usage). I am unsure why as the sql process working set has barely changed, at the low memory time. The rest of the processes in task manager shown would not make up more the 100-200 mbs. I am guessing it has something to do with the o.s. but using more than 2.5gb for the o.s. seems not right to me.

I did a sql trace and looked at the logical reads. If the problem was caused by a particular query I would have thought that, when the query was finished the memory would be relieved. What I seem to be seeing is Memory does get relieved (back to about 1-1.5 gb avialable memory) but the trace shows no queries that has been run for the duration of the low memory period and also the queries do not show massive logical reads at the time the memory is relieved.

Hope I make sense - please help!



Post #1397589
Posted Tuesday, December 18, 2012 2:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
Don't use Task Manager to check SQL's memory. It can be completely wrong.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1397607
Posted Tuesday, December 18, 2012 5:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 4, 2013 12:43 AM
Points: 10, Visits: 45
The issue is related to SQL SERVER Performance issue for sure

Increasing memory is one of the best ways to improve SQL Server performance

because more data can be cached in memory.

You can check you memory usage by using storage management for SQL related tools also
Post #1397708
Posted Tuesday, December 18, 2012 5:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 22, 2014 6:04 AM
Points: 217, Visits: 525
djordan 4543 (12/18/2012)
Hi Folks,

I got this weird issue on sql server.

I am running window 2008 r2 enterprise 64 bit with 8 gb of ram and running SQL Server 2008 10.0.4000 64 bit standard edition. The maxium server memory is set at 5 gbs. When I look at task manager the sql server process's working set barely gets anymore than this setting. This is a dedicated sql machine.

Anyway occasionally the avialable memory on the machine gets below 200mb (97% usage). I am unsure why as the sql process working set has barely changed, at the low memory time. The rest of the processes in task manager shown would not make up more the 100-200 mbs. I am guessing it has something to do with the o.s. but using more than 2.5gb for the o.s. seems not right to me.

I did a sql trace and looked at the logical reads. If the problem was caused by a particular query I would have thought that, when the query was finished the memory would be relieved. What I seem to be seeing is Memory does get relieved (back to about 1-1.5 gb avialable memory) but the trace shows no queries that has been run for the duration of the low memory period and also the queries do not show massive logical reads at the time the memory is relieved.

Hope I make sense - please help!





Hi,

As a matter of interest, do you have SCOM monitoring your server? Also, has an anti-virus package been deployed on this server?

Anna
Post #1397720
Posted Tuesday, December 18, 2012 6:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 39, Visits: 488
You need to check anti-virus software or get the server guys involved if this is a VM

Regards
Post #1397770
Posted Tuesday, December 18, 2012 4:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
thanks guys,

would perfmon be betterfor checking this?

SCOM is not monitoring this server (wish it was)

Symantec endpoint protection is our av program there is a full scan every monday morning at 5am.

Post #1398104
Posted Tuesday, December 18, 2012 9:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
I have just been monitoring the all processes working set in performance monitor for a few hours. There has been a couple of times where memory has been upto 97% utilization but the working set memory does not seem to be budging.

Is there anything else I can do to diagnose this?

Post #1398145
Posted Tuesday, December 18, 2012 10:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Have you checked the "non buffer pool" memory how much being uhsed there ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1398166
Posted Tuesday, December 18, 2012 10:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 7:14 PM
Points: 56, Visits: 200
thanks - I think I might be getting closer.. how do I check that?
Post #1398169
Posted Tuesday, December 18, 2012 11:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
djordan 4543 (12/18/2012)
thanks - I think I might be getting closer.. how do I check that?


--Bpool stats
select (bpool_committed * 8192)/ (1024*1024) as bpool_committed_mb
, (cast(bpool_commit_target as bigint) * 8192) / (1024*1024) as bpool_target_mb,
(bpool_visible * 8192) / (1024*1024) as bpool_visible_mb
from sys.dm_os_sys_info
go


-- Get me physical RAM installed
-- and size of user VAS
select physical_memory_in_bytes/(1024*1024) as phys_mem_mb,
virtual_memory_in_bytes/(1024*1024) as user_virtual_address_space_size
from sys.dm_os_sys_info
go
--
-- Get me other information about system memory
--
select total_physical_memory_kb/(1024) as phys_mem_mb,
available_physical_memory_kb/(1024) as avail_phys_mem_mb,
system_cache_kb/(1024) as sys_cache_mb,
(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024) as kernel_pool_mb,
total_page_file_kb/(1024) as total_virtual_memory_mb,
available_page_file_kb/(1024) as available_virtual_memory_mb,
system_memory_state_desc
from sys.dm_os_sys_memory
go
-- Get me memory information about SQLSERVR.EXE process
-- GetMemoryProcessInfo() API used for this
-- physical_memory_in_use_kb
select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb,
locked_page_allocations_kb/(1024) as awe_memory_mb,
total_virtual_address_space_kb/(1024) as max_vas_mb,
virtual_address_space_committed_kb/(1024) as sql_committed_mb,
memory_utilization_percentage as working_set_percentage,
virtual_address_space_available_kb/(1024) as vas_available_mb,
process_physical_memory_low as is_there_external_pressure,
process_virtual_memory_low as is_there_vas_pressure
from sys.dm_os_process_memory
go
select * from sys.dm_os_ring_buffers
where ring_buffer_type like 'RING_BUFFER_RESOURCE%'
go
select memory_node_id as node, virtual_address_space_reserved_kb/(1024) as VAS_reserved_mb,
virtual_address_space_committed_kb/(1024) as virtual_committed_mb,
locked_page_allocations_kb/(1024) as locked_pages_mb,
single_pages_kb/(1024) as single_pages_mb,
multi_pages_kb/(1024) as multi_pages_mb,
shared_memory_committed_kb/(1024) as shared_memory_mb
from sys.dm_os_memory_nodes
where memory_node_id != 64
go


with vasummary(Size,reserved,free) as ( select size = vadump.size,
reserved = SUM(case(convert(int, vadump.base) ^ 0) when 0 then 0 else 1 end),
free = SUM(case(convert(int, vadump.base) ^ 0x0) when 0 then 1 else 0 end)
from
(select CONVERT(varbinary, sum(region_size_in_bytes)) as size,
region_allocation_base_address as base
from sys.dm_os_virtual_address_dump
where region_allocation_base_address <> 0x0
group by region_allocation_base_address
UNION(
select CONVERT(varbinary, region_size_in_bytes),
region_allocation_base_address
from sys.dm_os_virtual_address_dump
where region_allocation_base_address = 0x0)
)
as vadump
group by size)


select * from vasummary
go

-- Get me all clerks that take some memory
--
select * from sys.dm_os_memory_clerks
where (single_pages_kb > 0) or (multi_pages_kb > 0)
or (virtual_memory_committed_kb > 0)
go
-- Get me stolen pages
--
select (SUM(single_pages_kb)*1024)/8192 as total_stolen_pages
from sys.dm_os_memory_clerks
go
-- Breakdown clerks with stolen pages
select type, name, sum((single_pages_kb*1024)/8192) as stolen_pages
from sys.dm_os_memory_clerks
where single_pages_kb > 0
group by type, name
order by stolen_pages desc
go
-- Get me the total amount of memory consumed by multi_page consumers
--
select SUM(multi_pages_kb)/1024 as total_multi_pages_mb
from sys.dm_os_memory_clerks
go
-- What about multi_page consumers
--
select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb
from sys.dm_os_memory_clerks
where multi_pages_kb > 0
group by type, name
order by multi_pages_mb desc
go
-- Let's now get the total consumption of virtual allocator
--
select SUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mb
from sys.dm_os_memory_clerks
go
-- Breakdown the clerks who use virtual allocator
--
select type, name, sum(virtual_memory_committed_kb)/1024 as virtual_mem_mb
from sys.dm_os_memory_clerks
where virtual_memory_committed_kb > 0
group by type, name
order by virtual_mem_mb desc
go
-- Is anyone using AWE allocator?
--
select SUM(awe_allocated_kb)/1024 as total_awe_allocated_mb
from sys.dm_os_memory_clerks
go
-- Who is the AWE user?
--
select type, name, sum(awe_allocated_kb)/1024 as awe_allocated_mb
from sys.dm_os_memory_clerks
where awe_allocated_kb > 0
group by type, name
order by awe_allocated_mb desc
go
-- What is the total memory used by the clerks?
--
select (sum(multi_pages_kb)+
SUM(virtual_memory_committed_kb)+
SUM(awe_allocated_kb))/1024
from sys.dm_os_memory_clerks
go
--
-- Does this sync up with what the node thinks?
--
select SUM(virtual_address_space_committed_kb)/1024 as total_node_virtual_memory_mb,
SUM(locked_page_allocations_kb)/1024 as total_awe_memory_mb,
SUM(single_pages_kb)/1024 as total_single_pages_mb,
SUM(multi_pages_kb)/1024 as total_multi_pages_mb
from sys.dm_os_memory_nodes
where memory_node_id != 64
go
--
-- Total memory used by SQL Server through SQLOS memory nodes
-- including DAC node
-- What takes up the rest of the space?
select (SUM(virtual_address_space_committed_kb)+
SUM(locked_page_allocations_kb)+
SUM(multi_pages_kb))/1024 as total_sql_memusage_mb
from sys.dm_os_memory_nodes
go
--
-- Who are the biggest cache stores?
select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024
as cache_size_mb
from sys.dm_os_memory_cache_counters
where type like 'CACHESTORE%'
group by name, type
order by cache_size_mb desc
go
--
-- Who are the biggest user stores?
select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024
as cache_size_mb
from sys.dm_os_memory_cache_counters
where type like 'USERSTORE%'
group by name, type
order by cache_size_mb desc
go
--
-- Who are the biggest object stores?
select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024
as cache_size_mb
from sys.dm_os_memory_clerks
where type like 'OBJECTSTORE%'
group by name, type
order by cache_size_mb desc
go
select mc.type, mo.type from sys.dm_os_memory_clerks mc
join sys.dm_os_memory_objects mo
on mc.page_allocator_address = mo.page_allocator_address
group by mc.type, mo.type
order by mc.type, mo.type
go




-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1398173
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse