Question from SQL noob | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Question from SQL noob

Hi all, First of all, I have not touched sql until 2 weeks ago and I appreciate all the info I have found on this site…it saved my bacon a few times. I would like to clarify a few things though and I can not seem to find a answer. 1)I tried installing 6gb ram on one server just for testing purposes using a /5gb /pae switch in boot.ini…….but can sql dynamically configure ram when using awe? If not what should I do to figure out what amount of memory these tests actually want from the servers? 2) My users complain their workstations can complete the sql back tests we run as fast as our servers..sometimes even faster. Hardware SHOULD not be an issue here. This company spent a fortune on hardware. Here are the specs… DISCLAIMER: I was not responsible for these purchases….just for making them work Workstation
Supermicro X5DAE Mobo
4GB RAM
(2) 3.0 Xeons 1mb cache
2 Seagate Cheetahs 15k 73gb HD NO RAID Server
Supermicro X5DAE Mobo
(2) Xeon 3.2 1mb cache
4gb RAM………trying now to set up test platform with 6gb
(2) Adaptec 2200s 128mb RAID cards
(5) Cheetahs 15k 73gb in 2 arrays…10 disks total each server Keep in mind I am NOT familiar with sql. I ran perfmon and used the recommended add ins to see what the server is up to when we run back tests. To me it seems our hardware is fine…I never show a disk queue more then 5 and that would be a spike, rarely a queue on my processors and I never sit at 100% cpu utilization. It will spike up to 100 for a moment and drop down to 80% or 50% rinse repeat. page split hit a max of 18 but again…that was a spike, it was usually at 0-3. Everything looks good to me…what am I missing here. Why are the workstations whopping the servers? I am really confused on the performance issue…hope you all can help.

1) What Whindows, SQL and SP do you have?.
2) Never compare performance WS with Server, simple not fair. One user instead how much?.
3) Is SQL server dedicated?. Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Thanks for the reply….. 1)2003 Server Standard on most servers with 2003 Enterprise on test bed. All with sql 2000 sp3 2)No users on these servers..we just run tests on them. Again I am not familiar with sql but to give you an idea..we are a financial firm and we try to predict the market based on variables which they run on the servers. 3)yup dedicated. Hope that helps
what is the sql back test you referred to?
can you post the script as well as the SHOWPLAN_ALL and STATISTICS IO output
on q1. the boot.ini switches are /3GB and /PAE
/3GB tells the OS to split the 4GB 32-bit address space at 3GB for user apps, 1GB for OS kernel, instead of the normal 2GB/2GB split.
this is helpful in most cases because the OS almost never needs more than 1GB for the kernel, one exception being when there is more than 16GB of memory.
the /PAE switch is for using memory above 4GB unless your active data size is more than 3GB, /PAE may actually degrade performance somewhat, because of the higher overhead of using PAE/AWE memory. try running in just /3GB mode and check for disk reads after the intial warmup. if there rarely any disk reads, don’t bother with AWE.
if you do have significant disk read, use the /PAE switch, set SQL Server memory to 1GB less than total system memory, and note if disk reads drop. if it does drop, then the higher overhead of AWE memory is worth it, otherwise go back and skip the PAE mode
May be I’m missing something, but Windows Standard can manage more than 2 Gbytes?
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Need you to be more specific here: 1)2003 Server Standard on most servers with 2003 Enterprise on test bed. All with sql 2000 sp3 On the SQL Server you are putting 6gb of RAM on, since that’s the one the thread is related to: 1. You have Windows 2003 Enterprise?
2. You have SQL Server 2000 Standard Edition? IF you have the above combination, you need to know the following: 1. SQL Server 2000 Standard Edition can only use 4gb of RAM. The most you probably want on the server is 5gb if all you are using it for is SQL Server. The operating system can use the other 1gb. Any more than that is just overkill. 2. You could really be killing yourself if you actually have /5gb instead of /3gb for the switch. 3. If you are using AWE, you should set SQL Server to just use the maximum 4gb it can use. You can do this with sp_configure. Also, run: USE master
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO Send us the results of that, so we can see how you have the server set up. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
LuisMartin..
with 2003 server standard and sql enterprise when I select properties -> memory I can allocate up to 3455 ram to sql on the systems w/ 4gb installed. If thats right or not I dont know..again I dont know sql well but that is what it says.
derrickleggett…
On the test bed 2003 server enterprise sql 2000 enterprise sp3
I ran the script you posted..nothing happened. joechang..
I wish I knew what you were talking about. I am just doing some part time work building these servers. I am not a programmer,dba, or really anything much other then a wrench monkey here to build some boxes. Problem is the boxes(servers) are not giving them an increase in performance they expected. Also when I had the /5gb /pae switches on and awe enabled the server would reboot in the middle of the tests. Thanks for the help guys, I realize this is above my head but I am trying best I can to figure this out.
I was wrong, Windows 2003 Standard support up to 4 Gb, so I suggest what Joe said, use /3G.
Try this one and see if SQL server can reach dinamically memory, using Enterprise Manager. Anyway, if all is Ok., this is the first step to get more performance on server.
The problem is: How we can help you after hardware problem?. At least we need from you some common words ie: Execution Plan, Profiler, Performance Monitor, Tuning.
Hope you understand what I mean.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
I am currently running the same test over and over with different configs and logging the following from perfmon (got the info from this site) mem-pages/sec
phys disk-%disk time
proc time
proc queue
page splits/sec
buffer cache hit ratio
mem both target and total server memory
Will report back when have more info after first test completes. For the first test i removed all references to any mem other then 4 gb. I did leave the /3gb in boot.ini and ran it dynamically to see what sql wants. Test should complete in about another hour. Once I can prove I am getting the best performance I can from the servers, my job is done here. Hopefully I can convince them to hire a dba at that point but until i can prove the hardware is not at fault then the blame rests on my shoulders.
Also when I had the /5gb /pae switches on and awe enabled the server would reboot in the middle of the tests. There is no /5gb switch. You need to change it to /3gb. There will not near as big of a performance increase if you don’t use this and have 6gb of RAM. You need to set this correctly or you can cause all kinds of problems. You then need to go into Enterprise Manager and set your memory to have a max of 5gb of RAM. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>