performance and scalability. optimizing performancescaling upscaling out

Your Data Any Place, Any TimePerformance and Scalability

Performance and Scalability Challenges

Optimizing Performance

Scaling Up

Scaling Out

Real-world, predictable performance

Performance and Scalability SQL Server 2008

Scale up with today’s hardware

Scale out for the enterprise

TPC benchmarks

Workload prioritizati

on

Tuning and optimization tools

Enterprise health

monitoring

Improved BI

performance

Multi-instance

architecture

6464-bit

technologiesNUMA

NUMA Support

+Hot-add hardware

Scalable shared

databases

Distributed partitioned views

Peer-to-peer replication

Query notificatio

nsService Broker

Data dependent

routing

Scale-out Analysis Services

Optimizing Performance

Historical Perspective

Relational Database Performance

Historical Perspective

•Enterprise Manager•On-Demand Memory•On-Demand Disk

SQL Server 7.0

•Multiple Server Instances•Distributed Partitioned Views, Indexed Views•64-GB Memory Support (AWE)

SQL Server 2000

•SQL Server Management Studio•64-bit Support, Partitioning, Snapshot Isolation•Database Maintenance Plans

SQL Server 2005

•Resource Governor•Performance Studio•Plan Freezing

SQL Server 2008

Relational Database Performance

Measurable, real-world

performance

• TPC-C• TPC-E• TPC-H

Performance

optimization tools• SQL Server

Profiler• Database Engine

Tuning Advisor• Performance

Studio

High performance query processing engine

• Cost-based optimization

• Read-ahead scans

• Partitioning

Relational Database PerformanceResource Governor

SQL Server

Admin Workload

Backup

Admin Tasks

Report WorkloadOLTP Workload

OLTP Activity

Executive

ReportsAd-hoc Reports

High

Min Memory 10%Max Memory 20%

Max CPU 20%

Admin Pool

Max CPU 90%

Application Pool

Ability to differentiate workloads

e.g. app_name, login, etc.

Per-request limitsMax memory %Max CPU timeGrant timeoutMax Requests

Resource monitoring

Relational Database PerformancePerformance Studio

• Data providers• SQL Trace• Performance Counters• Transact-SQL

Low overhead performance data collection

Relational Database PerformancePlan Freezing

Freeze Plan

• Clear Plan Cache

• Freeze Plan

Export Plan

• Enable/Disable

• Script Frozen Plan

Scaling Up

Hardware Support

Concurrency

Hardware Support

64-bit

Multi-core, multi-

processor

Large-scale memory NUMA

hardwareHot-Add hardware• Memory• Processor

Concurrency

Transaction isolation

•Read uncommitted•Read committed•Repeatable read•Serializable•Read committed (with row versioning)•Snapshot

Lock escalation granularity

•Row•Page•Partition•Table

Scaling Out

Scalable Shared Databases

Distributed Partitioned Views

Peer-to-Peer Replication

Scalable Shared Databases

Read-only database in SANMounted by multiple reporting serversApplications access a consistent copy from any server

Distributed Partitioned Views

Data is partitioned horizontally across multiple serversTransact-SQL view retrieves all data with a UNION ALL clauseRequests can be directed by using data dependent routing

Peer-to-Peer Replication

Data is replicated to local serversLocal modifications are propagated throughout the enterprise

Database Application Performance

Query Notifications

LINQ – Language Integrated Querying

Database Application Performance

Database Server

Database Schema

Client Application

Query Notifications

Application notified when queried data changesOptimal cache refresh behavior

LINQ – Language Integrated Querying

SQL is generated from standard querying languageBased on classes mapped to the tables

var query = from c in context.Customers where c.Country = “Luxembourg” select new { c.LastName, c.FirstName };

© 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after

the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.