| Author |
 Topic  |
|
gpspassion
Junior Member
260 Posts |
 Posted - 23 June 2005 : 10:00:02
|
| Right, here's hoping that the obivous mistake of having strDBType = "access" instead of strDBType = "sqlserver" could have been one of the problems, it will probably take some time for the previous connections to close. if it was. |
 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 23 June 2005 : 10:00:05
|
Ahhh clustered servers - therein may lie a clue why it is sometimes fast, sometimes slow. I hit this with some internal systems here and for some reason, some users were only ever being load balanced to a really ill server , others had a more random experience. This could explain why I sometimes getr times of 0.73 seconds, sometimes 30+ seconds for a page on your site...
An experiment you could try would be to selectively try the individual servers in the connection string (better still make a dummy simple asp page to experiment with), see if one is consistently bad - could be out of disk space, fluffed RAM, being slammed some other way.
Just a thought |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 23 June 2005 : 10:01:34
|
quote:
Right, here's hoping that the obivous mistake of having strDBType = "access" instead of strDBType = "sqlserver" could have been one of the problems, it will probably take some time for the previous connections to close. if it was.
you could try stopping and restarting the application, that should drop all the connections.
hth |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 23 June 2005 : 10:01:57
|
Ok, first thing to do is change your connection string, you are using the odbc drivers which are very poor at best, use the connection string that is like
strConnString = "Provider=SQLOLEDB.1;Data Source=
Then you should check that your db has all the correct indexes, if it was not created by the snitz code the chances are the indexes are not there.
having the strdbtype wrong will not severely affect anything unless you were using MySQL which probably wouldn't work. |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 23 June 2005 : 10:05:22
|
quote:
Originally posted by pdrg
quote:
Right, here's hoping that the obivous mistake of having strDBType = "access" instead of strDBType = "sqlserver" could have been one of the problems, it will probably take some time for the previous connections to close. if it was.
you could try stopping and restarting the application, that should drop all the connections.
hth
Actually, it won't, they will just sit there until they time out, only way to get rid of them is kill them from the sql end |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 23 June 2005 : 10:11:20
|
This page was generated in 2.03 seconds
This page was generated in 1.11 seconds
etc then suddenly
This page was generated in 20.88 seconds.
This page was generated in 34.64 seconds.
This page was generated in 30.91 seconds.
it is very patchy, so I don't think it can be your code, but much more likely to be a poorly box in the cluster
|
|
|
|
gpspassion
Junior Member
260 Posts |
Posted - 23 June 2005 : 10:13:49
|
Well I thought that for a long time, but then there are all these open connections that shouldn't be there apparently and when I put both the site and the databases on the dedicated P4/2.4 it completely "killed" it, so that would point to somewhere else I'm afraid.
Ok, no "quick fix" on the StringID then, I was somehow hoping it would take care of the connections not closing.
I changed the strConnString as per your indications, thanks.
Not sure how to check the indexes, how would I do that ? I can connect to the "live" database or the "migrated" one on the server I used the other day via Enterprise Manager. |
Edited by - gpspassion on 23 June 2005 10:17:49 |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 23 June 2005 : 10:15:50
|
quote:
Originally posted by HuwR
quote:
Originally posted by pdrg
quote:
Right, here's hoping that the obivous mistake of having strDBType = "access" instead of strDBType = "sqlserver" could have been one of the problems, it will probably take some time for the previous connections to close. if it was.
you could try stopping and restarting the application, that should drop all the connections.
hth
Actually, it won't, they will just sit there until they time out, only way to get rid of them is kill them from the sql end
Ah, OK. Are the connection objects held in the ASP server object? Guess an IIS restart would do it in that case? I was malthinking they were in the application object, which was wrong, obviously. I don't know if there's COM+ running, but it ought to be possible to kill the pooled connections in that too? |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 23 June 2005 : 10:19:05
|
also - what is the pattern of the slowdowns? Could it be periodic? Maybe there's a scheduled job/backup/replication/whatever knocking it for 6 - the pattern may give you more clues.
Do you have exclusive use of the SQL server/cluster? Another user could be affecting you.
more 2p's for you to collect up - you must have nearly 10p from me now ;-)
edit - perhaps you could use your sql profiler to see what's going on on the box when, record a few hours of stuff, and mine that for patterns. |
Edited by - pdrg on 23 June 2005 10:21:27 |
|
|
|
gpspassion
Junior Member
260 Posts |
Posted - 23 June 2005 : 10:43:35
|
Yes I'd been on a shared server since the beginning and only tried this migration yesterday morning and backtracked ASAP when I found that the P4/2.4 was stalled. I could move it back to see exactly what's going on but I'd prefer to simulate a load if at all possible so as not to disrupt the users more than they've already been.
So far it seems the driver change has helped quite a bit, but I suspect this will not do much for the connections that are not closing. Would appreciate some guidance on what to look for in the indexes to see is problems can be spotted there. Again though would that explain the connections not closing ? |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 23 June 2005 : 11:45:29
|
Indexing certainly wouldn't explain connections not closing :(
However, a great way to look at indexing is to record one of those profiler trace files simulating regular usage and using the SQL Server Index Optimising tool - it can advise, based on your typical trace load, whether you have insufficient or redundant indices. Try Books Online for help with this tool:
---------------
Index Tuning Wizard
The Index Tuning Wizard allows you to select and create an optimal set of indexes and statistics for a Microsoft® SQL Server™ 2000 database without requiring an expert understanding of the structure of the database, the workload, or the internals of SQL Server.
To build a recommendation of the optimal set of indexes that should be in place, the wizard requires a workload. A workload consists of an SQL script or a SQL Profiler trace saved to a file or table containing SQL batch or remote procedure call (RPC) event classes and the Event Class and Text data columns.
------------------
hth |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 23 June 2005 : 12:45:11
|
quote:
Indexing certainly wouldn't explain connections not closing :(
No, but the quicker the server can deal with queeries, the faster it will give up the connection for someone else rather than have to wait open doing nothing waiting for a timeout.
Can you try and find other sites which share the same host to see if they are getting similar problems, but it sound to me like you have outgrown your host |
|
|
|
gpspassion
Junior Member
260 Posts |
Posted - 23 June 2005 : 15:32:30
|
Yes so I thought but I had to backtrack in a rush yesterday after moving to my own server, that P4/2.4/1Gb
So anyway that server is offline so I did a few things:
1. Upgraded the database to 3.4.05
2. Installed version 3.4.05 and also multu-language 3.4.05 as provided by Image 2001
3. Been stress testing with webserver stress tool simulating multiple simulatenous requests (500 users clicking every 10 seconds) and I'm maxing out the CPU pretty quickly although this time the hit is 50/50 on sqlserver.exe and w3wp.exe - I still need to look up the number of connections to see if there is any improvement compared to my current custom 3.4.03 install.
I need to look into the indexing but it does seem to me at this point that I should at the very least isolate thes sqlserver.exe and w3wp.exe processe by using a server with two processors. From what you write it does sound like the Snitz forums have their own server for the SQL part. |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 23 June 2005 : 17:06:47
|
quote:
From what you write it does sound like the Snitz forums have their own server for the SQL part.
No it shares the same sql server as all my other hosting clients.
From your post it sounds like you are runing sql and IIS on the same machine, this is not a very good idea at all, they definately should be on different machines.
I would also suggest that you start with a clean Snitz forum and no mods, once you are satisfied that works, then try adding mods one at a time |
|
|
|
Topic |
|
Topic Locked
