SQL Server Scalability FAQ

Published: November 19, 2002

Review this FAQ to find answers to some common questions about how SQL Server 2000 scales to meet the needs of very large database (VLDB) systems.

Select a question from the list to show the answer. You can also view the answers to all of the questions by selecting the following check box.

Why does this Web site have a lot of information about scalable online transaction processing (OLTP) implementations, but very little about scalable business intelligence solutions?

We are putting together a separate Web site on business intelligence scalability. Stay tuned.

Which hardware platforms does SQL Server support?

Several editions of SQL Server 2000 are offered to meet the needs of your VLDB application. The following table identifies the number of processors, memory capacity, and database size that can be supported by different editions of SQL Server.

Edition of SQL Server	Maximum Number of Processors	Maximum Memory	aximum Database Size
SQL Server 2000 Enterprise Edition (64-bit)	64 processors	512 gigabytes (GB)	1,048,516 terabytes
SQL Server 2000 Enterprise Edition (32-bit)	32 processors	64 GB	1,048,516 terabytes
SQL Server 2000 Standard Edition (32-bit)	4 processors	2 GB	1,048,516 terabytes

Today, there are a number of customers who have implemented SQL Server 2000 on 32-processor computers with up to 64 GB of memory in production.

When should I consider 64-bit technology?

When considering SQL Server 2000 (64-bit), you should first determine whether your applications regularly access more than 4 GB of data. When running on a 32-bit system, such applications will spend a considerable amount of time swapping data in and out of memory, and will benefit from moving to a 64-bit database implementation.

Likewise, if you are consolidating multiple applications onto a single server, you may also benefit from SQL Server 2000 (64-bit). Because each additional SQL Server database requires additional memory for data and plan caching, the virtually unlimited memory addressing of the 64-bit platform may be beneficial.

Can I migrate a 32-bit SQL Server application to 64-bit technology?

Yes. Because SQL Server 2000 (64-bit) maintains the same on-disk format, database administrators are able to protect key investments they've already made on 32-bit servers, while easily moving user databases over to SQL Server 2000 (64-bit).

Migrating to a SQL Server 2000 (64-bit) database from a 32-bit database is as simple as detaching the user database from the old system, moving the database file to the new 64-bit platform, and attaching the database file to SQL Server 2000 (64-bit). This dramatically reduces the complexity in the database administrator's migration tasks and positions the user database for immediate use on the 64-bit system.

How do I select and size my hardware for a new VLDB application?

The best indicator of future performance is past performance. After you have estimated the number of concurrent users, the read and write requirements of your application, and the data size requirements (taking into account growth and management tasks), ask yourself what areas will be bottlenecks for your application (for example, number of processors, speed, memory, network, or disk input/output). Then look at customer case studies that align with your business needs. Talk to your hardware partners and customer contacts. After you have a good idea of the hardware platform, test your sizing estimates on similar hardware

What is the largest SQL Server instance in production?

To our knowledge, the largest SQL Server instance in production is 17 terabytes, split among several databases for backup management. The databases are linked together for queries.

What is the highest SQL Server bulk-load rate?

We have achieved 73.6 megabytes (MB) per second (or 528,009 rows per second) in Microsoft's scalability test lab. It was achieved on an 8-way Dell PowerEdge 8450 server connected to an EMC storage area network (SAN) during the Rosetta Genomics 10-terabyte human genome project.

Should I use RAID 5 or RAID 10 for a VLDB?

For both performance and reliability reasons, we recommend placing your data, log, and tempdb database files on RAID 10 for VLDB systems. Even in a data warehouse where the data is read-only, the data-loading performance and sort operations are important factors and RAID 10 outperforms RAID 5 in these areas.

How do I optimize tempdb database performance?

General recommendations for the physical placement and database options set for the tempdb database include:

•	Allow the tempdb database to automatically expand as needed. This ensures that queries that generate larger than expected intermediate result sets stored in the tempdb database are not terminated before execution is completed.
•	Set the original size of the tempdb database files to a reasonable size to avoid the files from automatically expanding as more space is needed. If the tempdb database expands too frequently, performance can be affected. This also applies to data files of user databases.
•	Set the file growth increment percentage to a reasonable number to avoid the tempdb database files from growing too often. If the file growth is too small compared to the amount of data being written to the tempdb database, then tempdb may need to constantly expand, thereby affecting performance.
•	Place the tempdb database on a fast input/output (I/O) subsystem to ensure good performance. Stripe the tempdb database across multiple disks for better performance. Use file groups to place the tempdb database on disks different from those used by user databases.
•	Use multiple tempdb data files across multiple logical units (LUNs) and be prepared to allocate 100 GB or more for terabyte size deployment.

Should I use multiple file groups for a VLDB system?

There are several benefits of using file groups, including:

•	File groups provide a logical grouping of physical files. You can choose to group files each with its own disk controller to allow for parallelism.
•	A table or an index can be mapped to a file group. This enables more granular control for managing space allocations to the object.
•	You can back up or recover a file group as a unit. For example, if changes are made to a single file group, you can choose to back up only that file group. Or you can choose to back up file groups in rotation instead of backing up the entire database.
•	A file group can be marked as read-only. This eliminates any chance that objects residing on that file group can be modified.

What is the best backup strategy for a VLDB?

There is no single backup strategy for a VLDB. It will depend on the size of the database, transactional load, and the backup window. Here are some best practices for developing a backup plan for a VLDB:

•	If possible, back up the entire database as a unit, to simplify backup and recovery management.
•	If a relatively small portion of the data has changed since the last full backup, you may consider doing a differential backup. This minimizes the time to do the backup and the space needed to store the backup.
•	Break up the database into multiple files or file groups. Each file or file group can be backed up or recovered individually. This eliminates the need to back up the entire database and offers an option to do piecemeal backups instead. However, this adds complexity to backup management because the backup of the database will span multiple backup devices. Also, depending on when a file or file group was backed up, it will need a different set of log backups to bring the entire database to a consistent state during full recovery.
•	Implement your SAN vendor's disk backup solution, if one is available. All of these solutions use the virtual device API in SQL Server to freeze I/O while writes to the "third mirror" are completed.

What is the best recovery strategy for a VLDB?

Similar to backup, there is no single recovery strategy for a VLDB. However, here are some best practices for developing a recovery plan for a VLDB:

•	In general, you will benefit from breaking your database into multiple files and file groups. This enables you to do a faster recovery because you will only need to recover the files that reside on the failed media.
•	It is faster to load a differential backup than to redo the log backups. If the transactional activity is limited to a small portion of the entire database, differential backups will facilitate faster recovery.
•	If the point-in-time recovery is not a requirement, you should set your database to Simple Recovery mode. This means you don't have to do log backups.

Q.
A.

Top of page