March 5, 2010 at 12:14 am
We are facing severe performance probs on one of our prod servers. And we trying to identify the hardware bottleneck.
Our Environment:
Server Version & Edition: Sql 2005 (Enterprise x86)
OS Version & Edition: Windows Server 2003 Std x86
RAM: 4 GB
We have enabled AWE for Sql to use all the available RAM and it had occupied till 3.6 GB of mem. There was heavy disk IO which we strongly feel is due to the insufficient memory.
A strange phenomemon which we observed was, the counter Memory: Pages/Sec is very low when the % Disk Utilization is nearly 100% (BufferCacheHit ratio 80% and page life exp 10s -too low). Is it because we had enabled AWE that the Memory: Pages/Sec is not recording a high value.
In other words, If AWE is enabled, does the Pages/Sec counter actually capture the swapping of pages b/w the buffer pool and the disk? I know that the buffer pool memory under AWE cannot swap pages out to the page file and Sql Server keeps it locked. But then how can I say memory is causing the IO prob coz I'm not able to measure the page swap count b/w buffer and disk.
Any advice on this would be very helpful.
March 5, 2010 at 7:11 am
I don'y know AWE well enough to answer your questions about it directly, I'll let someone more familiar do that. I can however help with the rest of it. First we need some additional information from you:
Config:
CPU: type, speed, number of processors & number of cores per processor.
Disk: How many volumes, type (direct, raid 5, raid 10, etc.)
SS Files: Which disks are your tempdb, major .mdfs, and .ldfs on?
SW: are any other products or apps (of significance) running on the server with SQL Server? If your disks are on SAN/NAS, ask the SAN manager what others servers or apps are sharing the same physical disks with your volumes.
Stats: (from perfmon)
Processor: CPU Util%, CPU Queue
PhysicalDisk (per volume(*): Idle%, Avg Disk Queue Length, Avg Disk secs/Transfer
(*): Note, this it important, do not use the "_Total" from Perfmon, use the individual disk instances stats.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 8, 2010 at 7:50 am
pdchandran (3/5/2010)
We are facing severe performance probs on one of our prod servers. And we trying to identify the hardware bottleneck...%BufferCacheHit ratio 80% and page life exp 10 seconds...but then how can I say memory is causing the IO prob coz I'm not able to measure the page swap count b/w buffer and disk.
You are right that AWE-allocated pages are locked and cannot be paged. The high disk I/O is very probably caused by SQL Server writing modified pages to disk to free up space in the Buffer Pool for new requests for clean pages.
The cache hit ratio of 80% and that appallingly low Page Life Expectancy are two very sound indications that your server needs more memory (or you need to tune your queries / create better indexes...and so on).
If you need to convince someone to spend some money, follow the advice in This Microsoft White Paper. I could go on all day about what things to measure and why, but it's already in that document.
Paul
March 8, 2010 at 10:31 pm
>>We are facing severe performance probs on one of our prod servers.
I never understand posters who make statements like that and then hunt and peck on a forum for days or even weeks before they eventually get in touch with a professional to help them out. There are simply tooooooo many variables and parameters at play here. Get a pro and get done with your problem!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply