InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 12:05:49 GMT20http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxAnother link [url]http://sqlserverplanet.com/troubleshooting/sql-server-slowness[/url]Mon, 07 Jan 2013 23:30:39 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxok - I appreciate you assistance anyway:-)Mon, 07 Jan 2013 23:18:47 GMTdjordan 4543http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspx[quote][b]djordan 4543 (1/7/2013)[/b][hr]with the script provided - is there anything in particular to look for . I ran the script during high memory usage and low memory usage and I don't see anything of any particular concern (I don't profess to understand everything the script is doing though).[/quote] i qam sorry there is no straight forward bread for this. you need to see the result of each query carefully and before result see "the commented line above of every query"then you need to dig into the issues. Are you still seeing continous memory usage ( any particular time period or pattern)? have you seen the sql error log ? or even the system log ?Have you seen the tempdb usage ? do you have dedicated or separate disk for it ? and any kind of IO high usage ? see this link for memory [url]http://blogs.msdn.com/b/karthick_pk/archive/2012/06/15/troubleshooting-sql-server-memory.aspx[/url]Mon, 07 Jan 2013 23:16:02 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxwith the script provided - is there anything in particular to look for . I ran the script during high memory usage and low memory usage and I don't see anything of any particular concern (I don't profess to understand everything the script is doing though).I thought perhap sql plans in the memory clerks may have been high (comparing values between high memory times and low memory times) but I appeared to be wrong here.Mon, 07 Jan 2013 18:32:16 GMTdjordan 4543http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxyep used perfmon memory.avialable_memory and process.workingset for all the processes - did not see anything useful from that.(i.e nothing spike in the working sets when avilable memory reduced dramatically).I thought I might setup some monitoring with those dmvs bhuvnesh sent through.. gonna try now!djThu, 20 Dec 2012 17:56:58 GMTdjordan 4543http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxJust to reiterate here...[quote][b]GilaMonster (12/18/2012)[/b][hr]Don't use Task Manager to check SQL's memory. It can be completely wrong.[/quote]Use Perfmon with either the SQL Server memory counters and/or the process-specific counters.Wed, 19 Dec 2012 00:06:44 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxthanks - i'll c what I can do with that.Tue, 18 Dec 2012 23:12:30 GMTdjordan 4543http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspx[quote][b]djordan 4543 (12/18/2012)[/b][hr]thanks - I think I might be getting closer.. how do I check that?[/quote][code="sql"]--Bpool statsselect (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_mbfrom sys.dm_os_sys_infogo -- Get me physical RAM installed-- and size of user VASselect physical_memory_in_bytes/(1024*1024) as phys_mem_mb, virtual_memory_in_bytes/(1024*1024) as user_virtual_address_space_sizefrom sys.dm_os_sys_infogo---- 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_descfrom sys.dm_os_sys_memorygo-- Get me memory information about SQLSERVR.EXE process-- GetMemoryProcessInfo() API used for this-- physical_memory_in_use_kbselect 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_pressurefrom sys.dm_os_process_memorygoselect * from sys.dm_os_ring_buffers where ring_buffer_type like 'RING_BUFFER_RESOURCE%'goselect 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_mbfrom sys.dm_os_memory_nodeswhere memory_node_id != 64go 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 basefrom sys.dm_os_virtual_address_dumpwhere region_allocation_base_address <> 0x0group by region_allocation_base_addressUNION(select CONVERT(varbinary, region_size_in_bytes),region_allocation_base_addressfrom sys.dm_os_virtual_address_dumpwhere region_allocation_base_address = 0x0))as vadumpgroup by size) select * from vasummarygo -- Get me all clerks that take some memory--select * from sys.dm_os_memory_clerkswhere (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_pagesfrom sys.dm_os_memory_clerksgo-- Breakdown clerks with stolen pagesselect type, name, sum((single_pages_kb*1024)/8192) as stolen_pagesfrom sys.dm_os_memory_clerkswhere single_pages_kb > 0group by type, nameorder by stolen_pages descgo-- Get me the total amount of memory consumed by multi_page consumers--select SUM(multi_pages_kb)/1024 as total_multi_pages_mbfrom sys.dm_os_memory_clerksgo-- What about multi_page consumers--select type, name, sum(multi_pages_kb)/1024 as multi_pages_mbfrom sys.dm_os_memory_clerkswhere multi_pages_kb > 0group by type, nameorder by multi_pages_mb descgo-- Let's now get the total consumption of virtual allocator--select SUM(virtual_memory_committed_kb)/1024 as total_virtual_mem_mbfrom sys.dm_os_memory_clerksgo-- Breakdown the clerks who use virtual allocator--select type, name, sum(virtual_memory_committed_kb)/1024 as virtual_mem_mbfrom sys.dm_os_memory_clerkswhere virtual_memory_committed_kb > 0group by type, nameorder by virtual_mem_mb descgo-- Is anyone using AWE allocator?--select SUM(awe_allocated_kb)/1024 as total_awe_allocated_mbfrom sys.dm_os_memory_clerksgo-- Who is the AWE user?--select type, name, sum(awe_allocated_kb)/1024 as awe_allocated_mbfrom sys.dm_os_memory_clerkswhere awe_allocated_kb > 0group by type, nameorder by awe_allocated_mb descgo-- What is the total memory used by the clerks?--select (sum(multi_pages_kb)+SUM(virtual_memory_committed_kb)+SUM(awe_allocated_kb))/1024from sys.dm_os_memory_clerksgo---- 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_mbfrom sys.dm_os_memory_nodeswhere memory_node_id != 64go---- 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_mbfrom sys.dm_os_memory_nodesgo-- -- Who are the biggest cache stores?select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024 as cache_size_mbfrom sys.dm_os_memory_cache_counterswhere type like 'CACHESTORE%'group by name, typeorder by cache_size_mb descgo---- Who are the biggest user stores?select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024 as cache_size_mbfrom sys.dm_os_memory_cache_counterswhere type like 'USERSTORE%'group by name, typeorder by cache_size_mb descgo---- Who are the biggest object stores?select name, type, (SUM(single_pages_kb)+SUM(multi_pages_kb))/1024 as cache_size_mbfrom sys.dm_os_memory_clerkswhere type like 'OBJECTSTORE%'group by name, typeorder by cache_size_mb descgoselect mc.type, mo.type from sys.dm_os_memory_clerks mcjoin sys.dm_os_memory_objects moon mc.page_allocator_address = mo.page_allocator_addressgroup by mc.type, mo.typeorder by mc.type, mo.typego[/code]Tue, 18 Dec 2012 23:02:31 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxthanks - I think I might be getting closer.. how do I check that?Tue, 18 Dec 2012 22:47:16 GMTdjordan 4543http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxHave you checked the "non buffer pool" memory how much being uhsed there ?Tue, 18 Dec 2012 22:44:11 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxI 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?Tue, 18 Dec 2012 21:36:16 GMTdjordan 4543http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxthanks 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.Tue, 18 Dec 2012 16:40:15 GMTdjordan 4543http://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxYou need to check anti-virus software or get the server guys involved if this is a VMRegardsTue, 18 Dec 2012 06:48:19 GMTNchax Phokuhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspx[quote][b]djordan 4543 (12/18/2012)[/b][hr]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![/quote]Hi,As a matter of interest, do you have SCOM monitoring your server? Also, has an anti-virus package been deployed on this server?AnnaTue, 18 Dec 2012 05:36:51 GMTAeternahttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxThe issue is related to SQL SERVER Performance issue for sure Increasing memory is one of the best ways to improve SQL Server performancebecause more data can be cached in memory.You can check you memory usage by using storage management for SQL related tools alsoTue, 18 Dec 2012 05:08:17 GMTitsmemegamindhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxDon't use Task Manager to check SQL's memory. It can be completely wrong.Tue, 18 Dec 2012 02:03:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1397589-391-1.aspxHi 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!Tue, 18 Dec 2012 00:42:21 GMTdjordan 4543