Tech Off Thread

15 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

the limit of sql server 2000

Back to Forum: Tech Off
  • User profile image
    zhuo

    Has anyone hit the limit of sql server 2000 yet? What happens when you database gets really really big? well how big can it get before it hits the limit or is there one?

  • User profile image
    Marcel_Isler

    Some of our databases are 600 - 800 gigs and over, with a total to 3.5 - 7TB per server... The only speed problem's we're seeing are with UPDATE and INSERT statements... usually we circumvent that by using SELECT INTO statements for those cases...

    SELECT statements usually get slower on tables where we have 50 - 150 mio rows in it...

    Hope that helps...

    Marcel

    http://sqlobjectsearch.blogspot.com

  • User profile image
    phreaks

    Marcel_Isler wrote:
    

    Some of our databases are 600 - 800 gigs and over, with a total to 3.5 - 7TB per server... The only speed problem's we're seeing are with UPDATE and INSERT statements... usually we circumvent that by using SELECT INTO statements for those cases...

    SELECT statements usually get slower on tables where we have 50 - 150 mio rows in it...

    Hope that helps...

    Marcel

    http://sqlobjectsearch.blogspot.com



    Instead of using SELECT INTO you might want to have a look at Table Variables, they create less overhead and don't create as many LOCKS.

    Might just speed your processing up a bit.

  • User profile image
    zhuo

    Marcel_Isler wrote:
    

    Some of our databases are 600 - 800 gigs and over, with a total to 3.5 - 7TB per server... The only speed problem's we're seeing are with UPDATE and INSERT statements... usually we circumvent that by using SELECT INTO statements for those cases...

    SELECT statements usually get slower on tables where we have 50 - 150 mio rows in it...

    Hope that helps...

    Marcel

    http://sqlobjectsearch.blogspot.com



    So SQL Server can scale quite well after all. The only thing it lacks as it seems is load balancing, a problem that Oracle 10g seem to have addressed. 

    E.g. how do you serve 20,000 simultaneous users, what about 1mil simultaneous users. You can build a web farm but the database too will need to be load balanced. Speaking of which, does channel 9 really have 20,000+ simultaneous users as it says on top left. What infrastructure does it run on? A single server or multiple servers, it seems to be quite responsive serving that many people.

    Anyone else interested in knowing what Channel9 is running on? Vote here and let the channel 9 people know!



  • User profile image
    phreaks

    zhuo wrote:
    
    Marcel_Isler wrote: 

    Some of our databases are 600 - 800 gigs and over, with a total to 3.5 - 7TB per server... The only speed problem's we're seeing are with UPDATE and INSERT statements... usually we circumvent that by using SELECT INTO statements for those cases...

    SELECT statements usually get slower on tables where we have 50 - 150 mio rows in it...

    Hope that helps...

    Marcel

    http://sqlobjectsearch.blogspot.com



    So SQL Server can scale quite well after all. The only thing it lacks as it seems is load balancing, a problem that Oracle 10g seem to have addressed. 

    E.g. how do you serve 20,000 simultaneous users, what about 1mil simultaneous users. You can build a web farm but the database too will need to be load balanced. Speaking of which, does channel 9 really have 20,000+ simultaneous users as it says on top left. What infrastructure does it run on? A single server or multiple servers, it seems to be quite responsive serving that many people.

    Anyone else interested in knowing what Channel9 is running on? Vote here and let the channel 9 people know!



    1) Throw more hardware at the Sql Box
    2) Tune the heck out of your database
    3) Cache whatever data you can
    4) Use Distributed Partitioned Views (DPV)
    5) Split the DB into multiple DB's where necessary.
    6) Design your app with scalability in mind
    7) Use SqlXML procs and Edge Tables for CRUDs that generally deal with more than 10 records at a time


    Don't front on Sql Server, it does a better job than it's reputation let's on.

    Top Ten TPC-C by Performance
    Version 5 Results  As of 16-Aug-2006 12:41 PM  [GMT]
     
     
    Rank Company System tpmC Price/tpmC System Availability Database Operating System TP Monitor Date Submitted Cluster
    IBM                 IBM System p5 595   4,033,378  2.97 US $ 12/20/06  IBM DB2 9   IBM AIX 5L V5.3   Microsoft COM+   08/08/06 
    IBM                 IBM eServer p5 595   3,210,540  5.07 US $ 05/14/05  IBM DB2 UDB 8.2   IBM AIX 5L V5.3   Microsoft COM+   11/18/04 
    IBM                 IBM eServer p5 595   1,601,784  5.05 US $ 04/20/05  Oracle Database 10g Enterprise Edition   IBM AIX 5L V5.3   Microsoft COM+   04/20/05 
    HP                  hp Integrity Superdome   1,231,433  4.82 US $ 06/05/06  Microsoft SQL Server 2005 Enterprise Edt SP1   Microsoft Windows Server 2003 Datacenter Ed.(64-bit)SP1   Microsoft COM+   11/28/05 

  • User profile image
    Cornelius Ellsonpeter

    Question: on a side note, does anybody know the limit of MS Access? I know it is 2GB or something like that, but how does that translate into the number of records a person could have? I know it depends on record/table size, how many reports you have, queries, etc. Someone told me they wanted to start putting things in a database, but it was going to grow at a rate of about 400 records a week. I was a little concerned that Access would only last for more than a few months at that rate, and that they might need something bigger, but I could be wrong. Any ideas?

  • User profile image
    phreaks

    Cornelius Ellsonpeter wrote:
    Question: on a side note, does anybody know the limit of MS Access? I know it is 2GB or something like that, but how does that translate into the number of records a person could have? I know it depends on record/table size, how many reports you have, queries, etc. Someone told me they wanted to start putting things in a database, but it was going to grow at a rate of about 400 records a week. I was a little concerned that Access would only last for more than a few months at that rate, and that they might need something bigger, but I could be wrong. Any ideas?


    I thought the limit was 64k rows?

  • User profile image
    Cornelius Ellsonpeter

    phreaks wrote:
    I thought the limit was 64k rows?
    Is it? I'll have to check on that. I know there is a 2GB limit for sure, but maybe you are right.

  • User profile image
    phreaks

    Cornelius Ellsonpeter wrote:
    
    phreaks wrote: I thought the limit was 64k rows?
    Is it? I'll have to check on that. I know there is a 2GB limit for sure, but maybe you are right.


    I just did some googling (Google don't sue me!)
    and it appears as if I was mistaken.

    http://www.databasedev.co.uk/access_specifications.html

  • User profile image
    Human​Compiler

    zhuo wrote:
    Anyone else interested in knowing what Channel9 is running on? Vote here and let the channel 9 people know!


    It's running on SQL Server 2000 right now and whenever we get the new version out (will still be working on it for a while), it will be running SQL Server 2005.

  • User profile image
    zhuo

    HumanCompiler wrote:
    
    zhuo wrote: Anyone else interested in knowing what Channel9 is running on? Vote here and let the channel 9 people know!


    It's running on SQL Server 2000 right now and whenever we get the new version out (will still be working on it for a while), it will be running SQL Server 2005.


    Is it running on a single machine? i.e. web server and database together? I guess I am more interested in the infrasture behind channel9, how many server is running it and was load balancing used and how has it been set up.

  • User profile image
    Human​Compiler

    Orcsweb takes care of it for us (which is nice).  I "believe" it is two web servers (load balanced) and a seperate sql server they call into.

  • User profile image
    phreaks

    zhuo wrote:
    
    HumanCompiler wrote: 
    zhuo wrote: Anyone else interested in knowing what Channel9 is running on? Vote here and let the channel 9 people know!


    It's running on SQL Server 2000 right now and whenever we get the new version out (will still be working on it for a while), it will be running SQL Server 2005.


    Is it running on a single machine? i.e. web server and database together? I guess I am more interested in the infrasture behind channel9, how many server is running it and was load balancing used and how has it been set up.


    I haven't seen any real production sites hosting their app server and database server on the same box in years.

  • User profile image
    Red5

    Cornelius Ellsonpeter wrote:
    
    phreaks wrote: I thought the limit was 64k rows?
    Is it? I'll have to check on that. I know there is a 2GB limit for sure, but maybe you are right.


    Access: 2GB
    Excel: 65,536 rows

  • User profile image
    Human​Compiler

    phreaks wrote:
    
    zhuo wrote: 
    HumanCompiler wrote: 
    zhuo wrote: Anyone else interested in knowing what Channel9 is running on? Vote here and let the channel 9 people know!


    It's running on SQL Server 2000 right now and whenever we get the new version out (will still be working on it for a while), it will be running SQL Server 2005.


    Is it running on a single machine? i.e. web server and database together? I guess I am more interested in the infrasture behind channel9, how many server is running it and was load balancing used and how has it been set up.


    I haven't seen any real production sites hosting their app server and database server on the same box in years.



    Yup.  Setting up the web server and sql server on the same box won't run very well (unless your site doesn't get hit much).  Sql Server likes to use as much memory as it can to cache things.  You can throttle how it uses the memory, but it won't be that performant if you don't allocate much to it so your web server can breathe.  Definitely better to have seperate boxes.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.