14.3 The database systems

< Free Open Study >

14.3.1 Database 1-Oracle architectural structure

The components comprising the Oracle database system are executed using virtual memory structures and basic application processes. Processes are jobs or tasks that work in the memory of these computers. Oracle has always placed great emphasis on portability: providing uniform features and facilities across the greatest possible range of operating environments. Oracle implements a common architecture, which includes the following components:

An area of memory available to all Oracle sessions, known as the system global area (SGA). This area of memory includes recently accessed data blocks (the buffer cache), SQL and PL/SQL objects (the library cache), and transaction information (the redo log buffer). The SGA may also contain session information.
Several tasks that perform dedicated database activities, including the database writer (DBWR), redo log writer (LGWR), system monitor (SMON), process monitor (PMON), and log archiver (ARCH). Other tasks may be configured if required to support Oracle options, such as parallel query, distributed database, or multithreaded servers. We will refer to these tasks as background tasks (although they are also often referred to as background processes).
Oracle data files, which contain the tables, indexes, and other segments that form the Oracle instance.
Redo logs, which record critical transaction information required for roll-forward in the event of instance failure.
A separate task created to perform database operations on behalf of each Oracle session, referred to as a dedicated server. If the multithreaded server option is implemented, many sessions can be supported by a smaller number of shared servers.
A SQL*Net listener task, which establishes connections from external systems.

Database and redo log files are generally implemented using the operating system's native file system or raw disk partitions, and port-specific differences at the file level are relatively minor. However, the memory and process structure of an Oracle instance will vary significantly, depending on how the operating system implements process and memory management.

The architecture of Oracle in a Windows NT environment is somewhat different from UNIX. Oracle takes advantage of NT's strong support for threads. In almost all operating systems, a process is prevented from accessing memory belonging to another process. Threads belonging to the same process, however, share a common memory address space and are able to share memory easily.

On NT, the Oracle instance is implemented as a single NT process (Figure 14.1). This process includes threads that implement each of the tasks required for the Oracle instance. Therefore, there is a thread for each of the background and server tasks plus a two-thread overhead per client connection. Because each thread shares the same memory space, there is no need to implement the SGA in shared memory; if you implement the SGA within the instance's process memory, it is available to all threads within the process.

click to expand
Figure 14.1: Oracle process and thread structure on NT.

Oracle's architecture suits the NT process/thread model. However, the single-process model restricts the total memory available to threads belonging to the Oracle instance on NT. Prior to NT version 3.51, the memory limit for a single process was only 256 MB-a severe limitation for even moderately sized Oracle instances. In NT version 4.0, a process may address up to 4 GB of virtual memory. However, 2 GB of this memory is reserved for system overhead, allowing only 2 GB for Oracle. At first glance, 2 GB might sound like a generous memory allocation for an Oracle instance. But remember that this area of memory must be sufficient to store the SGA and data segments for all Oracle sessions. Furthermore, the 2 GB is a virtual memory limit; it's possible that 2 GB of virtual memory will be expended when physical memory use is actually far lower. There are currently two options for extending the 2-GB limit: In Windows NT Server Enterprise Edition, you can reduce the system component of process memory to 1 GB, allowing up to 3 GB of memory for the Oracle instance. On Alpha NT platforms, the very large memory (VLM) option allows up to 8 GB of memory to be made available to the Oracle instance.

Oracle's multithreaded server option allows multiple client processes to share a smaller number of Oracle server processes. This approach can reduce memory requirements and process overhead. Multithreaded server is also available on NT, but only from Oracle 8 onward. Using multithreaded server under Windows NT can reduce the number of threads in the Oracle process as well as overall memory requirements. One may also be able to use the Oracle 8 connection pooling and concentrating facilities to further reduce thread and memory overhead.

Transactions

Oracle supports many types of transactions, including read-only, read/write, and discrete transactions. Depending on the transaction type set for the transaction, the Oracle database will provide different data consistency guarantees. If no transaction type is set for a transaction, it defaults to read/ write. For each transaction, Oracle must keep track of the transaction and the effect it has on the database. This is done to ensure that if the transaction does not finish, it can be rolled back and the effects of the transaction "undone" from the database. This will ensure database consistency. Oracle uses a special type of segment to record the specifics of the transaction.

Note

The queries run for the TPC-H experiment did not have any transaction type set, so they ran as read/write transactions.

Query optimization

Oracle provides an internal system feature called the optimizer. The optimizer will determine one or more execution plans that it can use to execute the SQL statement. Oracle 8i has three choices: cost, rule, and choose. The cost-based optimizer will execute the SQL statement using the plan that has the lowest cost. The rule-based optimizer will execute the SQL statement according to user-defined rules set up in the database. The choose optimizer will choose the lowest-cost optimization (cost or rule) that can execute the SQL statement. In order to determine the best execution plans for SQL statements, Oracle uses statistics that are stored in the database. These statistics must be updated periodically on the database tables, indexes, and other database objects. If the database is modified after the statistics are generated (after analyzing the tables), the optimizer might not execute with the least cost; therefore, it is crucial to regularly generate statistics on the database tables.

One of the most costly execution plans is the full table scan. Full table scans require Oracle to read every row in the table. Another execution plan to find rows of a table is by searching an index of a table. Optimizers can be passed hints to allow them to choose the best execution paths for a SQL statement. One of these hints could be to use indexes. Other optimizer hints include first rows, all rows, full table scan, nested loop, merge join, use hash join, and so on. Hints can be added to SQL statements to ensure the optimizer executes the SQL statement using the specific execution plan.

Note

The TPC-H queries were run with the Oracle database optimizer set to Choose. No indexes were implemented; all of the tables were analyzed prior to running the queries. No hints were added to the TPC-H queries, since doing so would violate the comparison guidelines.

Concurrency control and locking

Oracle uses locking mechanisms to protect data from being destroyed by concurrent transactions. Oracle provides both automatic and explicit locking capabilities. By default, Oracle provides locking for database resources for transactions in the database. The system will automatically set locks on tables and rows; the levels of the locks will depend on the transaction function (reads, inserts, updates, and deletes). Oracle can set locks in two lock modes: shared or exclusive. Shared locks are set on database resources so that many transactions can access the resource. Exclusive locks are set on resources that ensure one transaction has exclusive access to the database resource. Exclusive locks ensure transaction serialization. DML locks are Oracle locks that are automatically set on tables and indexes for transactions using DML operations (update, insert, delete). Oracle also automatically sets DDL locks on Oracle resources when DDL operations are used (create, alter, and drop).

14.3.2 Database 2-Informix Dynamic Server architectural structure

Informix Dynamic Server is a multithreaded object-relational database server that manages data stored in rows and columns in a table. It employs a single processor or symmetric multiprocessing (SMP) systems and dynamic scalable architecture to deliver database scalability, manageability, and performance. Dynamic Server can be used for on-line transaction processing (OLTP), packaging applications, data-warehousing applications, and Web solutions.

Dynamic scalable architecture

The foundation of Informix Dynamic Server's superior performance, scalability, and reliability is its parallel database architecture, dynamic scalable architecture (DSA), built to fully exploit the inherent processing power of any hardware (Figure 14.2). DSA enables all major database operations, such as I/O, complex queries, index builds, log recovery, and backups and restores, to execute in parallel across all available system resources. Informix Dynamic Server's core architecture was designed from the ground up to provide built-in multithreading and parallel processing capabilities. Parallel processing is achieved through dividing large user tasks into subtasks, thus enabling processing to be distributed across all available resources.

Figure 14.2: Configurable pool database server.

The key advantages of Informix Dynamic Server are as follows;

Maximum performance and scalability through a superior multithreaded parallel processing architecture
Reduced operating system overhead through bypassing operating system limits
Local table partitioning for superior parallel I/O operations and high-availability database administration
Parallel SQL functionality increases performance and lets all database operations execute in parallel, thereby eliminating potential bottlenecks
High database availability for supporting a wide range of business-critical applications on open systems platforms
Dynamic, distributed on-line system administration for monitoring tasks and distributing workloads
Full feature parity on Windows NT and UNIX operating systems
Full RDBMS functionality across all hardware architectures (uniprocessor, symmetric multiprocessing, and cluster systems) and database models (relational and object relational) enables seamless migration of applications, data, and skills

Locking, data consistency, isolation, and recovery

While high availability ensures integrity at the system level, data consistency ensures consistency at the transaction level. Informix Dynamic Server maintains data consistency via transaction logging and internal consistency checking and by establishing and enforcing locking procedures, isolation levels, and business rules.

When an operation is unable to be completed, the partially completed transaction must be removed from the database to maintain data consistency. To remove any partially completed transaction, Informix Dynamic Server maintains a historical record of all transactions in the logical logs and automatically uses these transaction records as a reference to restore the database to the state prior to the transaction.

Internal consistency checking is designed to alert the Informix Dynamic Server administrator to data and system inconsistencies. Informix Dynamic Server contains a data-level layer of checks, which can detect data inconsistencies that might be caused by hardware or operating system errors. If inconsistencies are detected, this internal mechanism automatically writes messages to the Informix Dynamic Server message log.

Other important features for maintaining data consistency are locking procedures and process isolation. These security measures prevent other users from changing data that are currently being read or modified and also helps the system detect when conflicting locks are held. Row- and page-level locking are specified when the table is created or altered. Table- and database-level locking are specified in the user's application.

The isolation level is the degree to which your read operation is isolated from concurrent actions of other database server processes: what modifications other processes can make to the records you are reading and what records you can read while other processes are reading or modifying them. Informix Dynamic Server has four isolation levels: dirty read, committed read, cursor stability, and repeatable read.

Join methods

When Informix must join tables, it chooses any of three algorithms. All joins are minimally two-table joins; multitable joins are resolved by joining initial resultant sets to subsequent tables in turn. The optimizer chooses which join method to use based on costs, except when you override this decision by setting OPTCOMPIND. Joins are described as follows:

Nested Loop Join: When the join columns on both tables are indexed, this method is usually the most efficient. The first table is scanned in any order. The join columns are matched via the indexes to form a resultant row. A row from the second table is then looked up via the index. Occasionally, Informix will construct a dynamic index on the second table to enable this join. These joins are often the most efficient for OLTP applications.
Sort Merge Join: After filters are applied, the database engine scans both tables in the order of the join filter. Both tables might need to be sorted first. If an index exists on the join column, no sort is necessary. This method is usually chosen when either or both join columns do not have an index. After the tables are sorted, joining is a simple matter of merging the sorted values.
Hash Join: Available starting in version 7, the hash merge join first scans one table and puts its hashed key values in a hash table. The second table is then scanned once, and its join values are looked up in the hash table. Hash joins are often faster than sort merge joins because no sort is required. Even though creating the hash table requires some overhead, with most DSS applications in which the tables involved are very large, this method is usually preferred.

Cost-based query optimizer

Informix Dynamic Server's cost-based optimizer will automatically determine the fastest way to retrieve data from a database table based on detailed information about the distribution of those data within the table's columns. The optimizer collects and calculates statistics about this data distribution and will pick the return path that has the least impact on system resources-in some cases this will be a parallelized return path, but in others it might be a sequential process. All that is needed to control the degree of parallelism is the memory grant manager.

To provide users with a degree of control, Informix Dynamic Server offers optimizer directives that let users bypass the optimizer. Areas that users can control include the following:

Access methods: This lets users specify how to access a table. For example, a user can direct the optimizer to use a specific index.
Join methods: This lets users specify how to join a table to the other tables in the query. For example, users can specify that the optimizer use a hash join.
Join order: This lets users direct the optimizer to join tables in a specific order.
Optimization goal: This lets users specify whether a query is to be optimized by response time (which returns the first set of rows) or by total time (which returns all rows).

Memory handling by Informix

All memory used by the Informix Dynamic Server is shared among the pool of virtual processors. In this way, Informix Dynamic Server can be configured to automatically add more memory to its shared memory pool in order to process client requests expeditiously. Data from the read-only data dictionary (system catalog) and stored procedures are shared among users rather than copied, resulting in optimized memory utilization and fast execution of heavily used procedures. This feature can provide substantial benefit in many applications, particularly those accessing many tables with a large number of columns and/or many stored procedures. Informix Dynamic Server also allocates an area, called the thread stack, in the virtual portion of shared memory to store nonshared data for the functions that a thread executes. The thread's stack tracks the state of a user session and enables a virtual processor to protect a thread's nonshared data from being overwritten by other threads concurrently executing the same code. Informix Dynamic Server dynamically grows the stack for certain operations such as recursive stored procedures. Informix Dynamic Server's shared memory minimizes fragmentation so that memory utilization does not degrade over time. Beyond the initial allocation, shared memory segments are automatically added in large chunks as needed but can also be added by the administrator while the database is running. The memory management system will also attempt to automatically grow the memory segment when it runs out of memory. When a user session terminates, the memory it used is freed and reused by another session. Memory can be reclaimed by the operating system by freeing the memory allocated to the database. User threads can, therefore, easily migrate among the virtual processors, contributing to Informix Dynamic Server's scalability as the number of users increases.

14.3.3 Database 3-IBM DB2 architectural structure

Conceptually, DB2 is a relational database management system. Physically, DB2 is an amalgamation of address spaces and intersystem communication links, which, when adequately tied together, provides the services of a relational database management system.

Beginning with DB2 version 3, each DB2 subsystem consists of three or four tasks started from the operator console 1. Each task runs in a portion of the CPU called an address space. Version 4 of DB2 provides an additional address space for stored procedures. A description of these five address spaces (Figure 14.3) follows.

click to expand
Figure 14.3: Components of the database services address space.

The DBAS, or Database Services Address Space, provides the facility for manipulating DB2 data structures. The default name for this address space is DSNDBM1, but each individual shop may rename any of the DB2 address spaces. The DBAS is responsible for running SQL statements and managing data buffers. It contains the core logic of the database management system. Three individual components make up the DBAS: the Relational Data System, the Data Manager, and the Buffer Manager. Each of these components performs specific tasks.
The SSAS, or System Services Address Space, coordinates the attachment of DB2 to other subsystems (CICS, IMS/DC, or TSO). SSAS is also responsible for all logging activities (physical logging, log archival, and BSDS). DSNMSTR is the default name for this address space.
The third address space required by DB2 is the IRLM, or Intersystem Resource Lock Manager. The IRLM is responsible for managing DB2 locks (including deadlock detection). The default name of this address space is IRLMPROC.
The fourth DB2 version 3 address space, DDF, or Distributed Data Facility, is the only optional one. The DDF is required only if distributed database functionality is needed.
The newest address space, SPAS, or Stored Procedure Address Space, has been added to DB2 version 4 to support stored procedures and remote procedure calls (RPCs). The SPAS runs as an allied address space providing an independent environment for stored procedures to execute. This effectively isolates the user-written stored procedure code in its own little world so that it cannot interfere with the system code of DB2 itself.

These five address spaces contain the logic to effectively handle all DB2 functionality.

The functionality of the DBAS

Recall that the DBAS is responsible for executing SQL and is composed of three distinct components: the relational system, the data manager, and the buffer manager. Each component passes a SQL statement to the next component, and, when results are returned, each component passes the results back.

The Relational Data System (RDS) is the component that gives DB2 its set orientation. When a SQL statement requesting a set of columns and rows is passed to the RDS, it determines the best mechanism for satisfying the request. Note that the RDS can parse a SQL statement and determine its needs. These needs may include any of the features supported by a relational database (such as selection, projection, or join). When a SQL statement is received by the RDS, it checks authorization; translates the data element names being accessed into internal identifiers; checks the syntax of the SQL; and optimizes the SQL, creating an access path.

The RDS then passes the optimized SQL statement to the Data Manager (DM) component. The DM delves deeper into the data being accessed. In other words, the DM is the component of DB2 that analyzes rows (either table rows or index rows) of data. The DM analyzes the request for data and then calls the Buffer Manager to satisfy the request.

The Buffer Manager (BM) accesses data for other DB2 components. A data buffer is often referred to as a cache in other DBMSs. The BM uses pools of memory set aside for the storage of frequently accessed data in order to create an efficient data access environment. When a request is passed to the BM, it must determine whether the data are in the appropriate buffer pool. If they are, the BM accesses these data and sends them to the DM. If these data are not in the buffer pool, the BM calls the VSAM Media Manager to read the data and send them back to the BM, which in turn sends these data back to the DM. The DM receives the data passed to it by the BM and applies as many predicates as possible to reduce the answer set. Only Stage 1 predicates are applied in the DM. Finally, the RDS receives the data from the DM. All Stage 2 predicates are applied, the necessary sorting is performed, and the results are returned to the requester.

An understanding of the internal components of DB2 can be helpful when developing a DB2 application. For example, consider Stage 1 and Stage 2 predictates. It is easier to understand that Stage 1 predicates are more efficient than Stage 2 predicates, because you know they are evaluated earlier in the process (in the DM instead of the RDS). Therefore, they avoid the overhead associated with the passing of additional data from one component to another.

DB2 memory management

The Database Manager Shared Memory is allocated when the database manager is started using the db2start command, and remains allocated until the database manager is stopped using the db2stop. This memory is used to manage activity across all database connections. From the Database Manager Shared Memory, all other memory is attached and/or allocated. The Database Global Memory (also called Database Shared Memory) is allocated for each database when the database is activated using the ACTIVATE DATABASE command or when the first application connects to the database. The Database Global Memory remains allocated until the database is deactivated using the DEACTIVATE DATABASE command or when the last application disconnects from the database. The Database Global Memory contains memory areas such as buffer pools, lock list, database heap, and utility heap. The database manager configuration parameter, NUMDB, defines the maximum number of concurrent active databases. If the value of this parameter increases, the number of Database Global Memory segments may grow, depending on the number of active databases.

Figure 14.4 shows how memory is used to support applications. In the previous section we introduced some configuration parameters that may affect the number of memory segments. We now introduce the configuration parameters, which allow you to control the size of each memory by limiting its size.

click to expand
Figure 14.4: Database Manager Shared Memory overview.

The Database Manager Shared Memory is required for the Database Manager to run. The size of this memory is affected by the following configuration parameters:

Database System Monitor Heap Size (MON_HEAP_SZ)
Audit Buffer Size (AUDIT_BUF_SZ)
FCM Buffers (FCM_NUM_BUFFERS)
FCM Message Anchors (FCM_NUM_ANCHORS)
FCM Connection Entries (FCM_NUM_CONNECT)
FCM Request Blocks (FCM_NUM_RQB)

The Database Manager uses the fast communication manager (FCM) component to transfer data between DB2 agents when intrapartition parallelism is enabled. Thus, if you do not enable intrapartition parallelism, memory areas required for FCM buffers, message anchors, connection entries, and request blocks are not allocated. The maximum size of the Database Global Memory segment is determined by the following configuration parameters:

Buffer pool size explicitly specified when the buffer pools were created or altered (the value of BUFFPAGE database configuration parameter is taken if 1 is specified)
Maximum storage for lock list (LOCKLIST)
Database heap (DBHEAP)
Utility heap size (UTIL_HEAP_SZ)
Extended storage memory segment size (ESTORE_SEG_SZ)
Number of extended storage memory segments (NUM_ESTORE_SEGS)
Package cache size (PCKCACHESZ)
Application global memory is determined by the following configuration parameter: application control heap size (APP_CTL_HEAP_SZ)

Query optimization

Query optimization is the part of the query process in which the database system compares different query strategies and chooses the one with the least expected cost. The query optimizer, which carries out this function, is a key part of the relational database and determines the most efficient way to access data. It makes it possible for the user to request the data without specifying how these data should be retrieved.

The cost of accessing a query is a weighted combination of the I/O and processing costs. The I/O cost is the cost of accessing index and data pages from disk. Processing cost is estimated by assigning an instruction count to each step in computing the result of the query. There are two approaches to optimization. They are as follows:

Cost based: This was developed by IBM. The optimizer estimates the cost of each processing method of the query and chooses the one with the lowest estimate. Presently, most systems use this.
Heuristic: Rules are based on the form of the query. Oracle used this at one point. Presently, no system uses this.

The query optimizer has the job of selecting the appropriate indexes for acquiring data, classifying predicates used in a query, performing simple data reductions, selecting access paths, determining the order of a join, performing predicate transformations, performing Boolean logic transformations, and performing subquery transformations-all in the name of making query processing more efficient.

Concurrency control and locking in DB2

The granularity of locking within a database management system represents a definite tradeoff between concurrency and CPU overhead. Whenever a finer granularity of locking is desired, an increase in the use of available CPU resources may be required, because locking in general increases CPU path length. No I/O operations are done, but each lock request requires two-way communication between DB2 and the internal resource lock manager (IRLM). However, it is also possible there may or may not be an increase in the number of potential lock requests. For example, for readonly SQL with highly effective lock avoidance you may not see any increase in the number of DB2 lock requests to the IRLM.

A DB2 thread makes lock requests through IRLM services. Transaction locks are owned by the work unit or thread and managed by the IRLM. DB2 objects that are candidates for transaction locking are as follows:

Table space
Partition
Table
Page
Row

The locking mechanisms must also perform many other operations in the name of locking-for example, manage the lock hierarchy, lock duration, the modes of locks, lock escalation, lock suspension, and deadlock detection and recovery.

Join methods

When multiple tables are requested within a single SQL statement, DB2 must perform a join. When joining tables, the access type (tablespace scan or index scan) defines how each single table will be accessed; understanding the join method defines how the result sets from multiple tables will be combined to deliver a unified result set back to the requester. While more than two tables can be joined together in a single SQL statement, DB2 will always perform the join operation in a series of steps. Each step joins only two tables together, and a composite table is passed to the next step in the series. The plan tables will describe how these tables are joined together and the order in which each table is accessed.

14.3.4 Database 4-Microsoft SQL Server architectural structure

Microsoft SQL Server 2000 persistently stores data in database-controlled tables organized as relations managed in physical files (Figure 14.5). When using a database, work is performed primarily with the logical components, such as tables, views, procedures, and user space. The physical implementation of relations and their realization as files is largely transparent.

click to expand
Figure 14.5: Logical versus physical view of the database.

Each instance of a SQL Server has four system databases (master, model, tempdb, and msdb) and one or more user databases (Figure 14.6). Some organizations have only one user database, containing all the data for their organization. Some organizations have different databases for each group in their organization and sometimes a database used by a single application.

click to expand
Figure 14.6: Logical tablespace structures.

It is not necessary to run multiple copies of the SQL Server database engine to allow multiple users to access the databases on a server. An instance of the SQL Server Standard or Enterprise Edition is capable of handling thousands of users working in multiple databases at the same time. Each instance of SQL Server makes all databases in the instance available to all users who connect to t*he instance, subject to the defined security permissions.

When connecting to an instance of SQL Server, your connection is associated with a particular database on the server. This database is called the current database. You are usually connected to a database defined as your default database by the system administrator, although you can use connection options in the database APIs to specify another database. You can switch from one database to another using either the Transact-SQL USE database_name statement or an API function that changes your current database context.

SQL Server 2000 allows you to detach databases from an instance of SQL Server and then reattach them to another instance, or even attach the database back to the same instance. If you have a SQL Server database file, you can tell SQL Server when you connect to attach that database file with a specific database name.

The memory algorithms and use of memory by SQL Server objects are major changes in SQL Server 7.0 over SQL Server 6.5 that improve the performance of the database and also minimize the work the database administrator must do to configure memory for good performance.

Microsoft SQL Server 7.0 has dramatically improved the way memory is allocated and accessed. Unlike SQL Server 6.5, in which memory is managed by the database administrator with configuration settings, SQL Server 7.0 has a memory manager to eliminate manual memory management.

SQL Server 6.5 has a memory configuration option, which allocates a fixed amount of memory on startup-that is, memory is segmented and manually managed. If the parameter is set too high, SQL Server cannot start. The database administrator must first determine how much memory SQL Server should use versus the operating system. For example, with 256 MB of memory, SQL Server may get 200 MB and leave 56 MB for the operating system. This in itself is an art, not a science. It is very difficult to plan how much the database alone needs, much less plan what the operating system and other applications, such as Web servers running on the same computer, might need. Use of memory is not stagnant; it is possible that SQL Server may need more memory from 8:00 A.M. to 5:00 P.M., and the operating system may need more memory from 5:00 P.M. to 8:00 A.M. to run nightly batch work. Changing the memory configuration requires a shutdown and startup of SQL Server 6.5.

When SQL Server 7.0 starts, its dynamic memory allocation determines how much memory to allocate based on how much memory the Windows NT operating system and applications for Windows NT are using. For example, assume that Windows NT has a total of 512 MB of memory. When SQL Server starts up, Windows NT and the applications running on Windows NT are using 72 MB of memory. SQL Server uses available memory, leaving 5 MB free. Therefore, SQL Server uses 435 MB of memory = 512 MB total - 72 MB for active Windows NT - 5 MB of free memory. If another Windows NT-based application is started and uses the 5 MB of free space, SQL Server proactively releases memory to ensure that 5 MB of free space always remains free. Conversely, if Windows NT releases memory so that the free memory is more than 5 MB, SQL Server uses that memory for database operations.

This dynamic memory algorithm has many advantages. You no longer need to guess the correct memory percentages for Windows NT, Windows NT-based applications, and SQL Server. You can also avoid Windows NT paging during times of heavy Windows NT use, and you can use Windows NT free memory during times of light Windows NT use. The memory algorithm for SQL Server 7.0 Desktop Edition works differently. Rather than taking memory when it is free, it gives memory back to the operating system when it is not needed. This is because it is more likely that the Desktop Edition is running other applications.

Locking architecture

Microsoft SQL Server 2000 uses locks to implement pessimistic concurrency control among multiple users performing modifications in a database at the same time. By default, SQL Server manages both transactions and locks on a per connection basis. For example, if an application opens two SQL Server connections, locks acquired by one connection cannot be shared with the other connection. Neither connection can acquire locks that would conflict with locks held by the other connection. Only bound connections are not affected by this rule.

SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases. SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. The level at which locks are acquired can vary for different objects referenced by the same query-for example, one table may be very small and have a table lock applied, while another, larger table may have row locks applied. The level at which locks are applied does not have to be specified by users and needs no configuration by administrators. Each instance of SQL Server ensures that locks granted at one level of granularity respect locks granted at another level. There are several lock modes: shared, update, exclusive, intent, and schema.

If several connections become blocked waiting for conflicting locks on a single resource, the locks are granted on a first come, first served basis as the preceding connections free their locks. In support of concurrent operations, SQL Server has an algorithm to detect deadlocks. If an instance of SQL Server detects a deadlock, it will terminate one transaction, allowing the other to continue.

SQL Server can dynamically escalate or de-escalate the granularity or type of locks. For example, if an update acquires a large number of row locks and has locked a significant percentage of a table, the row locks are escalated to a table lock. If a table lock is acquired, the row locks are released. SQL Server 2000 rarely needs to escalate locks; the query optimizer usually chooses the correct lock granularity at the time the execution plan is compiled.

Structured Query Language

To work with data in a database, you have to use a set of commands and statements (language) defined by the DBMS software. Several different languages can be used with relational databases; the most common is SQL. The American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) define software standards, including standards for the SQL language. SQL Server 2000 supports the entry level of SQL-92, the SQL standard published by ANSI and ISO in 1992. The dialect of SQL supported by Microsoft SQL Server is called Transact-SQL (T-SQL). T-SQL is the primary language used by Microsoft SQL Server applications.

Summary of special features

Microsoft SQL Server 2000 gives users an excellent streamlined database platform for large-scale, on-line transactional processing (OLTP), data warehousing, and e-commerce applications. The improvements made to SQL Server version 7.0 provide a fully integrated XML environment, add a new data mining feature in analysis services, and enhance repository technology with metadata services. SQL Server 2000 enhances the performance, reliability, quality, and ease of use of SQL Server 7.0.

< Free Open Study >