Performance and scalability of SQL Server 2008

1. Introduction

Today's companies need easy access and availability of good business data so they can get a place in the global marketplace. Echoing this need for accessible data, relational databases and analysis databases continue to grow in scale, with embedded databases and many products appearing together, and many companies merging servers to mitigate management efforts. While the company's data environment continues to grow in size and complexity, they must maintain the best performance.

This white paper describes the performance and scalability of SQL Server 2008 and explains how you can use these features to:

· Optimize the performance of databases of any size using tools and features available for the database engine, Analysis Services, Reporting Services, and integration services.

· Extend your server to take full advantage of new hardware features.

· Expand your database environment to optimize the response and make your data easier to access by users.

2. Optimizing Performance with SQL Server 2008

Because your company's data continues to grow in size and complexity, you must take action to provide the best data access time. SQL Server 2008 includes many features and enhancements to optimize the performance of all aspects of functionality, including relational online transaction processing (OLTP) databases, online analytical processing (OLAP) databases, reports, data extraction, transformation, and load (ETL) processing.

Relational database performance

In most business environments, relational databases are at the heart of business-critical applications and services. As the volume of data grows, and the number of users and applications dependent on relational data storage grows, companies must be able to ensure consistent performance and responsiveness to their data systems. SQL Server 2008 provides a powerful database engine that supports large relational databases and complex query processing.

Can measure actual performance

SQL Server 2008 builds on the industry-leading performance of previous versions of SQL Server, providing your company with the highest standard database performance. Using the transaction Performance Board's TPC-C benchmark to demonstrate the high-performance capabilities of SQL Server, Microsoft is the first database vendor to release the updated TCP-E benchmark results, which more precisely shows the type of OLTP workload prevalent in the company today.

In addition, SQL Server has demonstrated its ability to execute a large range of data warehouse workloads through a record 3GB tpc-h, which is the basis for SQL Server 2008 scalability and performance.

High-performance query processing engine

SQL Server's high-performance query processing engine helps users maximize the performance of their applications. This query processing engine evaluates queries and produces the best query execution plan for dynamically maintained statistics on indexes, key selection, and data volume. You can lock the query plan in SQL Server 2008 to ensure the stable performance of the normal query execution. The query processing engine can also leverage multi-core or multiprocessor systems and generate execution plans that can leverage parallelism to further improve performance.

In general, the most expensive operation in query performance is disk I/O. SQL Server's dynamic caching capability reduces the amount of physical disk access required to obtain and modify data, and the query processing engine uses a read-only scan to predict the data pages needed for a given plan and reads them into the cache beforehand, which can significantly improve overall performance. In addition, local support for data compression by SQL Server 2008 reduces the number of data pages that must be read, which increases the performance of the I/O constraint workload.

SQL Server 2008 supports partitioning of tables and indexes, which allows administrators to control the physical placement of data by partitioning from the same table or index to multiple filegroups on a separate physical storage device. The optimization of the query processing engine in SQL Server 2008 makes it possible to access partitioned data in parallel, which significantly improves performance.