Hi,
We are a web development company that provides hosting for our clients on a dedicated machine. The hardware set up for this machine is:
P4 3.2ghz processor
1gb RAM
2 x 80gb SATA HD (RAID mirroring)
From this one box, we run the web server, SQL Server 2000 and a third-party mail server called MailEnable. The OS is Windows Server 2003. We are running approximately 20-30 dynamic websites from this server, most of which are very low traffic (<100 visitors per day). We have a handful of sites that run from SQL Server and some of these databases are fairly complex e-commerce solutions.
We have stress-tested the server using the Web Stress Test 7 software and the results indicated a much lower level of performance than we expected. We tested sites with differering set ups, such as ASP/Access, ASP/SQL Server, ASP.NET/Access and ASP.NET/SQL Server and the results were fairly similar across the board, with SQL Server being slightly better than Access and ASP.NET being slightly better than ASP.
The testing indicated that the server hits 100% CPU usage when visited by 15 concurrent users above and beyond normal evening traffic. When not testing, the CPU is stable at 2-4%. Gradually increasing above 15 users will hang the server. When the CPU hits 100%, the Processes tab of Task Manager shows that sqlserver.exe consistently uses 80% of resources. During this time, the Performance Monitor counter for Memory shows that we still have 50-200mb of free RAM.
Coding-wise, we use all performance guidelines, such as stored procedures, connection pooling, no cursors or temporary tables, all objects killed etc etc.
With all this in mind, we'd like to assess what we currently have and what we may need for the future.
Are these results consistent with expectations for this hardware and software configuration?
What can we do to improve the performance of this server?
What would be our best options for expansion? If we add another server, would it be better to have standalone SQL Server or standalone mail server?
One of our clients sends out a newsletter and the influx of traffic (>300 concurrent users) hangs the server. At what pricing level should a client expect their hosted website to handle 300+ concurrent users? If you bought a machine similar to ours and plugged it into a decent network, would it be able to handle this amount of traffic?
Thanks in advance for your help. We are struggling to find impartial advice on this matter as everyone wants to sell us a server! We're monitoring this question, so feel free to ask us things and we'll get back to you quickly.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thanks for your response.
We have tested websites of various complexity and they all seem to report the same level of performance, which suggests the problem may not be related to a particular piece of software (i.e. SQL Server). As you have confirmed, we expect higher levels of performance than this from a server with our spec. Could there be a problem with our OS set up or anything like that? What should we be looking for?
Ok I would try changing your connection strings if possible to see if this makes a difference. A good resouce for an example is below. They have pretty much every one you would need. It really sounds like a connection issue.
http://www.connectionstrin
I would definitely split the SQL Server off as a dedicated server. Best practice for a reason.
Under load, you will find that IIS and SQL contend for resources - IIS is kernel intensive. Not a good idea from a security perspective either.
Also, SQL will use whatever memory is available as well - IIS will be forced to overutilise the page file. The cascading effect of this is that the page file usage will contend with SQL Server for I/O resources as well (SQL is I/O intensive). This will also create memory pressure on the worker processes in IIS 6 - resulting in premature recycling of sessions (unpopular with your users) and 'early' garbage collection (which is resrouce intensive).
As for the mail server, I don't know it, but if you were using exchange, best practice (again, for good reasons) is to have this on a dedicated box.
AND optimise your connection strings (although this is unlikely to be the primary cause of any bottlenecks it makes sense to improve where you can).
Also note: if you are storing
Nightman is right. It would be in your best interest to at least move SQL to a different server. If you did this and setup separate application pools for each website, you should reduce problems with all sites being affected.
Most webmail software packages use some resources. They tend not to utilize processor resources but do tend to use more physical memory. I originally used IMAIL, then Merek, now I use SmarterMail since you get a ton more for the buck. On average it uses around 30-50mb of memory for SMTP and POP services.
As for SQL if you are making money hosting these sites I would recommend possible having a DBA take a look at how it is setup and if there are any performance measures you can implement for your applications. One thing people don't understand is SQL server isn't a plug and play application. You can just setup a database and start running but larger application loads need further design and peformance measures setup. Id say an average DBA would be anywhere from $100-150 per hour.
Thanks for your responses. Sorry for the delay in getting back to you - we're in the UK so were asleep. :)
Firstly, we had a look at the URL you gave for connection strings, but it doesn't indicate the pros and cons of each, nor the best method. Could you please elaborate on what we should be doing to improve performance in this respect.
We have run some further tests and the bottlenecks certainly occur with the database connection, be it SQL Server or Access. We tested a range of sites that do not have database access and 300 users barely showed on the CPU. We also did further tests on some sites that use very light database access, and these also performed without too much bother. Some of these sites produced much-improved performance from our previous tests, so maybe there were other factors influencing the results on those days.
We then focused our our attention on the heavier database applications, looking at particular pages and queries, and this seemed to highlight a potential cause of the problem. We pinpointed a number of fairly intensive queries that put a strain on the server, one of which utilises a bespoke paging method. This query uses inline SQL to by-pass the need for ADO paging to download the whole recordset. During development, we tested this method using both inline SQL and Stored Procedures and inline SQL was comfortably the quickest. Could it be that day-to-day use produces a different level of performance for this query? Would there be any benefits to re-compiling with ASP.NET2?
We also found one of our ASP sites produced similarly poor performance. Whilst this site does not have major queries, it does call the database on numerous occasions for each page. Would this have a huge negative impact on performance, even though the Stored Procedures are small?
We're having an extra 1gb of server memory installed today, making a total of 2gb. How much should we expect this to increase performance? Our tests last night indicated that the server can handle a larger number of users than the first tests showed. Pages that don't use the under-performing query worked comfortably with 50 users. However, this is still smaller than desired. Ideally, being on a budget, we'd like to avoid having to buy another server, so what level of performance should we expect to achieve with our current machine?
Can't tell just from memory upgrade.
Is memory your biggest problem, or CPU? Increasing resources in one area often simply shifts the bottleneck elsewhere (i.e. more memory might result in more resources available, but queue more threads on SQL - SQL now has more resources so attempts to work harder, I/O is still slower SATA and probably all on the same physical disk as everything else, affecting I/O performance, etc).
If you really want to know, set up a lab environment and see how your system scales on larger machines.
TheChatKing
Do you have the same database connection strings in more than one place in your applications or do you only have it in one location and all pages that require a db connection use the same one? Memory probably won't do too much for performance since you said the processor peaks out during high traffic times. It really sounds like its the connections to the database causing the problem.
SQL Pooling is one way to eliminate this type of bottleneck. It should be enabled by default. But if you use the db connection string on more than one page and they are slightly different (ie: spaces ) then it won't use pooling.
It really sounds like you need someone there to actually look at the code and SQL server. This really isn't something someone can talk you through to fix the problem.
Jim
thechatking,
I hope that the recommendations that we posted was helpful- any feedback or more questions would be useful if you want to look at any more specific issues.
Cheers
Night
P.S. Just a note - inline SQL is a performance hog on the SQL box - in consumes SQL resources (and internally in SQL Server is a primary cause of statement recompiles) - recommend stored procedures (extensibility, reusability, maintainability, database logic abstraction, etc, etc, etc)
Hi,
We haven't forgotten about you! Our hosting company took longer than expected to install our RAM upgrade and, now it's been been done, we need to find some time to run more tests. We'll post these back on here when they're done.
Regarding the use of inline SQL, there's a reason we use it in this instance, but we are looking at ways to convert it to Stored Procedure, which is what we use for everything else.
Thanks again for your continuing help.
Cheers
Mark
Hi,
Haven't had time to run significant tests, but performance under stress has definitely improved since the memory upgrade. Our clients have reported better response times when there are large quantities of visitors, so hopefully the server's life has been extended for now. :)
I've split the points as requested. Again, thanks for your help.
Cheers
Mark
Business Accounts
Answer for Membership
by: jdfultonPosted on 2006-10-05 at 06:30:20ID: 17668170
Well first off you should definitely have SQL Server and your mail server on different machines if you expect high traffic. SQL Server is a memory hog although I am thinking you probably do have a coding issue or a configuration issue. I have a web server running a mail server, sql server, and 10 dynamic sites. I have had 100+ users access the database at a single time. My server is only a dual p2 333 with 1gb of ram. I also run multiple VPS servers that only have 256MB of ram and run SQL Express, 20 or so websites and mail, stats with no problems. As for price, a typical web host can handle thousands of users per server for only a few dollars a month. Our VPS servers cost 39.99 per month with 5GB space and the other info above. A typical hosting account should only cost 10-20 per month.