advise on maximizing performance

[zymrgy]

I am running SQL 7 on a windows NT box. The SQL is running SP3, I will soon update to SP4.
My question is how best to make the most out of my performance. The database that I work with is pretty small ( 500MB ). Right now I run 4 different jobs nightly.

1- database optimization
2- database integrity check
3- transaction log backup
4- database backup

I have very few users connected to the server, 15~20 max. I am almost suprised at the size of the database, it being text only. I have been doing a little bit of reading & have found some stuff about defragging the database & reorganizing the index's, but I have had little luck in finding out how exactally to do this. Are these jobs that can be run nightly or am I already doing as much as I can by running the database optimization job with the enterprise manager?

I have only very recently taken over as admin of this network, things were a bit of a mess when I first started. The last guy did not believe much in updates. I have not worked with SQL in the past & am kinda lost on this one....seems to me that I should be getting more performance out of it than I am.

PIII 800 w/ 512MB ram. Berfore I started it was working with 128MB, I used spare parts to bring it up to 512.

Thanks for all your help.

 

[tlbroadbent]

Use DBCC DBREINDEX to defragment indexes in SQL Server 7. It is explained in SQL Books Online (BOL).

Reindexing is just one of part of optimizing performance. Most performance problems are caused by poor query design, poor or missing indexes, and poor database/table design.

See the topic "Overview of Optimizing Database Performance" in SQL BOL. Check out the links on the following page.

http://tlbroadbent.home.attbi.com/sql/ss_performance.htm

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm