AWE Problems? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

AWE Problems?

Sorry if people see this more than once, cross posted a couple of different forums: I have been examining a Windows 2000 advanced server with 8 gig of memory, running SQL Server 2000 EE. Before I got there, they had some issues with the processor pegging, forcing a reboot. I examined it for several weeks, and it appeared generally healthy. 10 days ago we scheduled a reboot to turn on AWE support (via /3GB /PAE switch in the boot.ini)- a Friday. It came back up and claimed all 7 gig we allotted to SQL Server, just as the documentation indicated it should. It ran well the rest of the day. On the following Monday (I was not at the office), there was another occurrence of the pegged CPU and they shut it down (not very cleanly). Since then, I have been getting periodic integrity problems with five or six of the 15 databases on the server. By Friday night, all the databases tested consistent via DBCC CHECKDB (had to recreate some tables). Monday morning three had consistency issues. No indications of hardware disk or memory issues in the log or through their server monitoring software (is dell- OpenManage or something). The software vendor whose software the database servers supports had opened a microsoft support incident. Among other things, they recommended hotfixes to support AWE: KB838647 Access violations when you use the /PAE switch in Windows 2000
KB838765 You may notice unpredictable behavior on a multiprocessor computer that is running SQL Server 2000 and has the physical Addressing Extensions (PAE) specification enabled. Couple Questions:
1-If anyone follows up on these KBs, they are pretty intense. I have worked with AWE enabled systems, but never instituted one, previously. Is this generally working technology, in people#%92s views? Has anyone had issues with turning it on?
2-I am starting wonder if the continuing consistency errors are a result of the bad shutdown a week ago, or if the AWE change itself could be causing it. Thoughts? Can consistency issues not appear to DBCC CheckDB, CheckAlloc, etc, but exist nevertheless?
3-I had increased the max worker threads to over 600, per a recommendation on, I believe. Another recommendation Microsoft had was to reduce this to the default (255) because this can cause pressure on the ‘Mem-to-Leave#%92 area of memory. Does anyone have any information on this? Guess the biggest question is where to go from here? My thoughts: 1-Turn off AWE, see if the consistency problems clear up.
2-Or, install the hotfixes, see if that helps
3-Or, Continue to fix the databases, hoping that I sort out all the ‘hidden#%92 problems. Sorry for the small book, but this is an important production system. Thanks for any thoughts anyone has.
Have you collected the PERFMON (SYSMON) stats for assessment.
You should not rely on one counter to determine a bottleneck. Look for multiple counters to confirm your analysis. See if SQL Server is doing a lot of I/O. Tools like SQL Profiler and Blocker Script output can tell you if the query(s) are resulting in these large I/Os, and tuning or optimizing these individual queries from a statistics and indexes point of view may help from the disk I/O perspective. An improper index strategy can add to a disk subsystem workload. For example, instead of doing an Index Seek, the SQL Server Optimizer may choose to do a Table/Index Scan, resulting in a lot of unnecessary I/O. If the disk subsystem is a bottleneck, use the Index Tuning Wizard to go through a proper workload of the production load (captured in the form of a trace file) and see if it recommends any additional indexes. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I am having similar problems with our AMD opteron server and Windows 2003 except I see errors in the logs. See Not sure if it’s the same issue. What kind of hardware (Processor, motherboard are you using?)
Dell 6640, Quad Xenon 2.8 Ghz 400 FSB, 8x 1 gig chips DELL PERC 3DC RAID Controller I have never seen the box fully pegged, and rarely does cpu or i/o seem a bottleneck (have done perfmon stats, and am currently using a 3rd party monitoring tool – SQL Diagnostic Manager).
Do you see any blocking?
Any information from the PERFMON counters for assessment. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.