Slowed Speeds: Hardware vs Software design
-
We have an online (web) gradebook system in our school. Custom built for how our school operates.
During the peak time usually when grades are due, the system slows down to a crawl. And the dreaded time out or page cannot be displayed message appears.
The server is a Dual Processer Xeon 3.06GHz with 1 gig of RAM. This server acts as the database server and the webserver. We have about 100 teachers all entering grades at teh same time probably 30-40 records being added to the system/database per teacher continually throughout the day. Server Processor shows average of above 80% usage during these peek times, and occasionally 100% for long periods of time.
My question is two fold, is it simply the fact that we just need to upgrade our equipment? If so what's the recommendation for our situation? Or, is the software just designed poorly?
I can say when I query the information via T-SQL it takes seconds, but displaying the information via web, it takes say 30 seconds, which 100 teachers can become very frustrating.
What do you think?
-
well.
When good apps go bad. Thats tough.
Its likely that you would incurr some performance gain if you move webapp off of the sql server onto its own server. (Scale out).
and that will buy you some all important time to analyze where the performance issues are.
It will be much easier to diagnose when the app and db are on different machines.
-
First easy thing to do is put in more ram if you have more slots, its relativley cheap and usualy gives a good return. Depending on the sql version you have you could opt for another 2 gig, giving you 3 gig in all. If you have sql server standard edition then a max of two can be used by sql and the other 1 gig will be used by the operating system and web app.
If you have enterprize edition you could opt for another 3 gig and add the 3 gig switch to your boot ini and then sql could be set to use up to a max of 3 gig of ram and the other 1 gig will be used by the opperating system and iis.
You can dole this out however you choose, by having both apps, sql and web fighting for resource on the same machine you will find it hard to trouble shoot. By investing in more ram, quick, cheap and dirty option, you might find that both apps can reside happily together and you don't have to go to the expense of buying new kit.
-
I would review the software design 1st. Are you using stored-procedures and NOT adhoc queries via the web? IF NO then this is a good time to change that. May get a nice perf jump by changing that....
Also, is it possible to "batch" grade changes to be say every 15 minutes or 2x per hour or 6x daily instead of real-time? This would also provide a performance increase....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Seriously 100 teachers adding 35 records each shouldn't affect the server at all (unless it's already too stressed)... I can do batch inserts of 250K records in 2-3 seconds on a single live server with 20 users. Review the app, design, indexes. Maybe buy more ram to save you some time, but that's not gonna be the permanent solution.
-
If you are using stored procedures make sure that you have SET NOCOUNT ON at the head of each stored procedure. I have timed 10,000 itterations of a simple INSERT query and measured a 30% boost in performance.
There is a server configuration property that allows you to set this as the default however I would much prefer to put this in all my stored procedures.
If you use data referential integrity then expect and insert to be twice as expensive because the DRI has to be checked.
If you don't care about dirty reads then use WITH(NOLOCK) on any lookup tables.
If there are certain pieces of data that don't change and are used to generate parts of the web page (for example a list of schools) then I would have the app write an include file containing that list so you only ever have to go back to the database server if a new school is built.
Another approach is to design your web app so that you have the database layer with stored procedures, a compiled DLL acting as the data manipulation layer and the web app itself calling functions in the DLL.
-
Actually, the application uses stored procedures, with SET NOCOUNT ON. As I look at the server right now, with grades due today, memory usage is pretty much at 1.2gig used. So adding more memory could help. I've also noticed though that the memory usage really never drops after these types of days, it's generally hovering at the 1 gig range even during off usage days.
-
The server will always use all ram available whenever possible. If it can, the whole DB will be in ram.
-
The eventual goal is to use this for our middle school and elementary school teachers. At what point do you say ok, we need a server farm, tiered application, etc... what's the general rule? is it number of concurrent users, amount of data?
-
I used to develop using IIS5 and ASP and found that getting Visual Interdev to debug properly was an impossibility. For that reason I always developed a middle tier because at least the Visual Studio debugger DID actually work....sort of.
There are so many factors that could be affecting your application. The size of TEMPDB, the indexing of your tables in the database, your web server, a dodgy transaction causing locking problems, autoshrink or autoclose being set for the database, being in SIMPLE recovery mode, the list is endless.
If you are using IIS5 as your web server then I have seen it fight with SQL Server for resources. I used to work with a content management system and although both the app on the web server and the database were not heavy on resources, when placed on the same box (which should have coped theoretically) performance fell through the floor.
What are you using for a web server and what are you using for a scripting language?
-
We're using IIS5 and ASP, and it pretty much sounds like I'll need to separate the application and the database. That should pretty much tell me where the issues are and separate the work load between the servers.
More servers, just what the principal hates to hear, we gotta spend money. Sigh.
-
If memory contention is really the problem, you might try setting the max memory size for the sql server to a value low enough to leave some room for IIS. If IIS and the DBMS aren't forcing so much paging, you might actually get better throughput.
In my experience, though, it's never a good idea to have the web and database server on the same box.
And then again, I might be wrong ...
David Webb -
Burt -
Before you run out and purchase a new server or even upgrade the existing server you need to get a handle on why your machine is struggling. It could be that you need to upgrade the server but it also could be a single poorly written sql statement/procedure that is bringing your machine to it's knees.
Start at http://www.sql-server-performance.com/articles_audit.asp for instructions on how to "audit" your server for performance bottlenecks/issues.
Running out and purchasing a new server, etc. only to find that the performance still sucks is never a good thing.
Good luck and let us know how you fare.
Joe
-
My sql indexing knowledge is very small, but I have a feeling the database slowdown is due to design/indexing. Attached is a copy of the database diagram. The only indexes in the system are the primary keys. Today is a good day to test, since it's a school holiday and no one is using the system. Now with 1 person on, it takes a good 10-20 seconds just to open up their grade books. if you can imagin, when the gradebook opens, tblBookcolumns is displayed horizontally, tblgradebookstuds(students) displayed vertically, and tblscores fills in the center bulk of it. Again, i've only looked at SQL for about 4 weeks now so proper database design is completely foreign to me, but my thoughts are there should be additional indexes on BK_ID, and BIO_REC. Reason being it's taking time to match up the data.
What are your thoughts?
-
Burt,
You say: "I can say when I query the information via T-SQL it takes seconds, but displaying the information via web, it takes say 30 seconds..."
I have a feel that it is not SQL Server queries that have to be tuned up but the front end. RGR'us says "I can do batch inserts of 250K records in 2-3 seconds on a single live server with 20 users...." Which may be true too unless the front end (ASP app) does recordset processing. Just last week I was watching for 2.5 hours when vendor's app was working with the import updating or inserting less then 400 records into a database with only about 90 thousand records. The speed was 3 records /min, memory was down to 30 MB left on the server, CPU was working hard! On the same machine I can do more complex stuff in seconds if working from Query Analyzer. The problem in this case is that the front end application creates recordsets of existing records, works through it with each new record, applies rules etc. It is the application that takes resources.
SO: 1. Check Task manager what process is using resources 2. Use Performance Counters to see what resources. 3. (I do that) Run SQL Server Profiler with Batch Started/Batch Completed event. Then see if it is the batch that takes time (then it is SQL Statements) or the time between batches is long. Then it is your ASP app.
Yelena
Regards,Yelena Varsha
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply