I'm running SQL 7.0 on a RAID 5 Dell server. I'm accessing the SQL server database through a web site Forum (another server). The hardware on both machines should be adequate.
Lately the access time to navigate the forum web site has increased substantially. I rebuilt all of the indexes on the database and get the same slow performance. I did this by creating a maintenance plan and the log files tell me that the reindexing has been successful.
Does anyone have any ideas on how I can improve the performance of the SQL database to an acceptable level?
It's a bit hard to say where the bottleneck would be. First step, I think, would be to watch both the web server and the DB server on perfmon to see if either box is getting bogged down with CPU usage, or if network traffic seems out of whack, or maybe even having a lack of memory. Once you narrow down where the actual problem is, then you can start getting more information on that part of the system.
A quick way to tell if the DB server is suspect, is to run some of the queries the web server is throwing at the database in Query Analyzer. This will show you if the queries themselves are running slow. If they come back really quick, the problem is probably somewhere else.
Thank you for your response.
I just checked out PerfMon and it is showing that the sqlserv Page File Bytes usage seems high.
Task manager shows the 256M of memory all being used up and a little more (hitting the page file I presume).
I was thinking it was more a database problem but it is looking more like a hardware problem.
I'd imagine a memory upgrade might help.
Well the plot thickens folks.
As it turns out, we have another website (on the same web server) accessing a different database and the performance on that is just fine.
So the big question is: Can a lack of physical memory on a system have an effect on one database and not another? As I mentioned previously, physical memory is tapped out + hitting the page file.
Note: The database that is just fine is just over 1G in size. The problematic db is only 14M. Space available on both is very adequate.
It is very possible that two DBs on the same server can show very different performance. Several things could be going on here. If the "good" application is being used a lot more than the "poor" application, then the "good" application has probably gotten all of its data into cache, while the "poor" application is busy reading the disk. I find that a bit unlikely as a root cause, though, if the "poor" database is a mere 14MB.
At this point, set up a pair of profiler traces to watch each application. Maybe the "poor" application is suffering from bad indexing, table-scans, huge sorts, etc.. Just remember, two tables of 1000 rows each can be cross-joined into a 1,000,000 row table.
Check both traces (during or after the trace) to see which application is throwing more queries at the DB, how much CPU the average query takes for each application, and how many reads the average query runs up.
More memory would probably help too, and it is generally cheap these days.