The Microsoft SQL Server 7.0 query processor has been redesigned to support the large databases and complex queries found in decision support, data warehouse, and OLAP applications. The query processor includes several new execution strategies that can improve the performance of complex queries. One of the significant improvements is in the area of parallel queries.
SQL Server 7.0 supports parallel execution of a single query across multiple processors. A CPU-bound query that must examine a large number of rows often benefits if portions of its execution plan run in parallel. SQL Server 7.0 automatically determines which queries will benefit from parallelism and generates a parallel execution plan. If multiple processors are available when the query begins executing, the work is divided across the processors. Parallel query execution is enabled by default.
During query optimization, SQL Server looks for queries that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. After exchange operators are inserted, a parallel query execution plan results. A parallel query execution plan can use more than one thread, whereas a serial execution plan, used by a nonparallel query, uses only a single thread for its execution. The actual number of threads used by a parallel query is determined at query plan execution and initialization and is called the degree of parallelism.
The advantage of the exchange operator is that parallel query is enabled for all aspects of query execution (insert, delete, and select operations), and is enabled in one place efficiently and reliably. This is more effective than implementing parallel query multiple times in multiple operations.
Performance results from parallel query speak for themselves.
Microsoft SQL Server automatically detects the best degree of parallelism for each instance of a parallel query execution by considering the following:
Only computers with more than one processor can take advantage of parallel queries.
SQL Server monitors its CPU usage and adjusts the degree of parallelism at query startup time. Lower degrees of parallelism are chosen if the CPUs are already busy.
Each query requires a certain amount of memory to execute. Executing a parallel query requires more memory than a nonparallel query. The amount of memory required for executing a parallel query increases with the degree of parallelism. If the memory requirement of the parallel plan for a given degree of parallelism cannot be satisfied, SQL Server automatically decreases the degree of parallelism or completely abandons the parallel plan for the query in the given workload context and executes the serial plan.
Queries that ravenously consume CPU cycles are the best candidates for a parallel query, for example, joins of large tables, substantial aggregations, and sorting of large result sets. Simple queries, often found in transaction processing applications, illustrate that the additional coordination required to execute a query in parallel outweigh the potential performance boost. To distinguish between queries that benefit from parallelism and those that do not, SQL Server compares the estimated cost of executing the query with the cost threshold for parallelism value. Although not recommended, you can change the default value of 5 using sp_configure.
If the optimizer determines the number of rows in a stream is too low, it does not introduce exchange operators to distribute the stream. Consequently the operators in this stream are executed serially. Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination cost exceeds the gains achieved by parallel operator execution.
Recommendations:
Microsoft SQL Server 7.0 can read sequential data in parallel by using files and filegroups. Having multiple CPUs available to you allows execution of these parallel data scans to take place quickly.
Recommendation:
Microsoft SQL Server 7.0 server operations, such as backup, restore, DBCC, bulk copy, and CREATE INDEX run much faster and have less impact on server operations. Performance for these operations has improved for two reasons: the way each of these operations uses the SQL Server 7.0 optimizer and the ability of SQL Server to run many of these operations in parallel.
Multiple backup devices can be used for backup and restore operations. This allows SQL Server to use parallel I/O to increase the speed of backup and restore operations because each backup device can be written to or read from concurrently with other backup devices. For example, if it takes four hours to back up a database to a single tape drive, then the backup speed when two tape drives are used is likely to be two hours. For enterprises with very large databases, using many backup devices can decrease the time required for backup and restore operations.
Multiple nonclustered indexes can be scheduled in parallel, significantly reducing the time it takes to create multiple nonclustered indexes on a single table. Multiple CPUs can help run these create index operations faster.
Recommendation:
SQL Server 7.0 exploits multiple processors better than SQL Server 6.5. SQL Server 7.0 does not require you to upgrade your computers to more processors, but can achieve much better performance if multiple processors are available.
In SQL Server 7.0, three sp_configure stored procedure options that relate to processor use are:
In Windows NT, an activity or thread in a process can migrate from processor to processor, with each migration reloading the processor cache. Under heavy system loads, specifying which processor should run a specific thread can improve performance by reducing the number of times the processor cache is reloaded. The association between a processor and a thread is called processor affinity.
You can use the affinity mask option to increase performance on SMP systems with more than four microprocessors operating under heavy load. You can associate a thread with a specific processor and specify which processor(s) SQL Server will use. You can exclude SQL Server activity from processors that are given specific workload assignments by the Windows NT operating system.
Recommendations:
The lightweight pooling option provides a means of reducing the system overhead associated with the excessive context switching sometimes seen in SMP environments. When excessive context switching is present, lightweight pooling may provide better throughput by performing the context switching inline, thus helping to reduce user/kernel ring transitions. You can find the Context Switches/sec counter in Windows NT Performance Monitor under the object thread.
Setting the lightweight pooling option to 1 causes SQL Server to switch to fiber mode scheduling. The default value for this option is 0, or threading.
Recommendation:
The priority boost option is used to specify whether SQL Server should run at a higher Windows NT scheduling priority than other processes on the same computer. If you set this option to 1, SQL Server runs at a higher priority in the Windows NT scheduler. The default is 0 (run at priority base 7 on a single processor computer and at priority base 15 on an SMP computer). Setting the priority boost option to 1 changes the Windows NT priority base to 15 on a single processor computer and 24 on an SMP computer.
Although priority boost may seem like a useful option, it can cause more harm than good. By setting this option to 1 instead of 0, you increase the Windows NT priority for SQL Server threads over normal Windows NT user threads. This seems beneficial, but it can degrade the performance of users who are not SQL Server users.
If SQL Server is running on a computer with other applications and you want to ensure that all the applications get equal processor use, you can set the affinity mask option so that SQL Server gets only specific processors, and then set priority boost to 1, which will give SQL Server high priority on the specific processors and leave the other processors available for other applications.
Recommendation:
When several applications are running on the same server, you can set the affinity mask option so SQL Server gets only specific processors, and leaves processors free for the other applications. In this case, set the priority boost option to 1.