Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

share|improve this answer
    
The configuration is already in X64 and AWE is not enabled.Am more concerned about the stolen pages value.can please help me out in this? –  user18576 May 24 '10 at 12:54

I'm assuming you've configured SQL to use AWE. If so, then it's normal for SQL to allocate all the memory and release memory to other processes as needed. This article explains how AWE and allocated memory work in SQL Server.

http://technet.microsoft.com/en-us/library/ms190673.aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.