Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    34

    Unanswered: Some Advices for large databases

    Hi,
    My MSQSQL 2000 application inone company has a backup file of more than 6 GB. The total number of tables are more than 280 while the number of fields are more than 8000.
    The last months I noticed that the backup database file is increasing rapidly. Is there a particular reason or anyone fas an idea why?
    My largest table consists of more than 160 fields and has amore than 1,2 million records and 32 indexes. I know this is not very good, and I'm thinking methods to fix it since I have noticed performance problems. Can you have some advices?
    I read that with MSSQL 2005 I can partition my table horizontally and vertically. However I think that this option is available with the enterprise edition which costs more than 10,000 Euros. Is this correct?
    Is there another way to increase performance? My server is Windows 2003 with 4 GB memory. I think that Windows 2003 doesn't support more than 4 GB memory. Is this true?
    Are there some advices for my case from the experienced users of this forum??
    Regards,
    Manolis

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your backup file is probably growing because your database is. you can always looking at using differential backups during the week.

    horizontal partitioning is available in sql 2k. i have hardly touched 2k5 yet, but what's the difference between vertical partitioning and a join?

    I am not sure about win2k3 but I know SQL2K supports up to 64gb of RAM but that may require the data center OS.

    But if you are having performance issues I would look at fixing the software first because that is going to be cheaper than throwing hardware at it.

    Have you looked at the performance monitor and profiler during these slow downs?

    Have you looked at the execution plans and index usage of your queries?

    have you checked for blocking and deadlocks?

    Have you thought about using datawarehousing to seperate OLAP from OLTP operations?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A 6 Gb database isn't enough to stress Microsoft SQL Server 2000 unless you have seriously crippled hardware. The software can deal with databases 200 times that size when run on adequate hardware.

    My first guess would be that someone or something has changed your database from SIMPLE to FULL recovery. This would be the most obvious cause of the symptoms that you are seeing, and it might actually be exactly what you want. The easy way to check this is to:
    Code:
    1. start SQL Enterprise Manager
    2. Click on the server in question
    3. Click on Databases
    4. Right-click on the database in question
    5. Select Properties from the drop down menu
    6. Click on the Options tab
    7. Check the Model drop-down near the middle of the window.
    My guess is that you're currently set for FULL instead of SIMPLE. If so, you need to find out WHY this was set. Don't just reset it without understanding why it was set, this can be disasterous!

    -PatP

  4. #4
    Join Date
    May 2004
    Posts
    34
    Hi Pat,
    Thanks for the reply.
    Fortunately the recovery model is set to simple.
    My database server is HP ML530 with 5 SCSI disks and 0ne 128 MB raid controller. I use the first 2 disks with mirror for the operation system and as a dataserver. I have 5 GB photos stored in them.
    I also have a Raid5 system with the next 3.
    The memory is 4 GB.
    MSSQL runs 2 databases. The first is the accounting system with a database backup size of less than 800MB and the production database which I built and has backup size 6.2 GB.
    The maximum number of users is 35.
    Any comments??

    Regards,
    Manolis

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you pinpoint what you think is a problem? It sounds to me like your database has grown through normal use, and the size of its backup has increased because of that. That sounds perfectly normal to me.

    I understand that you see a problem, but I don't understand what that problem is from your description. Maybe I'm slow, but I can't see what is "broken" because it looks perfectly fine from what I understand.

    -PatP

  6. #6
    Join Date
    Sep 2004
    Posts
    21

    Database Backup Size

    Your database backup is going to be just a hair smaller than your database size. You may want to switch to a FULL backup once a week, differentials throughout the rest of the week and transaction log backups thoughout each day. The frequency all depends on what you are willing to loose should you need to recover.

    I have a 300GB database that gets hourly transaction log backups. We have no performance issues. The databases are on a RAID 10 formated in 64K blocks with an offset that allows SQL Server to store 64K extents together in one block. This has improved I/O on our system.

    My biggest concern for you would be 32 indexes on one table. Are these all covering indexes? I would examine the size of the individual elements of your database (data, text/image, indexes and log) Remeber, when you update your table, you also have to update 32 indexes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •