Sql-server – High Sqlservr.exe Memory Usage

memorysql server

I have a problem with sqlservr.exe (version 2008). It use a more memory. I checked on windows taskbar manager, sqlservr.exe usage ( Mem usage – 8GB Ram). I dont know how can I fix it.Got the following metrics of the server using Perfmon:

 SQLServer:Buffer Manager                                                                                                       
Buffer cache hit ratio                                                                                                          
13
SQLServer:Buffer Manager                                                                                                       
Page lookups/sec                                                                                                                
46026128096
SQLServer:Buffer Manager                                                                                                       
Free pages                                                                                                                      
129295
SQLServer:Buffer Manager                                                                                                       
Total pages                                                                                                                     
997309
SQLServer:Buffer Manager                                                                                                       
Target pages                                                                                                                   
1053560
SQLServer:Buffer Manager                                                                                                        
Database pages                                                                                                                 
484117
SQLServer:Buffer Manager                                                                                                        
Reserved pages                                                                                                                 
0
SQLServer:Buffer Manager                                                                                                        
Stolen pages                                                                                                                   
383897
SQLServer:Buffer Manager                                                                                                        
Lazy writes/sec                                                                                                                
384369
SQLServer:Buffer Manager                                                                                                        
Readahead pages/sec                                                                                                            
69315446
SQLServer:Buffer Manager                                                                                                        
Page reads/sec                                                                                                                 
71280353
SQLServer:Buffer Manager                                                                                                       
Page writes/sec                                                                                                                 
12408371
SQLServer:Buffer Manager                                                                                                       
Checkpoint pages/sec                                                                                                            
7053801
SQLServer:Buffer Manager                                                                                                       
Page life expectancy                                                                                                            
735262
SQLServer:General Statistics                                                                                                   
Active Temp Tables                                                                                                              
161
SQLServer:General Statistics                                                                                                   
Temp Tables Creation Rate                                                                                                       
3131845
SQLServer:General Statistics                                                                                                   
Logins/sec                                                                                                                      
2336011
SQLServer:General Statistics                                                                                                   
Logouts/sec                                                                                                                     
2335984
SQLServer:General Statistics                                                                                                   
User Connections                                                                                                               
27
SQLServer:General Statistics                                                                                                    
Transactions                                                                                                                   
0
SQLServer:Access Methods                                                                                                        
Full Scans/sec                                                                                                                 
34422821
SQLServer:Access Methods                                                                                                        
Range Scans/sec                                                                                                                
2027247756
SQLServer:Access Methods                                                                                                        
Workfiles Created/sec                                                                                                          
49771600
SQLServer:Access Methods                                                                                                        
Worktables Created/sec                                                                                                         
28205828
SQLServer:Access Methods                                                                                                        
Index Searches/sec                                                                                                             
4890715219
SQLServer:Access Methods                                                                                                       
FreeSpace Scans/sec                                                                                                             
21178928
SQLServer:Access Methods                                                                                                       
FreeSpace Page Fetches/sec                                                                                                      
21226653
SQLServer:Access Methods                                                                                                       
Pages Allocated/sec                                                                                                             
41483279
SQLServer:Access Methods                                                                                                       
Extents Allocated/sec                                                                                                           
4743504
SQLServer:Access Methods                                                                                                       
Extent Deallocations/sec                                                                                                        
4806606
SQLServer:Access Methods                                                                                                       
Page Deallocations/sec                                                                                                          
41419137
SQLServer:Access Methods                                                                                                       
Page Splits/sec                                                                                                                 
23834799
SQLServer:Memory Manager                                                                                                       
SQL Cache Memory (KB)                                                                                                          
29160
SQLServer:Memory Manager                                                                                                        
Target Server Memory (KB)                                                                                                      
8428480
SQLServer:Memory Manager                                                                                                        
Total Server Memory (KB)                                                                                                       
7978472

Some body could help me please.And I really want to know the cause for the above.

Best Answer

The counter "Target Server Memory", 8428480 KB is how much SQL Server would like to use You only have 7978472KB allocated to the instance ("Total Server Memory")

I suggest you add more memory or move to x64 if you haven't already

However, if you PAE and AWE enabled you must set max server memory on 32 bit. Otherwise it grabs the lot except 256 MB.

Finally, SQL Server is designed to use memory. Another SF question about it

Related Solutions

VMware ESX virtual machine, Windows 2008 Server R2, memory full but process total nowhere near

The performance of a host will degrade instantly whenever "thrashing" occurs - continuous swapping in and out of memory pages under tight memory conditions.

You might have a memory leak somewhere. If the task manager does not show excessive memory usage for a single process (BTW, which value were you looking at? The task manager typically shows private bytes, though you should be looking for the "working set" for current physical memory usage), a kernel module/driver might be another possible candidate. Take a look at Process Explorer's memory statistics, especially the kernel memory usage - they will be more detailed and might get you a step further to resolution.

Apache consuming too much CPU and memory

Other problem is memory usage. When a process is created, 80M is allocated for apache2. I think is too much.

Is that real or virtual memory? Honestly, it's not very much; more to the point, you should focus on fixing things that are causing problems, not just things that you "think" should be different.

If you want Apache processes to take up less memory, you should disable modules (as each one is more code that needs to be in memory). But if you need all the modules you've got enabled, then, well, that's that.

One approach I've used when administrating a memory-constrained machine was to move certain tasks out of Apache and into other servers, so I could tune them separately.

But a far simpler approach is to change

MaxClients           120

to something more reasonable for your workload:

If we consider the rewrite accesses and the main site traffic, we have about 70 requests per minute. Right now, we have 33 incoming connections.

I'll get back to that momentarily, but if you're only dealing with 33 concurrent requests, you don't need 120 workers!

MaxClients           40

And you should probably tune down MinSpareServers and MaxSpareServer to something like 5 and 10, respectively. There's no need to have 30 workers sitting around doing nothing.

Now, getting back to

If we consider the rewrite accesses and the main site traffic, we have about 70 requests per minute. Right now, we have 33 incoming connections.

If you've got 33 concurrent requests, but you're only doing 70 a minute, there are a couple of possibilities:

Your requests are taking around 30 seconds each to serve!
Your request rate isn't very stable, and most of the minute you're doing nothing.

If #1 is the case, I don't really know how to help - something is incredibly wrong, so wrong I don't even know where to tell you to start looking.

If it's #2, my guess is you're serving all your static assets (images, js, css, fonts) from your server. It's best to put these on a CDN, but if you really can't do that, you can set super-long cache times on them and turn Varnish back on. If you're using Apache processes with PHP and a whole host of other things just to serve static files, you're wasting resources - do that with something simpler!

My problem is that CPU (all cores) have a high load. Most of time, hitting between 90% and 100% load! The offending process is the apache2.

Is this a constant number, or only when you're serving requests?

How does disk I/O look (iostat -mhx 2)? What is MySQL doing (show processlist;)?

Your server is vastly overpowered for what you've described. This is good news, because it means you should be able to fix this problem.

Best Answer

Related Solutions

VMware ESX virtual machine, Windows 2008 Server R2, memory full but process total nowhere near

Apache consuming too much CPU and memory

Related Topic