Increased High-End Scalability
SQL Server 7.0 includes new features to improve its high-end scalability. Microsoft increased the maximum amount of memory that SQL Server can address. Under NT 4.0, Enterprise Edition's Virtual Memory Manager (VMM), SQL Server 7.0 can address up to the NT 4.0 maximum of 4GB of RAM. With NT 5.0, SQL Server 7.0 supports 64-bit addressing, which lets SQL Server address up to 32GB of RAM on 64-bit platforms such as Alpha.
Another enhancement Microsoft added to improve SQL Server 7.0's high-end scalability is better support for symmetric multiprocessing (SMP). Although SQL Server 6.5 took advantage of multiple processors, independent tests show it can't scale past four processors. Microsoft designed SQL Server 7.0 to let it take advantage of more processors, including 8-way systems. Future issues of Windows NT Magazine will examine SQL Server's 7.0's SMP scalability in detail. In addition, SQL Server 7.0 adds support for intra-query parallelism to better support multiple processors. Intra-query parallelism is a high-performance SMP processing technique that lets one query execute on multiple CPUs. This feature lets SQL Server provide the shortest response time by maximizing the use of the available processors.
Increased Performance
According to SQL Server's designers, Microsoft had left SQL Server's internal structures unchanged since the company acquired SQL Server from Sybase. Microsoft knew it must significantly change the software to support the next generation of the product. Not only did SQL Server 7.0 need to grow bigger, it needed to grow faster. To enable these speed improvements, Microsoft made many changes to the SQL Server 7.0 core database structures and query engine. Table 1 presents a partial list of the most significant changes.
One basic change (not shown in Table 1) is the increased page size. SQL Server's page size increased from 2KB to 8KB. Microsoft also increased the extents from 2KB to 8KB and increased the basic I/O size for scans to 64KB.
Unlike previous versions, SQL Server 7.0 can back up databases online while minimizing the effect on the system's performance. Microsoft tests demonstrate that the online backup degrades performance by only about 5 percent. Another backup enhancement is the introduction of incremental database backup and restore. The new incremental backup feature lets you back up database changes instead of having to back up the entire database.
Microsoft also improved the performance of SQL Server 7.0's query processor by making the query engine smarter than it was in previous versions. Many new enhancements target complex queries and decision-support applications, and how to handle large databases better. The query processor now performs read-ahead and parallel I/O during unordered scans for greater performance. In addition, the query processor uses several new high-performance processing techniques such as multi-index filters, merge joins, and hash joins. Multi-index filters let SQL Server use index values to satisfy a data request, a process that can reduce physical I/O. If the index contains all the information that the query engine needs, the engine doesn't need to access the base table. Merge joins use multiple indexes by merging the subsets of two or more indexes on the fly to speed up complex queries. Hash joins let you join tables when no appropriate indexes are available. Hash joins are useful in ad-hoc queries. The query processor makes an initial rough pass (i.e., hash) on the file and groups the data into subsets. The query processor then checks these subsets before retrieving the data. Finally, the query processor uses a cost basis, which it calculates according to the type of request, the projected processor, and I/O requirements, to select the appropriate processing technique.
My informal testing confirms that SQL Server 7.0's performance-related enhancements are worthwhile. When I ran custom database applications on a 200MHz Pentium-based server with 96MB of memory, the applications performed noticeably better running against SQL Server 7.0 than SQL Server 6.5.
Dynamic Tuning and Memory Management
One of SQL Server 7.0's biggest enhancements is the introduction of dynamic resource management. Dynamic resource management eliminates the need to pre-allocate a given amount of NT's memory and hard disk space for SQL Server. SQL Server 6.5 required you to manually configure the amount of memory you wanted to allocate to SQL Server and the amount of disk space you wanted to assign to each database device. As your SQL Server 6.5 system grew, you manually altered the configuration settings for these resources to keep SQL Server optimized. SQL Server 7.0 eliminates the need to manually allocate system resources. SQL Server 7.0 integrates tightly with the operating system (OS) and automatically increases and decreases the amount of memory and disk space needed.
SQL Server 7.0's dynamic disk allocation also eliminates devices. In previous versions of SQL Server, you had to assign a given amount of disk storage to a device to contain your databases. This disk storage didn't dynamically change as the databases changed. SQL Server 7.0 uses OS files instead of devices to contain databases. When you define a SQL Server 7.0 database, you can specify a growth limit and a maximum size. When the database becomes full, it automatically expands by the size of the growth increment. A database can expand up to the limit of available disk space or until it reaches its maximum configured size.
Screen 4 presents SQL Server 7.0's new database creation window.
The database properties in Screen 4 illustrate some of the new SQL Server 7.0 database characteristics. When you build a new database, SQL Server creates an OS file in the \MSSQL7\DATAdirectory. In this example, you see that creating the new winntmag database creates the file \MSSQL7\DATA\winntmag_Data. Likewise, SQL Server creates a file for the \MSSQL7\DATA\winntmag_Log transaction log when it creates the database. The options at the bottom of the window let you control how the database will dynamically allocate storage. You can control whether the database expands by MBs or by a specified percentage. You can also set maximum limits or turn off dynamic disk allocation.
Dynamic Cost-based Locking
The addition of dynamic row-level locking required extensive changes to SQL Server's core database engine. SQL Server 6.5 relied on page-level locking, which provides good performance but doesn't offer a lot of granular control in an online transaction processing (OLTP) environment. Page-level locking locks users out of a row in a database that other users aren't accessing but that is still on the same page as another row that another user's application is waiting to update. Conversely, row-level locking provides completely granular control, but its greater overhead causes worse performance than page-level locking, especially in batch update scenarios. Instead of using strictly page-level or row-level locking, SQL Server 7.0 uses a locking scheme based on cost. Query Analyzer uses a combination of required CPU and I/O resources to calculate the locking cost. Based on the Query Analyzer's cost calculation, SQL Server uses row-level locks for OLTP-type, single-row update transactions and page- or table-level locks for batch-type transactions involving multiple rows.
Prev. page
1
[2]
3
next page 
