Experts, I am in need of help afer pulling my hair out for 2 days solid on this.
ASP Web Application on dedicated hosted server with Operating System Windows Server 2003 SE 64 Bit and SQL 2005 Express as the DB. Server is Single Core AMD Athlon 64 3500+ 2.2 with 1gig RAM. Although there is a 20gig System drive and in addition 2 x 160 GB (RAID 1) drives for data, I now notice that the RAID drives are empty despite the fact the system was supposed to be configured with DB data on the RAID drives. All this means OS, ASP application, DB data (150mb) and DB logs on one 20gig system drive.
I am getting complaints from the users of the app (at various locations) of speed problems only when accessing particular parts of the app (parts which I know rely on the biggest table in the DB with just under 200k records). It started a couple of days ago but the problem is I can't 'experience' the same speed problems. The perfmon counters for CPU, disk and memory all seem fine and whenever I access the app as a user I have no problems at all....it's as fast as ever.
I had been doing some slighty more unsual work on the DB recently including scheduling backups of data and logs then FTP to other locations, and also table dumps to CSV for user download. Some of this involved messing around with sp_configure values but I have undone all of this work and no improvment.
It's true I am hardly an expert at this stuff but I am learning quickly, here are some of the things I have tried...
I have checked indexes and feel confident they cannot be the problem, I did make some changes and infact CPU utilissation went down but the users still experienced slow service.
I cannot run SQL profiler directly on the machine as it is Express edition, I have managed to copy the 'blackbox' trace results onto my dev machine and poured over the trace logs....it's clear I need to put some attention in here as I have Sort Errors, Hash Errors and Missing Join Predicates on what must be the most popular series of stored procedures in the app....problem is I am stuck here....I cannot figure out how to run custom traces on the server so I can then get the trace file onto my dev machine for analysis.
As for perfmon counters, I do seem to have a lot fo lock requests and index reads for the size of the DB.
There are SQL user and SQL info errors visible in perfmon but I cannot get any more detail on these I guess? without sorting the trace.
Please help, I am sure if I could just narrow the possibilites then I can fix from there.