Solved

SQL 6.5 Maintenance Info

Posted on 2002-06-13
16
286 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
Comment Utility
Somebody has to know something...
0
 
LVL 2

Expert Comment

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

Author Comment

by:gencross
Comment Utility
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
Comment Utility
Post a link to this Q at http://www.experts-exchange.com/mssql/
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Sorry, you already did so.
0
 
LVL 1

Expert Comment

by:carpediem
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 4

Author Comment

by:gencross
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

6 Experts available now in Live!

Get 1:1 Help Now