Database scalability and performance

• João Oliveira

  Valued Member

  Points: 55

  More actions

  March 22, 2006 at 3:56 am

  #167650

  Hello ppl,

  I'm using SQL Server 2000 to facilitate analisys from a datawarehouse. The SQL Server is installed in a "normal" P4 (2.60 GHz) with 1GB ram. I'm using a 222 GB Hard Drive.

  Until now I had no problem with querie performance and hard drive space because I was using only minor samples of data from the datawarehouse. But because of some internal policies I won't be able to acess the datawarehouse. So I had to define a weekly procedure to receive data from the datawarehouse and import it to my SQL Server.

  I started to extract all the meaningful data from 2005. I imported it into my server and started to play with it. The first thing I found as a problem was the speed of data retrieval. I used indexes to improve performance and it works fine. The other problem I found was that I was running out of free hard drive space. The log files were getting huge. After investigating a bit I decided to shrink the files without any care. The database is static and I have all the data backed up.

  The problem is I'm planning to receive 500,000,000 lines of data from 2006.

  Every week I'll receive shunks of data to import into the server.

  So my concerns are as follows:

  1) My "normal" computer wont be able to deal with all that data - Scalability

      - Hard drive space - data files, log files

      - Performance - I have to get good acess times when running queries

  2) Best practices to improve performance - Performance

  I know it's a long post but it was the only way to explain my situation...

  I would appreciate your usefull advices.

  Thanks,

  Best regards 

• Cory E.

  SSCertifiable

  Points: 7076

  More actions

  March 22, 2006 at 6:29 am

  #628062

  I guess the first thing I would do, since it appears you do not care about the log files, would be to put the database in simple mode. 

  -- Cory

• mngong

  SSCertifiable

  Points: 6167

  More actions

  March 22, 2006 at 11:43 am

  #628177

  It appears your database is growing big time.If you are running out of space in the current server please get another one .Disk space is not the most expensive thing these days.If this is a production system not sure if it will be recommended to turn it into simple mode(my opinion).

  Worries are usually  about optimally tuning the database to handle the extra data not really about space to hold the data

   

  HTH

  Mike

• João Oliveira

  Valued Member

  Points: 55

  More actions

  March 23, 2006 at 5:11 am

  #628307

  Thanks for your usefull replies.

  Mike, you're write about the disk space. That's my minor concern.

  The problem is that it is the first time I work with such amount of data and until now file growth was not a problem. So I don't have experience in managing file growth. This is one of the databases I have:

  

  

  I have the default options selected.

  Do you think I should change this?

  Thanks

  JP