Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: SQL SERVER 2005 Limitation ?

    We are maintaining 25 databases (one per country) on 25 MS SQL Servers. Each end of month, a cost allocation is made each database.

    Average monthly data created by the allocation: 500,000 records / DB (it can be much more in extreme cases)
    Average time of Cost Allocation: at least 4-8 hours / DB (in case of 500,000 records)

    We are on a project of centralizing all data on the Same Database.
    In this case, the allocation will have to be run on a big number of databases (18 countries / ~25 databases)
    If taking the average of 500,000 records each month, we are looking at 25*500,000 = 12 500 000 records per month
    I think that this is pushing SQL’s limits. Could you let me know if MS SQL technology chosen could cope with the above estimated number of transactions?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Easily - not even close to SQL Server's limits.
    This isn't a SQL Server issue. It is more about your application, database design, hardware etc.

    EDIT - it also depends on the nature of the transactions too. I assume you mean one transaction = one row added.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I can't tell but you seem to use Database to mean both Instance and Database. Do you know the difference between the two? are you using Database when you mean Instance?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I had a sql table with 800 million rows (LoanPerformance data), it was partitioned and indexed correctly and behaved such as a table with 800 rows. If you have any processes that need to analyze all data as a whole, you could pre-calculate historical/static data at whatever level of detail you would require.
    Last edited by PMASchmed; 06-17-09 at 09:43.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I wouldn't want to run that database on a laptop, but on server grade hardware that was properly scaled and configured for that job it should work splendidly. Two years ago, I worked on a database that processed more rows than that every day.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2009
    Posts
    2
    Many thanks for your answers.

    To summarize:

    This is not a MS SQL Server issue but this is more linked to the database design, hardware etc.

    If the database design is correctly made MS SQL is able to manage much more than the number of transaction specified above.

    An SQL database correctly designed and indexed could support a large amount of data without any problem!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Exactly. Given proper hardware and indexes, the response time should be fine for any reasonable number of rows regardless of the size of the database.

    If you create a query that needs to access 100,000,000 rows to compute a sum, that will take a while no matter what hardware or indexes you have. IF you need to sum 1000 rows out of a total of 500,000,000 the response can be sub-second if you have the right hardware and indexes for the query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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