Link to home
Start Free TrialLog in
Avatar of gencross
gencross

asked on

SQL 6.5 Maintenance Info

I have a SQL 6.5 database 23.9 gigs.  The log is shared with the data, and set to 4 gigs.  

There are about 30 users hitting the database.  

It is sitting on a server with 4 400 mhz processors and 2 gigs of memory.

At certain times it will just seem to take forever to retrieve data, while other times it runs fine.  Most of the time the same number of users are using the database.

I can do simple DB admin tasks, but I'm not familiar with maintenance tasks.  I am looking for a list of maintenace tasks that can be performed routinely to keep the database in good working condition.  No slow downs.  It doesn't seem like it should be a hardware issue, but please let me know if you think it may be.

Thank you for the help.
Avatar of gencross
gencross

ASKER

Somebody has to know something...
Does the system have any other scheduled tasks that could be running periodically? Perhaps Disk Defragment?
It does a dump of the transaction log every half hour, but that is it.  The system has been doing that for years now and it hasn't seemed to cause slow downs.
Avatar of Richie_Simonetti
Post a link to this Q at https://www.experts-exchange.com/mssql/
Sorry, you already did so.
When it is in a slow period, check out your system's performance info.  Is your CPU or memory being used at a much higher rate than when the server responds normally?

If it is using the CPU or memory at a higher rate, then check what process is causing this.  It may not even be SQL Server that is your problem.

If the server's usages seems normal, is there higher than normal network traffic at the time?

Also, is it the same type of data retrieval that is slow everytime or will the exact same action go fast once and then slow another time.  If its just slow for certain functions, I'd check to see what the application is looking for and see if you can speed this up by making more efficient indexes for that search.
I've encountered the same problem at my work and it baffled db admins, network people, everyone.  The cure was found when we moved the drives to a different server.  The read/write on the server was so bad because of the horrific fragmentation on the drives.  If you have lots of data that is constantly being overwritten or purged, defrag it once in a while.  It may sound too simple, but its what happened with me and now we fly.  Good luck.
When you say it slows down "at certian times", is there any pattern?  Does it tend to do it for the same queries?  Is it always at the same time or time interval?

If you do a defrag, remember to take your database server down, you can't defrag an open file.  It's generally easier to do a complete (physical) backup/restore than use defrag software.
carpediem, I have monitored it and the server doesn't seem to even breath that heavy.  Usually using between 700-800 megs of rams with a CPU usage of around 30-40% max.  I will have the network admin keep a close eye on it today and see if there is a time when it is bogged down.

jackjeckyl/woka,

I'll get a defrag done, that could be the problem.  Are you saying it would be better to wipe the machine and restore SQL than just do a defrag?

There is no pattern for the slowdowns, and it is hard to tell exactly what queries because they are all over the place.  We have about 50 tables with many JOINs.  What it seems to be sometimes is one person getting hung up in a server process, which hangs everyone else up that is trying to access the tables that the hung up process is trying to access.  I hope you can follow that.

Is it possible to rebuild indexes or reindex the tables?  We have a few tables with 1-2 million rows.  And it always appears to be these tables that are involved.

Thank you for the responses.  Please keep them coming.  
Tables with 1-2 million rows. If a user does a query on one of those tables and they don't use one of the indexed fields, I can see how it might bog down a bit. Especially if they are trying to equi-join with another 1-2 million record table.
ventond,

yes, but then it would be slow all of the time.  I have looked at indexes and all of the proper indexes exist.  I don't think there are any missing.  Thanks.
ASKER CERTIFIED SOLUTION
Avatar of woka
woka

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you everyone for the responses.  I am going to do a reindex this weekend and see how things turn out on Monday.
Have a good weekend.
Hi,

As well as a defrag look up a dbcc dbreindex. I'm not sure what it is for SQL 6.5, but it works well for SQL 7 & SQL 2000.

Basically it rearranges the data inside the tables to be in the clustered index order.

It is like a defrag for the data inside the files.

Also look at updating the statistics.

Regards
  David
I have done a reindex and also updated the distribution statistics on indexes.

I have also monitored the server CPU and memory.  It never seems to go over 18% CPU usage and 780 megs of memory.

It may have something to do with the log dumps every half hour.  It seems that sometimes that is when people begin to complain.

I have quite a bit to go on now.  Thank you all for your responses.

I am going to award the points later on today, once I have a chance to try one or two other things.

Any other comments are appreciated.
Thank you everyone for the suggestions.  I am dividing the points between woka and PNJ, who pointed me in the right direction with the reindex and gave some other good tips.

Thanks again