| Author |
Topic |
|
AlfieNoakes
Starting Member
14 Posts |
 Posted - 2003-08-06 : 06:53:42
|
| I've just been running a little VB app which opens and closes hundreds of little queries, each returning 3-4 rows of data. At the same time, a network guy opened up our website which uses the same database, and the CPU level of the database server went up to 100% !It is unacceptable for the CPU level of the SQL Server 2000 database server to go to 100% when only two users are using it!!Does anyone have any idea what we are doing wrong here? |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-08-06 : 07:08:04
|
| so many things to ask :Check the CPU in Task Manager. Is it SQL Server that is using 99% of CPU, or another process ?Is there any other app that runs on the server ?Does this only ever happen when the "network guy opened up our website" ? What happens if you stop your VB app ? What happen if he only "opens the website" and nothing else is happening ?Is the growth to 100% immediate, or does it grow to that ? When he "opens the websites" does that mean there is one connection from the "website" to the Database, but potentially many users of the website (although I don't know that that would cause a CPU escalation, but it helps define the environment) ?The VB app runs 100's of SQL queries ? Concurrent or not ? I know this doesn't help, but with more info, we might be able to dig further.PS - what service pack level is SQL ?*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
AlfieNoakes
Starting Member
14 Posts |
Posted - 2003-08-06 : 07:16:32
|
Sorry, I realise I was too vague.  Firstly, yes, the server is specifically for SQL Server, and only has that running on it.As soon as I paused my VB App, the performance of the website increased. While it was running, the performance of the site was about 7-8 seconds slower than usual. As I understood it, the CPU growth to 100% was pretty much immediate. The VB app is constantly opening and closing a recordset object, querying one table in the database.I think SQL Server 2000 is running SP3. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-08-06 : 10:02:06
|
| Your VB app is client only - doesn't have a server side component ?Can you confirm that the SQLServer process in the Process tab on Task Manager (on the server) was running at 99% when the problem occurs ?I don't suppose that your VB app is doing massive number crunching?Can you replicate the problem on a test environment ?We have seen a single query MAX a server that was a 4x550 Mhz server, where it was idling. The query was doing a major amount of number crunching to generate a report based on raw data, for MIS. Does that describe at all what you are trying ?*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-06 : 12:56:35
|
| You should run SQL Profiler so that you can trace what is occurring inside SQL Server. First run it with just you using the application. Then have the network administrator run it from the web site. Compare the results, maybe one of them is running some query that needs an index on a table or something.Tara |
|
|
|
p2bl
Yak Posting Veteran
54 Posts |
Posted - 2003-08-07 : 07:56:56
|
| is the WEB site app query or update the same table?Have u close ur connection immidiately in ur VB app========================look! |
|
|
|
AlfieNoakes
Starting Member
14 Posts |
Posted - 2003-08-07 : 08:00:25
|
| No, the table my VB app is working with is in the same database, but not involved in anyway with the Website's DLL. The Network guy just told me that when the site is working, it seems to use a lot of actual CPU power, but not actually a lot of memory. It might be worth adding that the website does a lot of database inserts and updates, but all by using RecordSets, rather than Actual Insert / Update statements. I know that's a crap method, but I didn't write the website. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-07 : 08:11:16
|
I think that as long as the appquote:
opens and closes hundreds of little queries, each returning 3-4 rows of data
...you are going to suffer performance problems due to the overhead. This is definitely a problem with the way the app was written, the only way to correct it is to rewrite it. If there is any way to consolidate the queries so that they can be done with as few calls as possible, you should do it. Stored procedures will go a long way in helping.And yes, you gotta get rid of ADO AddNew and Update methods, they're the slowest way to insert data. |
|
|
|
|
SQL Server 2000 - Poor Performance 100%