Scalability and Performance

Customer questions about scalability and performance can include the following:

An important benefit that SQL Server offers for mobile applications development is that applications written for SQL Server run unchanged from Windows 95 or Windows 98 laptops to Windows NT Server, Enterprise Edition, multiprocessor clusters. SQL Server 7.0  is the first RDBMS engine to provide a single codebase that can scale from a mobile client to a high-end enterprise-class server.

SQL Server 7.0 handles the needs of both small and large organizations. Recent benchmarks from the Transaction Processing Performance Council (TPC) show that SQL Server 7.0 is among the best-performing RDBMSs available for the Windows NT operating system. For more information about TPC benchmarks, see www.tpc.org.

Support for Smaller Databases

SQL Server 7.0 includes the following support features for smaller databases located on either a mobile client or a workgroup server. In these environments, ease of management is the primary concern.

Dynamic memory
Dynamic memory improves performance by optimizing memory allocation and usage. The simplified design minimizes contention with other resource managers. This feature simplifies management of SQL Server on the Windows 9x and Windows NT Workstation operating systems because administrators do not need to predefine the cache size for particular databases.
Dynamic space management
The database can grow and shrink automatically within configurable limits, minimizing the need for intervention by the database administrator. It is not necessary to preallocate space and manage data structures. Dynamic space management also simplifies administration of mobile clients.
Automatic statistics maintenance
SQL Server maintains statistics on data distribution to improve query performance. This means that application developers can rely on the query optimizer for the latest statistics rather than overriding the query optimizer with specific hints in their Transact-SQL statements.
Scalable storage
The disk format and storage subsystem provides storage that is scalable from very small to very large databases. Specific changes include:
  • Simple mapping of database objects to files.

    This provides less complex management and more tuning flexibility by mapping database objects to specific disks to balance I/O load.

  • Efficient space management.

    Page sizes have increased from 2 KB to 8 KB, 64 KB I/O, column limits have lifted, variable-length character fields have increased to 8 KB, and the ability to add and delete columns from existing tables without unloading and reloading the data has been added.

  • Support for terabyte-size databases.

    Redesigned utilities support large databases efficiently.

Support for Large Databases

SQL Server 7.0 includes the following features to support very large databases that are typically located in a managed-server environment. In this environment, efficiency and performance are the primary concerns.

Hash joins and merge joins
Hash, merge, and nested loops joins are available for processing complex queries. Multiple join types can be used within a single query. The query optimizer supports specialized join operations, such as star schema joins.
Superior cache management
In general, larger I/O sizes support higher throughput rates. In SQL Server 7.0, the page size is 8 KB, extents are 64 KB, and most I/O operations use 64 KB blocks. Smart I/O is the key to enhancing performance. More efficient read aheads, physical row-order scans, and parallel I/O also improve performance.
Parallel queries
SQL Server 7.0 provides intraquery parallel execution across multiple processors. Steps in a single query are executed in parallel, delivering the optimum response time. Users can take advantage of symmetric multiprocessing (SMP) hardware for complex analysis.
Dynamic row-level locking
Full row-level locking is implemented for both data rows and index entries. Dynamic locking automatically chooses the optimal level of lock (row, page, multiple page, table) for all database operations. This feature provides optimal concurrency levels.
Large memory support
SQL Server 7.0, Enterprise Edition, supports memory addressing greater than 4 gigabytes (GB) with Alpha processor-based systems running the Windows NT Server 4.0 operating system.
Read-ahead
Smart read-ahead logic improves performance and eliminates the need for manual tuning.
Backup and restore
The parallel backup and restore utilities in SQL Server 7.0 are limited only by device speeds. Very high server transaction processing rates are maintained during full online backups.
Bulk copy program (BCP)
The bcp utility offers fast import and export transfer capabilities. The bcp utility uses OLE DB and works in conjunction with the query processor to load and unload data quickly.

Sybase Scalability and Performance

Sybase SQL Anywhere Studio is a family of RDBMS server products from Sybase, Inc., that includes the Adaptive Server Enterprise and Adaptive Server Anywhere 6.0 server engines. Both server engines have similar architectures but are based on different code bases. This means that applications written for Sybase Adaptive Server Enterprise may not be compatible with Sybase Adaptive Server Anywhere, and vice versa. The result is additional development, testing, and administration to assure application compatibility when using both database products.

The Sybase Adaptive Server Anywhere 6.0 server engine is not primarily offered as an enterprise-class database, so there are no TPC benchmarks available. Organizations would need to upsize to Sybase Adaptive Server Enterprise to obtain a comparison. For a comparison of Microsoft SQL Server and Sybase Adaptive Server Enterprise benchmarks, see the TPC Web site at www.tpc.org/.

Sybase Adaptive Server Anywhere 6.0 does not support dynamic memory allocation. Typically, a database in a mobile or disconnected environment requires additional memory only for short periods of time to process queries. Sybase Adaptive Server Anywhere 6.0 requires a fixed amount of memory while the application is running. There also is an administrative overhead because the cache size on the client must be preassigned by using startup parameters.

Sybase claims that Adaptive Server Anywhere 6.0 can run in as little as 1 megabyte (MB) of memory. However, this figure does not take the following factors into consideration:

In a real-world application, Sybase Adaptive Server Anywhere 6.0 users need from 6 MB through 8 MB of memory on a client computer, about the same as for SQL Server 7.0. Because mobile clients are running all the code on the client, Windows 9x-based users need at least 32 MB of memory, and Windows NT Workstation users need at least 48 MB. These figures may vary depending on the number and size of applications running on the client.