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.
Who is Participating?
wokaConnect With a Mentor Commented:
I tend to agree with JackJeckyl.  Try a defrag.

I'm a bit rusty on this, I haven't had to look after anything other than a development database for a while and the only production DB's I looked after were Oracle.

What you need to keep in mind is there are two types of fragmentation for any database.

The first is physical fragmentation that occurs for any file you store on a disk, fragments of the file get spread across the disk as the file grows.  For databases, this not a large concern if you preallocate the database file size large enough to prevent it.  If you let the files grow by themselves, and their size increases dramatically, you will get fragmentation.

However for databases, there is also internal fragmentation.  As the tables/indexes grow beyond their original allocation, they are fragmented throughout the file.

With Oracle, it took us a bit of practice to get around this without any side effects.  I kow you have to be careful exporting/importing or detaching/ataching in SQL Server that you don't upset your security identifiers (SID's).

Anyway, this is not really an answer, just background information.  I don't know enough to offer you a solution for a production database.  Keep listening, hopefully someone else here can.

It would be worth doing a search on MS's knowledgebase or MSDN on 'fragmentation' or 'reindex' or 'defrag' in the SQL Server categories.  You might find something useful.  Good luck!
gencrossAuthor Commented:
Somebody has to know something...
Does the system have any other scheduled tasks that could be running periodically? Perhaps Disk Defragment?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

gencrossAuthor Commented:
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.
Richie_SimonettiIT OperationsCommented:
Post a link to this Q at http://www.experts-exchange.com/mssql/
Richie_SimonettiIT OperationsCommented:
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.
gencrossAuthor Commented:
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.


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.
gencrossAuthor Commented:

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.
gencrossAuthor Commented:
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.
David ToddSenior DBACommented:

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.

gencrossAuthor Commented:
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.
gencrossAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.