SQL Server 2000 AND IIS on same box
-
Hi All,
My site has been running on a dedicated PIII 1.1GHz 512MB RAM 40GB IDE Win2K server connecting to a shared MS SQL 2000 server.
My IIS Server was getting bogged down due to multiple users hitting my site, 1000+ users.
I decided to move much of my processing from my ASP pages to Stored Procedures which, from everything I've read is a smart thing to do. I did this successfully and did notice that pages were loading faster. My testing was done during off-peak times.
The first time that I hit a peak period with the 1000 users hitting me, the shared SQL Server started to choke! According to my host, I was over my resource limit (disk space/processor/memory) and that I'd need to move to a dedicated SQL Server. They put MS SQL Server 2000 on the same box I described above.
I suspect that my site will fail miserably with this configuration as there is no way both IIS and SQL Server will be happy running in only 512MB of RAM. Are my suspicions correct?
Also, I have a number of pages that query and user stored procedures each time even if the data has not changed. I've changed the ASP pages. to write the data from these pages to disk and then on subsequent page loads, read the data from disk rather than retrieve it from SQL Server. Basically I put the burden on Disk I/O instead of SQL Server.
Was this a good move or will I cause IIS to choke due to disk access and memory useage?
Thanks in advance,
Larry
-
Larry,
I wouldn't recommend SQL Server and IIS on the same box (and MSDN also recommends against it). However, in your case it might be worth looking at a RAM upgrade (to 1 or 2 gb) instead of the separate box that your host is pushing.
If upgrading the current box is not an option then I definitely support a separate one.
Also, I think that even though you had good intentions, you may be placing extra burdern on your server by changing the ASP pages to save oft-retreived info to disk. SQL itself has complex caching capabilities and will keep oft-used data available in memory, providing you with optimum data re-retrieval.
Having said that, if your queries are complicated or long-running then the ASP solution may well be better for performance. But denormalisation or indexed views may be an option also.
Cheers,
- Mark
Cheers,
- Mark -
Better to cache all unchanged data (if not in Giga's) in the IIS memory.
-
Hi,
maybe this http://www.sqlservercentral.com/columnists/dpoole/sqlandiisonthesamebox.asp
and the discussion on this article will help you.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You may win on the network io transfer time but agree with mccork. Once enough memory then cpu will be too slow, etc.
-
Larry, In addition to mccork's suggestion let me also recommend that you upgrade your disk drive(s) to a SCSI based system, maybe even a RAID.
V/R
Raleigh
-
Reminder: limit SQL max memory to 450 or 480 MB as leave memory for Windows/IIS/COM+, etc.
-
Thanks for all of the great input!
My site had some trouble again this weekend but I think the trouble was less due to writing static pages to disk. I can't cache all of these static pages in memory because I have about 4 of these pages per usergroup with about 1000 usergroups and files ranging in size from 10K to 100K bytes, so the total number of bytes is pretty high. Rather than calling a stored procedure each time a user accesses a page, its just pulled from disk using the file object. If 300 users need this page and the data has not been updated, then there are 300 disk access to files rather than 300 concurrent hits to the stored procedure in SQL.
The article and discussion was great. Addressed exactly what I'm dealing with. My site does not pay for itself yet so $$$ make all the difference right now.
I'm going to be changing hosts. I'll have a P4 2.0GHz with 1GB of memory. Sounds definitely like the extra memory will help.
I think if I can switch to SCSI, that might be a good idea as well.
I forgot to mention that I'll have a mail server running on the box as well to handle my POP3 email accounts.
How do I set/change the max SQL memory useage?
-
i think the problem with having the IIS, SQL Server and on top of it ur POP3 mails apart from performance might also affect the security of the server and the data...So be careful with the ports that SQL uses and other such things...And as far as the performance is concerned ..i think P4 with 1 Gig RAM with SCSI shud be Ok....
Cheers!
Arvind
Arvind -
ugh! Just found out that my hosting company had SQL Server set for a max of 152MB of RAM. That couldn't have been a help. They're bumping that to 250MB to give IIS a little more than half.
-
Think they are a bit stingy with SQL Server
If SQL is not stressed it should release the memory if the box is stressed.Lastly, adding more spindles may help a bit, even with IDE but still think increase the RAM if possible.
-
Larry, I suggest you get them to put some extra drives in.
As a rule, I limit SQL to max memory less 20% on any multi-
purpose box. 512 should be ample - but more the merrier.
Also ensure that you have a minimum of 2 drive, preferable 3.
Drive 1: Main OS/Databases
Drive 2: TempDb/Logs
If you have 3 drives, then dedicate a drive for logs and all
OS temp stuff.
Two drive system should use TWO channels, not one.
This will give you maximum "bang for buck" as the biggest
bottleneck is usually I/O.
Oh, one more thing. If you can get a fourth drive, and you
have a basic Win 2k setup, mirror using windows the drive the
databases are on. Writes degrade marginally, but reads become
hugely improved.
Because hardware is out of your control, major work is hard to
do, but send them the drives (HDD is cheap) and make them do it.
You will have a system that *SHOULD* behave even with 1000+
users. SQL loves the above config, and the OS will love not
having the I/O delays caused by a single drive config.
That means IIS and SQL will work side by side without hogging
the other's resources.
Karl Rohde
If SQL is not stressed it should release the memory if the box is stressed.Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply