Fog Creek Software
Discussion Board




What can a SQL Server do?

Need to organize my thoughts for tomorrow 06/18/203…I’ll start by listing some questions that I have:

How does SQL Server Scale up
        o What is the largest DB that you are aware of - Bytes
          and Number of Tables
        o What are the Hardware configuration and Operating 
          Systems it can handle

Is there a rule of thumb to use for distributing the database to Multiple Servers based on size and CPU utilization
        o What is the OLTP mix that it can handle in
          measurable time frames
                Per Hour
§    Per Day
        o How many concurrent users can it support doing the
          following
§    Inserts
§    Changes
§    Deletes

        o How many concurrent batch processes can it support
          in addition to OLTP

Does it support
        o Suspended I/O
        o Split mirror image
        o Dual logging
        o Parallel recovery
        o Backup from split image
        o Incremental and Delta Backups

Online Index Reorg
        o Is there any degradation while the online index is
          being performed
        o How do you control the process (Suspend) if you are
          doing multiple concurrent inserts and deletes while
          you are doing multiple batch processes

Daniel Reyes
Tuesday, June 17, 2003

The answer to a lot of these questions is "it depends" - depending on the number of tables, the complexity of the data, the design of the schema, indexes, etc.

For more information, you might want to start at Microsoft's SQL Server performance and comparisons page: http://www.microsoft.com/sql/evaluation/compare/default.asp

As for the largest database, I am aware of Microsoft's Terraserver site ( http://terraserver.microsoft.com/ ) which uses 3 2TB (that is, 2 terabytes or 2000 gigabytes) SQL Server databases.

Philip Dickerson
Tuesday, June 17, 2003

Hardware configuration and operating system requirements are at: http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp

Philip Dickerson
Tuesday, June 17, 2003

try subscribe-sql2k@ls.sswug.org

Just me (Sir to you)
Tuesday, June 17, 2003

You can outsource your research to me. I'll send you a report and invoice when finished.

Tom Vu
Tuesday, June 17, 2003

You're pretty much limited only by how much ($$$) you want to spend on hardware.

You'll want to visit http://www.tpc.org.

SQL Server is currently showing limits of about 700,000 transactions per minute with over 500,000 (simulated/scripted) users hitting the database.

Of course, results like that mean you'll have to spend 10 plus million dollars on your server.

I've had good results with what I consider a large database (the TPC benchmarks are waaaaaay out there from what I'm used to!).

I've got a 4-way Win2K box that currently handles a 24X7 application for a large insurance carrier that is roughly 300 GB of data and has on the order of 4,000 users. I consider that a large database (the TPC databases are ridiculously large and I have no experience on that level.)

Anyway, we handle 300 GB of data, 4,000 users, and roughly 2 million transactions a week on about $70,000 worth of hardware. Our biggest expense was the disk sub-system.

I'll attempt to answer some of your questions below:

" How does SQL Server Scale up
        o What is the largest DB that you are aware of - Bytes
          and Number of Tables
        o What are the Hardware configuration and Operating 
          Systems it can handle"

==> Scales both "up" and "out" rather well. Scaling up is easier, but more expensive. You basically buy a BBF box (Bigger, Better, Faster) to scale "up". Scaling "out" involves clustering multiple (cheaper) servers. SQL Server handles this with what it calls "Federated Servers". As far as OS, you're currently strictly limited to the MS arena. Sorry, it doesn't support any of the Unixes or any other non-MS-non-Windows OSes. They've got versions that run on all of the current MS server offerings, and developer/personal editions that will run on the client desktops.


"Is there a rule of thumb to use for distributing the database to Multiple Servers based on size and CPU utilization"

==> There are no rules of thumb in this area. All you can do is experiment and see what works best.


"        o What is the OLTP mix that it can handle in
          measurable time frames
                Per Hour
§    Per Day
        o How many concurrent users can it support doing the
          following
§    Inserts
§    Changes
§    Deletes"

==> Again, limited only to your hardware budget. If you want more throughput, you've gotta spend more $$$. I would concentrate on your disk I/O subsystem, as that is where your bottleneck is going to be in almost any large database system.

        o How many concurrent batch processes can it support
          in addition to OLTP

==> SQL Server does not distinguish between "batch" and "online". To SQL Server, a connection is a connection -- regardless of whether the connection is from some batch program or from a user entering an online query.

Does it support
        o Suspended I/O
        o Split mirror image

==> Not directly, however if your hardware (disk subsystem) supports it, there's no reason you can't do it via the hardware or OS, depending if your mirroring is done via the hardware of if the mirroring is done on the OS level.

        o Dual logging

==> Again, not directly but there's no reason you can't use a combination of the OS mirroring, hardware mirroring, or SQL Server's "log shipping" if you need a secondary copy of the log file.

        o Parallel recovery

==> Not yet, but we can only hope!

        o Backup from split image

==> Not sure what this is

        o Incremental and Delta Backups

==> SQL supports a "differential" backup model. I'd have to know your definition of "Incremental" and "Delta" to make a comparison. If I understand you, I believe that SQL Servers transaction log backup will support your "Incremental" backup and an SQL "differential" backup maps to your "Delta" backup -- but I'm still not sure what you mean.

Online Index Reorg
        o Is there any degradation while the online index is
          being performed

==> Yes. And it can be, at times, quite expensive in terms of slamming the system. We schedule reorgs of larger tables for late night batch windows because of this.

        o How do you control the process (Suspend) if you are
          doing multiple concurrent inserts and deletes while
          you are doing multiple batch processes

==> HUH? I'm not understanding the question. There's no need to "Suspend" for anything. You simply use SQL Server's TRANSACTION model to maintain the consistency level you want across multiple processes performing multiple data manipulations.

Sgt. Sausage
Tuesday, June 17, 2003

These sites will be a better place for researching sql related questions:

sql-server-performance.com

sqlteam.com

Jason Watts
Tuesday, June 17, 2003

Sounds like you know Oracle and are interested in SQL Server.

Most of your questions have a trivial (and useless) answer: It depends.

Without more details of what you're trying to do and what your data, infrastructure, etc, etc is like, it is pretty difficult to answer.

Steve Jones (UK)
Wednesday, June 18, 2003

Don't you think asking that question on a forum like this is a bit ridiculous?

anon
Wednesday, June 18, 2003

*  Recent Topics

*  Fog Creek Home