February 8, 2005 at 10:37 am
I have a dual processor server thats got processor 0 allocated to SQL. The server is a very heavily used web server (IIS 5 on 2000) using the database and i'm suffering performance hits on processor 0 (running at 75-100%) while processor 1 chugs happily along at 10-30%.
Question is does anyone have experience of specifying the processor that SQL uses, are there any recommendations people know of, etc...
Main aim is to improve the performance of IIS (as SQL seems happy enough).
Any help/ideas would be much appreciated.
Jas
February 8, 2005 at 10:53 am
From what I've read on this site, it is considered a best practice to split the web server and sql server into 2 machines. Long story short, the web server is exposed to the internet and therefore needs to be patched as soon as a new vulnerability is exposed. While the sql server needs to be very stable and have patches tested as much as possible before being implemented, also it should not be exposed to the net. Also both servers need a lot of ram to work and they shouldn't fight for it to do their work.
As for splitting the cpus I have no idea how to solve your problem.
February 8, 2005 at 1:34 pm
The easiest way is to use Enterprise Manager, right click on your server name, go to properties. Click on Processor and then you have a listing of available processors. Just uncheck Processor 0.
This may not really improve the situation though since SQL Server might now be "starved" for CPU resources...
/*****************
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 9, 2005 at 2:30 am
Put SQL onto CPU 1?
Is IIS using CPU 0 as well?
Remove the SQL affinity mask and let the OS load balance
Is the database design optimised?
Can you split the roles across 2 servers?
Put more RAM in to minimise IO.
Buy a bigger server.
February 9, 2005 at 10:10 am
On a small box restricting processors is rarely effective. I'd remove any restrictions on cpu and see what happens.
If cpu 1 is not doing much and cpu 0 is overworked then you need to share the load - having low cpu is not good.
You could monitor to see what's using cpu, sometimes cpu relates to excessive i/o and/or lack of memory.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 9, 2005 at 10:49 am
It all depends on the situation. If you have no choice but to have IIS and SQL on the same box, and can't upgrade the box, then you have to find some way of forcing the two applications to get along.
I have seen several cases where SQL Server will consume so much CPU and memory that IIS literally starves for resources and becomes the bottleneck. By reducing the number of processors (these were both 4-way boxes and we limited SQL Server to three CPU's) and capping the amount of RAM available to SQL we realized a significant improvement in overall system performance. It is a more difficult choice with only two processors, but it seems to me that it's worth a try.
/*****************
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 9, 2005 at 2:18 pm
I am not sure, but I will make a guess that IIS 5 is not processor aware (does not support multi threads). It might be worth a little research time to dertrmine if IIS defaults to proc 0. This would easily let you move SQL to proc 1 since SQL is definitely proc aware.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
February 10, 2005 at 4:07 am
From what I have found out IIS will default usage to processor 0. I've changed the sql setting to use processor 1 which has spread the load and visibly shows a more balanced usage in the performance monitor but now both seem to run at a higher utilisation rate (about 60-70%) and there is no difference in performance.
I'll try the suggestion of capping the RAM as this wasn't something i'd considered. Has anyone any suggestions on the level to set it at? The server has 3GB of RAM, has just two disks (27GB each) the database is 3GB with a 1GB log file and the data is growing at about 10MB per day. There are 250 users on the system at any time on an internal fibre network. The IIS bandwith has been upped to 10240 packet sizes and the performance throttling has been reduced to less than 10,000 hits per day (in line with MS recommendations).
Jas
February 10, 2005 at 4:39 am
If the OS is Advanced Server then you can use the /3GB switch in boot.ini to change the server memory behaviour. There's lots of KB articles on this.
See this one though for SQL memory behaviour with 2-3GB RAM
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316749
After the /3GB switch, SQL standard edition will use up to 2 GB RAM max and IIS can use the rest. If you have SQL Enterpise, cap the SQL memory usage.
(loose explanation, I know, please no one correct me on this I do know the full technical details: http://msdn.microsoft.com/SQL/?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp)
If you're running Win 2000 standard server then it'll probably be new hardware one way or another.
If you have SQL Enterprise on Win 2000 standard server then I'd upgrade your OS to Advanced Server, otherwise you're wasting a helluva lot of licensing money
February 10, 2005 at 4:59 am
Its Windows 2000 standard....
February 10, 2005 at 5:08 am
The /3GB does not work on Standard
Both SQL and IIS are working with a 2GB address space, even if you have SQL Enterprise. The OS is using the remaining 1Gb.
http://support.microsoft.com/default.aspx?scid=kb;en-us;291988
The previous KB article (316749) states that SQL 2000 will use 2GB less 384MB, so IIS may be starved of memory, especially if SQL is using a lot of the database (you may only be using a fraction of the data in the database)
Some of your CPU load may be paging of memory, not actual use.
All you can do is cap SQL RAM, say to 1024MB, but this may slow SQL down.
You really do need to spend some money here... compare the time spent on trying to solve this to the cost of throwing hardware at the problem...
February 10, 2005 at 5:28 am
Thanks for the post and thanks everyone for the responses i think i'll start trying to get some more money....
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply