February 18, 2011 at 4:13 pm
I'm new to my current environment so forgive any vague description or lack of details. While I do have a specific problem to address, my boss is happy with the status quo since this production OLTP is working and business is floating along.
What I know:
Two quad core processors
8GB memory
five physical disks in a raid 5 array.
SQL Server Configurations (from DBArtisan Report)
affinity I/O mask value 0 run 0 min -2,147,483,648 max 2,147,483,647 Dynamic Yes
affinity mask value 0 run 0 min -2,147,483,648 max 2,147,483,647 Dynamic No
min server memory (MB) value 4096 run 4096 min 0 max 2,147,483,647 Dynamic No
max server memory (MB) value 2,147,483,647 run 2,147,483,647 min 16 max 2,147,483,647 Dynamic No
USE master
go
EXEC sp_configure 'affinity I/O mask',0
go
EXEC sp_configure 'affinity mask',0
go
EXEC sp_configure 'max server memory (MB)',2147483647
go
EXEC sp_configure 'max worker threads',0
go
EXEC sp_configure 'min memory per query (KB)',1024
go
EXEC sp_configure 'min server memory (MB)',4096
go
Task Manager
w3wp.exe as utilizing the most memory @ 122,272 K
sqlserver.exe is 113,016 K
CPU usage is 1% but he PF Usage is 7.95 GB (<- concern?)
Perf Mon log (file attached)
\\NG-SQL\Memory\Available MBytes
\\NG-SQL\Memory\Pages/sec
\\NG-SQL\Process(sqlservr)\Page Faults/sec
\\NG-SQL\Process(sqlservr)\Working Set
\\NG-SQL\SQLServer:Buffer Manager\Buffer cache hit ratio
\\NG-SQL\SQLServer:Buffer Manager\Page reads/sec
\\NG-SQL\SQLServer:Buffer Manager\Page writes/sec
\\NG-SQL\SQLServer:Buffer Manager\Total pages
\\NG-SQL\SQLServer:Memory Manager\Total Server Memory (KB)
The Microsoft Official Course book 2780B for SQL 2005 lists these counters as indicators for isolating disk and memory bottlenecks. I have limited experience with this but none of the counters, accourding to the course book reveal a bottleneck.
DBArtisans Performance analyst reports (three)
1) File I/O detail
2) Database I/O.
3) System Waits
Windows 2003 Server - Computer Management->Advanced ->Performance->Settings
This is my biggest inquery of the forum. What are the MS recommendations for SQL for these server settings?
Our current configurations are:
'Processor scheduling' set to 'Background services as opposed to 'Programs'
'Memory Usage set to 'System Cache' as opposed to 'Programs'
Virtual Memory is set to 4546 MB
Again, Task manager displays the 'PF Usage' as almost 8 GB.
February 18, 2011 at 6:02 pm
...While I do have a specific problem to address...
What exactly would this specific problem be?
I cannot find it in your system description...
February 28, 2011 at 1:52 pm
I'm sorry I didn't mention that clearly. Task manager is showing 7.85 GB page file usage and I can't figure what process is causing it.
March 1, 2011 at 11:32 am
I am not an expert on the topic but I have seen this a few times here. Something is causing a memory leak on the server. In my experience if this SQL Server is used heavily it will eventually become quite sluggish and you will be forced to reboot. A couple times in the past we sort of traced it back to Anti-Virus. If you go into Task Manager and go to the View at the top and hit the 'select columns' and start adding some of the memory columns you might be able to track down what has it.... sometimes this won't even help.
March 2, 2011 at 10:54 am
I see... a memory leak will cause the system to page. This makes sense. Thanks
March 3, 2011 at 6:55 am
I have had the same problem with my SQL Server 2005 Standard Edition. We have contacted Microsoft and finally Microsoft said it is an issue with memory leak for SQL Server 2005 Standard Edition only. However, if you have an Enterprise Edition, there is no problem. What edition do you have?
**I am not sure if they fixed it in SQL Server 2008 Standard Edition or not....
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 3, 2011 at 7:54 am
If performance is "sluggish" thread topic... you would need to provide a bit more detail about how the SQL server DBs are spread across the drives, what else is running on the server (W3WP.exe is a worker process task, IIS perhaps?). Is it just 1 RAID5 drive for the entire server (IE. OS, SQL, DBs, apps, etc)?
Also the CPU usage may only be 1% now, but what about when performance is sluggish?
Are running AWE/PAE switches (to get above the addressable memory limits)? If so, I believe task manager will not reflect memory usage correctly. What are the memory figures from PerfMon?
Do you know what is causing the sluggish performance? as that is the best place to start...
You should consider putting a MAX memory setting into SQL Server.
August 4, 2011 at 10:35 am
Have you found out what's taking up PF uasag yet? If not, check the buffer cache size.
What I have found is that once you configure any value for min or max server memory on an OLTP SQL Server, the memory manager will eventually attempt to load data pages into the buffer pool (aka buffer cache) to the tune of total memory available to SQL Server. To determine the total memory available to SQL, query
sys.dm_os_sys_info
and check figures for (1) bpool_committed and (2) bpool_commit_target. (1) is the amount to buffers being used in the buffer pool, and (2) represents the amount the buffer pool aim to acquire. In your case, if (1) is about or equal to (2) then the buffer pool is what is taking up the PF usage. You can then move on to finding out how to manage how much memory to allow for the Buffer Pool.
I find this is the behaviour on 64-bit Windows 2003 server, 64-bit SQL Server. Try leaving the memory settings as the default and see how your PF usage appears.
August 5, 2011 at 7:26 am
Two thoughts from a probable newb about this (you've been warned):
1) w3wp.exe is an IIS process. Why would you be running IIS on a SQL server? Security and performance land mines for you. If it's chewing up RAM, then there's less left for OS and SQL.
2) I'm not sure what Task Manager is actually reporting there for PF usage. I've got a SQL server that currently shows a flatline 2.69 Gb Page File usage right now, but the swap file is fixed at 2046 Mb! If I run Perfmon and add the % Swapfile counter, it's showing a flatline at 4.3% (yep, percent, not Gb) usage.
This link suggests that what PF reports is not what you'd think it is.
Rich
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply