LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1880
Members Online:
1
Guests Online:
102

Total Post History
Posts:
81452
Topics:
18717

7-Day Post History
New Posts:
1
New Topics:
1
Active Topics:
1

Our newest member
kwanail87

Other

FAQ
All times are UTC [ DST ]

high memory usage

Please wait...

high memory usage

Postby Thirster42 on Fri Aug 06, 2010 3:17 pm

I've got a development server, Windows Server 2008 standard 64 bit with 4 gb of ram, with SQL Server 2008 SP1 installed. On this server the database process ends up consuming 2+gb of ram (instance is set to 3gb) and causing the server to slow down. This server is only for one development of one application. My concern is that this behavior will copy over to production with over 100 users accessing the application. Is there any way to tell what's taking up the memory? How can i see if there are any processes in SQL Server that are still running?
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: high memory usage

Postby SQLDenis on Fri Aug 06, 2010 3:22 pm

SQL Server will use and hold as much RAM as it can get, it is designed to work that way..getting data from RAM is much faster than getting it from Disk


See also The SQL Server Memory Leak Confusion

Set the max memory limit if you don't want SQL Server to use up all the memory from the server. Or don't set the /3GB flag that way it will only use 2GB max
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: high memory usage

Postby SQLDenis on Fri Aug 06, 2010 3:24 pm

sp_who2 will give you all the processes running

  1. EXEC sp_who2
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: high memory usage

Postby SQLDenis on Fri Aug 06, 2010 3:26 pm

BTW just keep in mind..if data is read in RAM it will NOT release it..so it doesn't matter if the process is running anymore or not

you could try running

  1. DBCC FREEPROCCACHE
  2. DBCC DROPCLEANBUFFERS


but now it will have to go to disk to read the data
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: high memory usage

Postby onpnt on Fri Aug 06, 2010 3:37 pm

Yeah, I have to say this might not even be a problem and usually isn't.

Do you see memory pressure entries in teh SQL Server logs or direct performance problems on the SQL Server?

what do you get from this?
  1. SELECT TOP 10 *
  2. FROM sys.dm_os_wait_stats
  3. WHERE wait_time_ms > 0 -- remove zero wait_time
  4.   AND wait_type NOT IN -- filter out additional irrelevant waits
  5. ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
  6.   'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  7.   'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
  8.   'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
  9.   'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TRANSMITTER',
  10.   'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
  11.   'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT'
  12.   )
  13. ORDER BY wait_time_ms DESC
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: high memory usage

Postby Thirster42 on Fri Aug 06, 2010 3:39 pm

onpnt wrote:Yeah, I have to say this might not even be a problem and usually isn't.

Do you see memory pressure entries in teh SQL Server logs or direct performance problems on the SQL Server?

what do you get from this?
  1. SELECT TOP 10 *
  2. FROM sys.dm_os_wait_stats
  3. WHERE wait_time_ms > 0 -- remove zero wait_time
  4.   AND wait_type NOT IN -- filter out additional irrelevant waits
  5. ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
  6.   'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  7.   'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
  8.   'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
  9.   'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TRANSMITTER',
  10.   'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
  11.   'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT'
  12.   )
  13. ORDER BY wait_time_ms DESC


wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
BACKUPBUFFER 384770 1014924 1105 19676
OLEDB 1556963 934459 16875 0
CXPACKET 21976 888872 15904 31224
ASYNC_IO_COMPLETION 379 861229 57258 34
BACKUPIO 125588 855915 1996 9182
PAGEIOLATCH_SH 59684 403608 473 7043
BACKUPTHREAD 2479 183032 1996 182
WRITELOG 33107 122931 10180 1761
LATCH_EX 365137 91767 227 25852
SOS_SCHEDULER_YIELD 2952671 43852 206 35806
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: high memory usage

Postby Kermit on Fri Aug 06, 2010 3:50 pm

What operating system are you running SQL Server on?

Windows 2003 and especially Windows 2008 Servers are a royal pain in the butt as I have found out.

There is a mechanism in the OS that causes problems with SQL 2008 x64 Servers aka the Buffer Pools.

Have a look at these links, I know I had to play around with some settings to get my servers to behave (they are all configured as Windows 2008 x64 OS, SQL Server 2008 Enterprise x64, 12GB main memory).

http://support.microsoft.com/kb/918483
http://sqlblog.com/blogs/jonathan_kehay ... erver.aspx

I will trying and track down my notes covering what I did for a fix and post em here soon.
MotherFbleeper
User avatar
Kermit
LTD Admin
LTD Admin
LTD Bronze - Rating: 125LTD Bronze - Rating: 125LTD Bronze - Rating: 125
 
Posts: 571
Joined: Thu Oct 11, 2007 11:14 am
Location: United Kingdom
Unrated

Re: high memory usage

Postby onpnt on Fri Aug 06, 2010 3:50 pm

The CXPACKET + SOS_SCHEDULER_YIELD waits might indicate you have CPU issues due to parallelism. I express might because they accumulate over time but seeing them probably means the rpoblem exists. This is primarily an OLTP database server?

What do you have for MAXDOP and threashold settings? you can get those from advanced section in the properties of the SQL Server
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: high memory usage

Postby Thirster42 on Fri Aug 06, 2010 6:04 pm

onpnt wrote:The CXPACKET + SOS_SCHEDULER_YIELD waits might indicate you have CPU issues due to parallelism. I express might because they accumulate over time but seeing them probably means the rpoblem exists. This is primarily an OLTP database server?

What do you have for MAXDOP and threashold settings? you can get those from advanced section in the properties of the SQL Server


yes it's an oltp server... for one application even.

are these the settings you want to see?

server settings.png
You do not have the required permissions to view the files attached to this post.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: high memory usage

Postby onpnt on Fri Aug 06, 2010 7:36 pm

Is SQL Server the only thing installed on the server?

Alter MAXDOP to 2 and cost threashold for par. to 25
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: high memory usage

Postby Thirster42 on Fri Aug 06, 2010 8:03 pm

onpnt wrote:Is SQL Server the only thing installed on the server?

Alter MAXDOP to 2 and cost threashold for par. to 25


i believe so.

Where do i modify maxdrop? I am not seeing it in the advanced options.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: high memory usage

Postby SQLDenis on Fri Aug 06, 2010 8:10 pm

  1. EXEC sys.SP_CONFIGURE N'max degree of parallelism', N'2'
  2. GO
  3. RECONFIGURE WITH OVERRIDE
  4. GO
  5.  



  1. EXEC sys.SP_CONFIGURE N'cost threshold for parallelism', N'25'
  2. GO
  3. RECONFIGURE WITH OVERRIDE
  4. GO
  5.  
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: high memory usage

Postby SQLDenis on Fri Aug 06, 2010 8:14 pm

Thirster42 wrote:
onpnt wrote:Is SQL Server the only thing installed on the server?

Alter MAXDOP to 2 and cost threashold for par. to 25


i believe so.

Where do i modify maxdrop? I am not seeing it in the advanced options.


in the pic you gave us it is the Max Degree of Parallelism setting, people tend to say MAXDOP because it is an option for a query

example

  1. SELECT *
  2. FROM Bla
  3. OPTION(MAXDOP 1)


This will only affect the query and no other queries, same like NOLOCK, FAST 1 etc etc
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: high memory usage

Postby onpnt on Fri Aug 06, 2010 9:18 pm

Sorry about that. Been jumping back and forth today. Thanks for getting the sp_configure up Denis
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated