Solved

SQL 6.5 Maintenance Info

Posted on 2002-06-13
16
287 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:gencross
  • 7
  • 2
  • 2
  • +4
16 Comments
 
LVL 4

Author Comment

by:gencross
ID: 7076103
Somebody has to know something...
0
 
LVL 2

Expert Comment

by:ventond
ID: 7076507
Does the system have any other scheduled tasks that could be running periodically? Perhaps Disk Defragment?
0
 
LVL 4

Author Comment

by:gencross
ID: 7076518
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.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7076640
Post a link to this Q at http://www.experts-exchange.com/mssql/
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7076642
Sorry, you already did so.
0
 
LVL 1

Expert Comment

by:carpediem
ID: 7076650
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.
0
 

Expert Comment

by:jackjeckyl
ID: 7076721
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.
0
 
LVL 2

Expert Comment

by:woka
ID: 7076779
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.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 4

Author Comment

by:gencross
ID: 7078034
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.  
0
 
LVL 2

Expert Comment

by:ventond
ID: 7078119
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.
0
 
LVL 4

Author Comment

by:gencross
ID: 7078144
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.
0
 
LVL 2

Accepted Solution

by:
woka earned 200 total points
ID: 7078148
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!
0
 
LVL 4

Author Comment

by:gencross
ID: 7079121
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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 7085635
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
0
 
LVL 4

Author Comment

by:gencross
ID: 7089990
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.
0
 
LVL 4

Author Comment

by:gencross
ID: 7092615
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
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now