January 28, 2004 at 6:11 am
It's been suggested that it is worth testing a new server we have, which has 32G and is running S2K EE and W2003EE, running at both 16G using /3GB (giving more space to the normally addressable work area) and also at the full 32G and not using /3GB (since you can't).
The theory is certain kinds of information SQL can stash only in (or perhaps is more accessible in) the core addressible space, and giving up the additional I/O buffers in AWE space may be a worthwhile tradeoff.
Here's my problem -- this is an ad hoc server, where from one moment to the next the workload varies quite a bit. Now certainly I can run certain benchmark queries in both modes on an idle system, but what I really want to improve is general, many-user, multi-query workloads. Specifically ours.
So my question is this -- how?
More specifically, what kind of penalty does the /3GB -> /2GB cause that is visible in performance counters, should I be looking fo some specific buffers that will now have a lower hit ratio, and can they be distinguished from the ones in the AWE space?
The AWE buffering works very effectively, we have seen big updates that before took several million I/O's now drop to a few hundred thousand. It comes at CPU tradeoffs -- we have at least a 4:1 ratio in CPU speed against our old server, and a 10:1 drop in I/O is accompanied by a 2:1 drop in CPU time, so in a loose sense the extra buffering is costing us about twice the CPU cycles. But clock time is down about 2-3:1 also on these heavy I/O queries as well.
However, it's not really those queries I am trying to address, it is the high interactive workloads in prime time which is mostly ad hoc. So controlled tuning is difficult, I'm looking for performance metrics that might give a clue what is happening under the covers.
Or of course.. general advice from people who have tried both.
January 29, 2004 at 6:43 am
I was told yesterday by my MS SQL rep on an incident report I'm working through that I should NOT use the /3gb switch in the boot.ini of 2003 Server EE with SQL2KEE. According to him, MS will soon be releasing a KB article about the problems that occur from it.
You do want to use the /PAE switch and then the enble AWE fun on the SQL Server config... I challenged him that in the newest SQL Server 2K BOL (released this month), the enble awe instructions may still be incorrect for setting max memory. He understands 2003EE on 2KEE "knows" the value is GB, while I have used the same values before on win2k OS and it meant MB. He was to check and get back to me about the today or tomorrow. I'll update this when I hear his answer. HTH.
January 29, 2004 at 7:10 am
Mongo,
I will be waiting to hear on this. I am running a Win 2003 EE, SQL 2000EE box now, with those switches.
/fastdetect /3gb /PAE
We have not had any problems so far though.
Thanks,
Chris
January 29, 2004 at 7:33 am
On SQL 2000 EE SP3 I can confirm that it does understand 20000 as 20G. I moved it up and down and could see the memory usage changing on task manager. I did not try 20 to see if it woudl take that as 20M or 20G.
So far we have been running with /PAE and AWE Enabled without /3GB and using 32G, and it has been stable. I'd like to say it is performing well. It is performing well, but the CPU itself is sooo much faster than what we had, I cannot say if it is performing well given the hardware.
February 2, 2004 at 3:02 pm
"The theory is certain kinds of information SQL can stash only in (or perhaps is more accessible in) the core addressible space, and giving up the additional I/O buffers in AWE space may be a worthwhile tradeoff."
At the last PASS Summit, several product team members told me that each instance of SQL Server can only use 2Gb of RAM for its procedure cache. My own subsequent testing has seemed to support that claim. Given that you have 32Gb of RAM you should be able to make more efficient use of your RAM by installing multiple instances of SQL Server.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 2, 2004 at 3:21 pm
Be nice if I could, but these are all users of a couple of databases, so the workload is not easily partitioned among instances.
I can say that so far it's been nice and stable on W2003 with 32G. (Knock)(Knock) -- sound of knocking on wood.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply