scaling oltp applications application design and hardware considerations
Download
Skip this Video
Download Presentation
Scaling OLTP Applications: Application Design and Hardware Considerations

Loading in 2 Seconds...

play fullscreen
1 / 42

Scaling OLTP Applications: Application Design and Hardware Considerations - PowerPoint PPT Presentation


  • 119 Views
  • Uploaded on

SESSION CODE: DAT206. Scaling OLTP Applications: Application Design and Hardware Considerations. Scaling Online Transaction Processing Applications with SQL Server 2008. Srik Raghavan Principal Lead Program Manager [email protected] Kevin Cox Principal Program Manager

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Scaling OLTP Applications: Application Design and Hardware Considerations' - beth


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
scaling oltp applications application design and hardware considerations

SESSION CODE: DAT206

Scaling OLTP Applications: Application Design and Hardware Considerations

Scaling Online Transaction Processing Applications with SQL Server 2008

SrikRaghavan

Principal Lead Program Manager

[email protected]

Kevin Cox

Principal Program Manager

[email protected]

session summary
Session Summary
  • SQL Server is a proven platform for OLTP workloads
  • SQL Server 2008 R2 offers features to assist with OLTP scalability
  • How to design hardware and software for scalability
agenda
Agenda
  • OLTP workload characteristics
  • OLTP application design principles
  • Scalability determinants and bottlenecks
    • SQL Server 2008 R2 Performance and Scale features
    • Demo
  • Scaling Up – Hardware to the rescue
  • Summary
oltp workload characteristics
OLTP Workload Characteristics
  • Typically used by line-of-business (LOB) applications
  • Has both read-write
  • Fine-grained inserts and updates
  • High transaction throughput e.g., 10s K/sec
  • Usually very short transactions e.g., 1–3 tables
  • Sometimes multi-step e.g., financial
  • Relatively small data sizes
application design principles
Application Design Principles

LOGICAL DESIGN

PHYSICAL DESIGN

ER MODEL

INDEXES

Design to leverage set-oriented processing power of SQL Server

Use development tools Visual Studio for Entity Framework design and DTA for tuning indexes

entity framework 4 0
Entity Framework 4.0
  • Development Approaches
    • Model First development –  Start from a Model and then have T-SQL and customized code generated.
    • Testing– New interface and guidance for building test suites faster.
  • Architectural Advantages
    • Persistence Ignorance – Use your own classes without needing to introduce interfaces or other elements
    • Applications Patterns – Discussing patterns like the Repository and UnitOfWork patterns with guidance on how to use them with the Entity Framework
    • Building N-Tier applications – Adding API’s and templates that make building N-Tier applications much easier
exploring the model
Exploring the Model
  • The Three Parts of the Model:

The image is taken from Julia Lerman’s book Programming Entity Framework, 1st Edition

application design best practices
Application Design Best Practices
  • Ensure good logical (E-R Model) and physical (indexes) DB design
  • Leverage set-oriented processing power of SQL Server
  • Update Statistics – ensure it is up to date!
  • Use DTA to assist with physical design
  • Avoid too many joins
  • Now let’s talk Physical Design
physical design best practices
Physical Design Best Practices
  • Reasons for Physical Design changes
    • Performance
    • Availability
    • Security
    • Auditing
  • Separate logs and data if possible
  • Spend time doing index analysis
  • Tune OLTP systems for high I/O per second
  • Tune data warehouse for high throughput per second
clustered index guidelines
Clustered index guidelines
  • Good when queries select large number of adjacent rows (range queries)
    • Create on the frequently used columns (in JOINs and WHERE with “=“, “<“, “>“, “BETWEEN”)
    • If number of returned rows is small – non-clustered index may be as efficient
    • Preferred on narrow and highly selective columns
  • Remember cost of maintenance:
    • Updates reorganize the table
      • Performance impact
      • Causes index fragmentation over time
non clustered index guidelines
Non-clustered index guidelines
  • Create for frequent search columns
  • Use on narrow and highly selective columns
  • Place on foreign key constraints (for join queries)
  • Check the workload for “covering” queries
    • Consider adding included columns
  • The drawback: maintenance cost
    • Frequent updates will ruin perf where there are too many indexes
  • Evaluate benefits of [not] indexing small tables
agenda13
Agenda
  • OLTP workload characteristics
  • OLTP application design principles
  • Scalability determinants and bottlenecks
    • SQL Server 2008 R2 Performance and Scale features
    • Demo
  • Scaling Up – Hardware to the rescue
  • Summary
oltp scalability dimensions determinants
OLTP Scalability Dimensions & Determinants

Dimensions

Resources

CPU

Memory

IO

Network

  • Transaction throughput
  • No. of concurrent users
  • Data size and growth rate

Key Design Pattern for Scalability: Divide and Conquer

typical cpu scaling issues
Typical CPU scaling issues

Symptoms

Causes

Queries not parameterized

Inefficient Query plan

Not enough stored procedures

MAXDOP is not set to 1

Statistics not updated

Table scan, range scan

SET option changes within SP

  • Plan compilation and recompilations
    • Plan reuse < 90% is bad
  • Parallel queries
    • Parallel wait type cxpacket > 10% of total waits
  • High runnable tasks or sos_scheduler_yield waits

Use stored procedures and

parameterize queries where possible

typical io scaling issues
Typical IO Scaling Issues

Symptoms

Causes

Aggravated by Big IOs such as table scans (bad query plans)

Non covering indexes

Sharing of storage backend – combine OLTP and DW workloads

TempDB bottleneck

Too few spindles, HBA’s

  • High average disk seconds per read (> 10 msec) and write (> 2 msec) for spindle based devices
  • Top 2 values for wait stats are one of - ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR, WRITELOG, PAGEIOLATCH_x

OLTP applications need to be designed for random I/O

typical blocking issues
Typical Blocking Issues

Symptoms

Causes

Higher isolation levels

Index contention

Lock escalation

Slow I/O

Sequence number problem

  • High average row lock or latch waits
  • Will show up in
    • sp_configure “blocked process threshold” and Profiler “Blocked process Report”
    • Top wait statistics are LCK_x. See sys.dm_os_wait_stats.

Use RCSI/Snapshot isolation

typical memory issues
Typical Memory Issues

Symptoms

Causes

Too many large scans (I/O)

Bad query plans

External (other process) pressure

  • Page life expectancy < 300 secs
  • SQL Cache hit ratio < 99%
      • Lazy writes/sec constantly active
      • Out of memory errors

Eliminate table scans in query plans

Use WSRM for non SQLServer processes on machine

agenda19
Agenda
  • OLTP workload characteristics
  • OLTP application design principles
  • Scalability determinants and bottlenecks
    • SQL Server 2008 R2 Performance and Scale features
    • Demo
  • Scaling Up – Hardware to the rescue
  • Summary
performance and scale features in sql server 2008 r2

Dynamic affinity (hard or soft)

Hot-add CPU support

Data Compression

Especially if you have I/O issues

Partitioning

Snapshot Isolation, RCSI

Control Point

Performance and Scale Features in SQL Server 2008 R2
  • Better query plans
    • Plan guides
    • Optimize for Unknown
  • Lock escalation hints
  • Resource governor
  • Transparency and Diagnostics – Xevent, DMV’s
  • > 64 thread support
plan guides
Plan Guides
  • Guide optimizer to use a fixed query plan
  • Helps with plan predictability
  • Use when you can’t change the application
  • Simple example
    • SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;
    • sp_create_plan_guide @name = N\'Guide2\', @stmt = N\'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC\', @type = N\'SQL\', @module_or_batch = NULL, @params = NULL, @hints = N\'OPTION (MAXDOP 1)\';
optimize for unknown
Optimize for Unknown
  • OPTIMIZE FOR UNKNOWN
    • Hint directs the query optimizer to treat as if no parameters values had been passed
    • Helps solve case where specific parameter values in query result in a bad plan for other values
    • Example
      • @p1=1, @p2=9998,
      • SELECT * FROM t WHERE col > @p1 or col2 > @p2 ORDER BY col1 OPTION (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))
lock escalation controls
Lock Escalation Controls
  • Check if lock escalation is causing blocking before disabling
  • Disable lock escalation at an object or table level
  • Enable lock to be escalated to the partition of the table
  • If the lock is escalated to partition (Hobt), it is not escalated further
    • Alter table T1 set (LOCK_ESCALATION = DISABLE)
resource governor
Resource Governor

Benefits

  • Provide deterministic Quality Of Service
  • Prevent run-away queries
  • Tames ill behaved Apps
  • DW & Consolidation scenarios

SQL Server 2008 RG

  • Workloads are mapped to Resource Pools
  • Online changes of groups and pools
  • Real-time Resource Monitoring
  • Up to 20 Resource Pools

SQL Server 2008

Admin Workload

OLTP Workload

Report Workload

Executive

Reports

OLTP

Activity

Backup

Admin Tasks

Ad-hoc

Reports

High

Max CPU 90%

Min Memory 10%

Max Memory 20%

Max CPU 20%

Application Pool

Admin Pool

extended events x event
Extended Events (XEvent)
  • Extremely high performance and extensible event and trace mechanism
  • Dynamic data collection on event fire
  • Integrated with ETW (Event Tracing for Windows)
    • Enables correlation with events exposed by Windows and third party applications
  • Hundreds of event points throughout SQL Server code base
  • Can identify session/statement level wait statistics
agenda27
Agenda
  • OLTP workload characteristics
  • OLTP application design principles
  • Scalability determinants and bottlenecks
    • SQL Server 2008 R2 Performance and Scale features
    • Demo
  • Scaling Up – Hardware to the rescue
  • Summary
core system components

Server

Core System Components

The key is to build a Balanced System without bottlenecks

NIC

4

Network

1

Memory

2

HBA

5

3

SQLFileLayout

DiskSubsystem

SQL Server is only part of the equation. Eco system needs to scale.

concepts numa
Concepts - NUMA

Front side bus contention increases w/ higher #CPUs

Symmetric Multiprocessor Architecture

Foreign

Memory

Access

Local Memory Access

Non-Uniform Memory Access

> 64 thread support

exploits NUMA

Foreign memory access > local memory access

disk subsystem configuration
Disk Subsystem Configuration

Trends

Configuration

Scale throughput with multiple HBA’s, spindles

If using RAID 10 get HBA that can do simultaneous read of the mirrors

Use multipathing for load balancing

HBA Queue Depth – default 32 too low at times

Configure to ensure healthy disk latencies < 10 msec

  • Disk sizes grew by 100 times over last 10 years
  • Disk access times only decreased by factor 10
  • Disk configuration of high-end systems is not just sizeof(data) but matter of expected I/O workload
  • Solid State Disks now more prevalent

For OLTP Design for IO/sec

and data warehouse design for throughput

network
Network

Trends

Configuration

Use Windows Server 2008

Offers Distributed network DPC processing

Suggest one NIC per NUMA node; maximum 4 to 8 cores per NIC

Use Adapter teaming

  • Gigabit is standard today. Usable bandwidth typically ~350 Mbps
  • 10GBit Ethernet adapters available now – high demand for iSCSI
  • Bandwidth not always bottleneck cause
    • Lack of parallel processing of network interrupts

Upgrade to Windows Server 2008 to gain these benefits

summary
Summary
  • SQL Server 2008 R2 and Windows together offer an ecosystem to scale the most demanding OLTP applications
  • Good application design is a precursor to great scalability
complete the evaluation form win
Complete the Evaluation Form & Win!
  • You could win a Dell Mini Netbook– every day – just for handing in your completed form! Each session form is another chance to win!

Pick up your Evaluation Form:

  • Within each presentation room
  • At the PASS Booth near registration area

Drop off your completed Form:

  • Near the exit of each presentation room
  • At the PASS Booth near registration area

Sponsored by Dell

slide35

Visit the

Microsoft Technical Learning Center Located in the Expo Hall

Microsoft Ask the Experts Lounge

Microsoft Chalk Talk Theater Presentations

Microsoft Partner Village

dat track scratch 2 win

Required Slide

Track PMs will supply the content for this slide, which will be inserted during the final scrub.

DAT Track Scratch 2 Win
  • Find the DAT Track Surface Table in the Yellow Section of the TLC
  • Try your luck to win a Zune HD
  • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win
resources

Required Slide

Resources

Learning

Sessions On-Demand & Community

Microsoft Certification & Training Resources

www.microsoft.com/teched

www.microsoft.com/learning

Resources for IT Professionals

Resources for Developers

http://microsoft.com/technet

http://microsoft.com/msdn

slide38

Required Slide

Complete an evaluation on CommNet and enter to win!

slide39

Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st

http://northamerica.msteched.com/registration

You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

slide40

© 2010 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.

ad