Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2005-09-28 : 19:33:10
|
| I'm having an issue that is similar to this one:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52445We've got PAE enabled on our Windows 2003 EE.awe enabled is set to 1max server memory (MB) is set to 4096The user that SQL Server runs as has the Lock pages in Memory local security policy setSo, SQL Server should be using 4GB's of memory, but Task manager is only showing 80-100MB's of memory in use. Things "seem" to be performing OK, but I'm not sure if that's just our fast servers and fast disk subsystem overriding the fact that "nothing" is in physical memory.So, my questions are:1. Is there a more accurate way to see how much memory SQL server is actually using? 2. Is there a test I can do to see if I'm actually using all of the physical memory I've got configured? I tried to run some big nasty queries, but task manager showed little or no gain in memory utilization for SQL Server.3. Do I have something setup wrong?Thanks!Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-28 : 20:43:30
|
| To see how much SQL Server is using and how much it wants to use, you use SQLServer:MemoryManager\Total Server Memory(KB) and SQLServer:MemoryManager\Target Server Memory(KB) Performance Monitor counters. Total is how much it is currently using, target is what it wants to use. These numbers should be equal. Total should match what Task Manager is showing.All that being said, I've noticed the same thing as you. Since being on Win2k3, it appears we are using less memory. Nothing in the SQL Server config changed or in the database, yet it is showing a significant drop in memory utilization. I don't know what to think. Is it that we just don't need as much or are we actually using more than it's reporting? I heard from a MS employee who specializes in SQL Server that you get a 15% performance boost on Win2k3 when nothing else has changed. Perhaps the boost means it is using memory more efficiently.Tara |
 |
|
|
ashish459
Starting Member
2 Posts |
Posted - 2006-12-21 : 07:48:26
|
| The answers of your questions is very simpleOpen system performance monitor and monitor following counters • To help identify potential CPU performance issues, the Windows 2000 System Object: % Total Processor Time counter measures the average of all the CPUs in your server. This is the key counter to watch for CPU utilization. If this counter exceeds 80% for continuous periods (over 10 minutes or so), then you may have a CPU bottleneck. If you do have a bottleneck, then potential solutions include reducing the workload on SQL Server, getting faster CPUs, or getting more CPUs. • To help identify potential memory performance issues, the Windows 2000 Memory Object: Pages/Sec counter is important to watch. It measures the number of pages per second that are paged out of memory to disk, or paged into memory from disk. Assuming that SQL Server is the only major application running on your server, then this figure should average nearly zero, except for occasional spikes, which are normal. If this counter exceeds 0 for continuous periods (over 10 minutes or so), then you may have a paging-related problem. These can occur if you are running programs other than SQL Server on the server, or if you have turned off dynamic memory configuration on SQL Server. • To help identify potential I/O performance problems, the Windows 2000 PhysicalDisk Object: Avg. Disk Queue Length counter is critical to monitor. If the Avg. Disk Queue Length exceeds 2 for continuous periods (over 10 minutes or so) for each disk drive in an array, then you probably have an I/O bottleneck for that array. Ways to remove this bottleneck include: adding drives to an array (if you can), getting faster drives, adding cache memory to the controller card (if you can), using a different version of RAID, getting a faster controller, or reducing the workload on SQL Server. • To help identify if your server has enough physical RAM, the SQL Server 2000 Buffer Manager Object: Buffer Cache Hit Ratio counter needs to be monitored. This counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. In OLTP applications, this ratio should exceed 90%. If it doesn't, then you need to add more RAM to your server to increase performance, or reduce the workload on SQL Server. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-12-21 : 12:21:39
|
| I have also noticed that memory usage is not accurately displayed in task manager when using AWE/PAE memory. It appears that task manager is not AWE aware.You can use the counters Tara suggests, but both target and total will be the same value since dynamic memory allocation is disabled when you turn on AWE in SQL Server.Probably the best counter to monitor is SQLServer:Buffer Manager.-ec |
|
|
|
|
|
|
|
|
Windows 2003 PAE issue