United States   Change   |   All Microsoft Sites

Home

Performance and Scale

SQL Server 2008 Performance and Scale

Overview

Microsoft SQL Server 2008 provides growing databases with the tools and features necessary to optimize performance, scale-up individual servers and scale-out for very large databases.

Top New Features

  • Take control of resource allocation with Resource Governor

  • Troubleshoot, tune, and monitor SQL Server 2008 instances across the enterprise with Performance Data Collector.

  • Build high-performance analysis solutions with scalability and performance enhancements in SQL Server Analysis Services

  • Take advantage of on-demand processing and better performance with the re-engineered reporting engine for SQL Server Reporting Services

  • Increase extract, transform, and load (ETL) performance in SQL Server Integration Services with scalable lookup tasks

Resource Governor

Resource Governor

Performance

Realize the Scalability and Performance you’ve always wanted. Optimize performance for any size database with the tools and features available in SQL Server 2008.

Resource Governor

  • Provide predictable performance across workloads by defining resource limits and priorities for workloads with Resource Governor

  • Gain control of your multiple workload environments by defining workload prioritization, minimize the possibility of runaway queries, and provide mission-critical workloads with the resources they need

Performance Data Collector

  • Use SQL Server Performance Data Collector to integrate the collection, analysis, troubleshooting, and persistence of SQL Server diagnostics information

  • Use the low overhead data collector to gather performance data from performance counters, dynamic management views, SQL Trace and other sources

  • Centralize storage of performance data from across the enterprise in a management data warehouse

  • Analyze performance data using built-in reports or easily created custom reports

Real-World Performance

  • Take advantage of SQL Server 2008 industry-leading performance and scalability for real-world database workloads, as demonstrated by industry-standard Transaction Processing Performance Council benchmarks (posted at www.tpc.org)

High-Performance Query Processing Engine

  • Maximize application performance with the SQL Server 2008 high-performance query processing engine

  • Use table and index partitions to enhance performance

  • Optimize partitioned tables and indexes by using the results from the Database Tuning Advisor

  • Results are based on actual query workloads collected by SQL Server Profiler

  • Take advantage of multi-processor servers with query parallelism

  • Reduce the size of tables and indexes with data compression

Enhanced Concurrency Features

  • Optimize concurrency while protecting data integrity with configurable transaction isolation levels

  • Use snapshot isolation to provide statement-level read consistency

  • Benefit from better concurrency on partitioned tables by controlling lock escalation

  • Take advantage of partition locking and reduce disk contention by splitting tables and indexes into partitions

Analysis Services Performance

  • Increase performance while allowing users to update cell values with write-back support for MOLAP partitions

  • Increase the depth of your hierarchies and the complexity of your computations while maintaining high performance with block computations

Reporting Services Performance

  • Use on-demand processing and instance-base rendering to provide optimal performance with the re-engineered Reporting Services engine

Integration Services Performance

  • Decrease package run times and optimize ETL operations with improved lookup performance

Performance Data Collection

Performance Data Collection

Scale up

Architect a high performing and scalable SQL Server 2008 data platform to provide optimal performance for your database systems by taking advantage of the latest advancements in hardware technology.

Hardware Support

  • Take full advantage of high-end servers with SQL Server support for 64-bit technologies

  • Provide greater scalability to your systems with support for non-uniform memory access (NUMA) hardware

  • Take advantage of NUMA-based computers without application configuration changes

  • SQL Server supports both hardware NUMA and soft-NUMA

Hot-Add Memory and CPU

  • Take advantage of the Hot-Add capabilities of SQL Server 2008 Enterprise to add memory and CPU resources to scale-up a server without stopping database services

Multiple Instances

  • Optimize hardware usage by supporting multiple Database Engine instances and Analysis Services instances on a single server

  • SQL Server 2008 Enterprise supports up to 50 instances on a single Windows Server computer

Scale out

Make the right investment now for your future needs. Support very large databases and tables with a variety of techniques to scale out your databases.

Scalable Shared Databases

  • Create a dedicated read-only reporting database by using the scalable shared database feature

  • Provide enhanced performance by mounting these read-only databases on multiple reporting servers

  • All users will receive an identical picture of the data, no matter which reporting server is used

Peer-To-Peer Replication

  • Use peer-to-peer replication to scale out heavily accessed databases and enable users to update multiple copies of the database while maintaining consistency through replication

  • Reduce the time taken to implement and manage a peer-to-peer replication solution by using the new visual designer to enable applications to read or modify data in any of the databases that are participating in replication

Query Notifications

  • Build applications to more efficiently utilize application cache with the SQL Server ability to notify middle-tier applications when the cache is outdated

Service Broker

  • Build highly scalable, service-oriented solutions with SQL Server Service Broker

Data-Dependent Routing

  • Use Service Broker to handle the routing of partitioned data to multiple databases, services, or instances

  • Take advantage of the Service Broker inherent ability to support load balancing, forwarding, and gateways to add reliability and better performance to a distributed database

Scalable Shared Databases for Analysis Services

  • Improve Analysis Services performance by scaling out your solution with a single read-only Analysis Services database shared between multiple instances.

Additional Resources

 

Take the next step

Download the Essential Backpack
TechNet IT Pros Start Here
MSDN Developers Start Here