Docstoc

DAT206_ Scaling OLTP Applications_ Application Design and - MSDN

Document Sample
DAT206_ Scaling OLTP Applications_ Application Design and - MSDN Powered By Docstoc
					srikr@microsoft.com
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
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
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
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
                                           Bottleneck


Transaction throughput                                                 CPU
No. of concurrent users                                  Transaction   Memory
                                  Poor
                                                             takes
Data size and growth rate        scaling
                                                            longer     IO
                                                                       Network
                                           Transaction
                                              holds
                                            resources




    Key Design Pattern for Scalability: Divide and Conquer
Plan compilation and recompilations                  Queries not parameterized
  Plan reuse < 90% is bad
                                                     Inefficient Query plan
Parallel queries                                     Not enough stored procedures
  Parallel wait type cxpacket > 10% of total waits
                                                     MAXDOP is not set to 1
High runnable tasks or sos_scheduler_yield waits
                                                     Statistics not updated
                                                     Table scan, range scan
                                                     SET option changes within SP
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
Better query plans            Dynamic affinity (hard or soft)
  Plan guides                 Hot-add CPU support
  Optimize for Unknown        Data Compression
Lock escalation hints           Especially if you have I/O issues
Resource governor             Partitioning
Transparency and              Snapshot Isolation, RCSI
Diagnostics – Xevent, DMV’s   Control Point
> 64 thread support
                                                               Benefits
                   SQL Server 2008
                                                                Provide deterministic Quality Of
Admin Workload       OLTP Workload           Report Workload    Service
  Backup                  OLTP                    Executive     Prevent run-away queries
                          Activity                 Reports
                                                                Tames ill behaved Apps
Admin Tasks                                        Ad-hoc       DW & Consolidation scenarios
                                                   Reports
                           High

                                                               SQL Server 2008 RG
                                                                Workloads are mapped to
 Min Memory 10%                                                 Resource Pools
 Max Memory 20%                   Max CPU 90%                   Online changes of groups and
  Max CPU 20%                                                   pools
                                                                Real-time Resource Monitoring
      Admin Pool                     Application Pool
                                                                Up to 20 Resource Pools
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
The key is to build a Balanced System without bottlenecks
                           NIC
                                                         4
         1                                                   Network
             Server

       Memory         2
                          HBA

                                     3                              5
                                    Disk Subsystem                   SQL File Layout

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

                                                             Front side bus contention increases w/ higher #CPUs


      LP 0            LP 1       LP 2       LP 3            LP 4        LP 5         LP 6        LP 7

                             Symmetric Multiprocessor Architecture




                                               Foreign
Local Memory Access
                                               Memory
                                               Access

                                Non-Uniform Memory Access
                                Foreign memory access > local memory access
       For OLTP Design for IO/sec
and data warehouse design for throughput
Upgrade to Windows Server 2008 to gain these benefits
Category                               Metric
Largest single database                80 TB
Largest table                          20 TB
Biggest total data 1 customer          2.5 PB
Highest transactions per second 1 db   36,000
Fastest I/O subsystem in production    18 GB/sec
Fastest “real time” cube               15 sec latency
Data load for 1TB                      20 minutes
Largest cube                           4.2 TB
•Sponsored by Dell
             Visit the
Microsoft Technical Learning Center
     Located in the Expo Hall
www.microsoft.com/teched       www.microsoft.com/learning




http://microsoft.com/technet   http://microsoft.com/msdn
    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 registration
             Join us in Atlanta next year

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:11/30/2012
language:English
pages:42