SQL Server on VMware - PowerPoint PPT Presentation

Loading...

PPT – SQL Server on VMware PowerPoint presentation | free to download - id: 3f3f41-MTYzZ



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Server on VMware

Description:

SQL Server on VMware Jonathan Kehayias (MCTS, MCITP) SQL Database Administrator Tampa, FL Agenda SQL Initiative Overview Performance Metrics I/O Metrics Processor ... – PowerPoint PPT presentation

Number of Views:42
Avg rating:3.0/5.0
Slides: 29
Provided by: JonathanK154
Learn more at: http://www.sqlclr.net
Category:
Tags: sql | server | vmware

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Server on VMware


1
SQL Server on VMware
  • Jonathan Kehayias (MCTS, MCITP)
  • SQL Database Administrator
  • Tampa, FL

2
Agenda
  • SQL Initiative Overview
  • Performance Metrics
  • I/O Metrics
  • Processor
  • Performance Counters
  • High Availability Configuration
  • Manageability Gains

3
SQL Initiative
  • Primary Objective
  • Disaster Recovery
  • Success Criteria
  • Immediate failover to remote data center with
    minimal data loss
  • Performance

4
PerformanceVMware ESX Server, 3.0.2, 63195
5
PerformanceSQL Virtual Machine Configuration
6
PerformanceSQLIO Benchmarks
  • SQLIO is a tool provided by Microsoft which can
    also be used to determine the I/O capacity of a
    given configuration.
  • SQL Server stores data 8K pages allocated in
    blocks of 8 as 64K extents. Typical SQL I/O
    operations involve Random Reads of extenteRs from
    disk.
  • SQLIO benchmarks on this SQL Server for 64K
    Random Read I/O with 2 threads simulating the
    recommended setting of one file per processor
    core for SQL Server were equivalent or better
    than common physical hardware.

7
Performance (VMWare)Random Read I/O By The
Numbers
  • Drive Format Test IOs/sec MBs/sec
  • READ
  • Default Read 8KB random 2286.33 17.86Default
    Read 64KB random 1940.86 121.30Default Read
    128KB random 1059.25 132.40Default Read 256KB
    random 500.54 125.13Default Read 8KB
    sequential 2549.82 19.92Default Read 64KB
    sequential 1397.24 87.32Default Read 128KB
    sequential 766.72 95.84Default Read 256KB
    sequential 466.00 116.50WRITEDefault Write
    8KB Random 1475.78 11.52Default Write 64KB
    Random 609.97 38.12Default Write 128KB Random
    366.03 45.75Default Write 256KB Random 210.17
    52.54Default Write 8KB Sequential 1560.25
    12.18Default Write 64KB Sequential 634.69
    39.66 Default Write 128KB sequential 222.10
    27.76 Default Write 256KB sequential 165.52
    41.38

8
Dell PowerEdge 6650 2x 2.7 GHz 2GB RAM4x 36GB
RAID 10 array
  • Drive Format Test IOs/sec MBs/sec
  • READ
  • Default Read 8KB random 656 5.1Default Read
    64KB random 258 16.1Default Read 128KB random
    149 18.6Default Read 256KB random 82
    20.4Default Read 8KB sequential 3246
    25.4Default Read 64KB sequential 378
    23.6Default Read 128KB sequential 188
    23.5Default Read 256KB sequential 94
    23.5WRITEDefault Write 8KB Random 1567
    12.2Default Write 64KB Random 809
    50.5Default Write 128KB Random 486
    60.7Default Write 256KB Random 333
    83.2Default Write 8KB Sequential 7112
    55.6Default Write 64KB Sequential 1889
    118.1Default Write 128KB sequential 977
    122.1Default Write 256KB sequential 494
    123.5
  • Physical SQLIO outputs were obtained from the
    SQL Server Performance website Forums
  • http//sql-server-performance.com/Community/forum
    s/t/15370.aspx

9
Dual 3.0Ghz Xeon Dual 2GB FC HBAIBM DS4300 SAN
RAID 10 (10 x 74GB 15K)
  • Drive Format Test IOs/sec MBs/secSQLIO 8k
    sector
  • Read - Random 2096.15 16.38Read -
    Sequential 3651.36 28.53Write - Random
    1985.08 15.51Write - Sequential 1914.68
    14.96SQLIO 32k sector
  • Read - Random 1653.85 51.68Read -
    Sequential 2736.05 85.50Write - Random
    1125.63 35.18Write - Sequential 1337.12
    41.79SQLIO 64k sector Read - Random
    1280.91 80.06Read - Sequential 1889.55
    118.10Write - Random 724.24 45.27Write -
    Sequential 889.01 55.56
  • Physical SQLIO outputs were obtained from the
    SQL Server Performance website Forums
  • http//sql-server-performance.com/Community/forum
    s/t/15370.aspx

10
DELL 6850 Quad Xeon 3.4ghz w/4GB RAM EMC
Clariion CX-700 RAID 10
  • Drive Format Test IOs/sec MBs/sec
  • 8k random write 14884.96 116.2864k random
    write 1573.03 98.31128k random write
    1110.79 138.84256k random write 722.48
    180.628k seq. write 16545.36 129.2664k
    seq. write 3142.00 196.37128k seq. write
    1573.62 196.70256k seq. write 787.21
    196.808k random read 5776.05 45.1264k
    random read 2857.16 178.57128k random read
    1567.23 195.90256k random read 787.22
    196.808k seq. read 9200.54 71.8764k
    seq. read 2805.23 175.32128k seq. read
    1506.95 188.36256k seq. read 783.07
    195.76
  • Physical SQLIO outputs were obtained from the
    SQL Server Performance website Forums
  • http//sql-server-performance.com/Community/forum
    s/t/15370.aspx

11
PerformancePerformance Counter Monitoring
  • To understand how well a SQL Server is
    performing the SQL Server as well as Windows
    Subsystem Performance Counters need to be
    Monitored.
  • Key Counters to monitor include
  • Processor/Processor Time should remain below
    80.
  • Processor/Privileged Time should remain below
    20
  • SQL Server General/User Connections
  • Batches/Sec
  • Page Life Expectancy
  • Pages/Sec
  • Memory Grants Pending
  • Lazy Writes/sec
  • For further information, take a look at the
    following Screencast series by Kevin Kline (SQL
    Server MVP and Professional Association for SQL
    Server (PASS) President)
  • http//searchsqlserver.techtarget.com/generic/0,29
    5582,sid87_gci1302600,00.html

12
PerformancePerformance Counters (Processor Time)
lt 80 average
13
PerformancePerformance Counters
(Processor\Privileged Time)
lt 20 Average
14
PerformancePerformance Counters (User
Connections)
This is just a reference counter to be used in
tandem with other counters to view system
activity.
15
PerformancePerformance Counters (Batches/sec)
This is a reference to the amount of activity the
Server is performing. It is used along with
other counters like Page Splits/sec to determine
if there are problems.
16
PerformancePerformance Counters (Buffer Cache)
Should remain as close to 100 as possible.
Consistent drops below 95-90 signals Memory
Pressure
17
PerformancePerformance Counters (Page Life
Expectance)
Page Life in the cache should ideally remain over
300 seconds. Consistent drops below this should
be investigated and can signal Memory Pressure on
the server.
18
PerformancePerformance Counters (Memory
pages/sec)
The rate at which pages are read from or written
to disk. If gt 100 on a slow disk subsystem or gt
600 on a fast disk subsystem it should be
investigated.
19
Performance Conclusions
  • 1 Compared to various disk configurations of
    physical implementations with local storage, we
    experience 10x performance for disk subsystem I/O
  • 2 Critical performance counters SQL maintains
    industry acceptable performance
  • 3 Ability to consolidate multiple VMs along
    with SQL server, 5 to 71 to save costs on
    physical hardware, rack space, power, cooling and
    integrate into DR plan.

20
Consolidation Architecture
  • Single SQL server per ESX host
  • Multiple SQL Servers never reside on the same ESX
    host
  • Logical placement of VMs to eliminate contention
    of resources
  • Web and App server never communicate with the SQL
    server on the same host

21
High Availability
  • SAN Storage Mirrored for Disaster Recovery to
    Chicago Datacenter.
  • Quarterly Failover Tests of key SQL Servers on
    Chicago Network with zero data loss at failover.

22
High Availability
  • VMotion
  • Eliminates need for Mirroring Solutions for
    Hardware redundancy. In the event of a host
    hardware failure the Virtual Machines can be hot
    migrated to another host
  • Allows live migration of Servers during high load
    operation to shift load to more powerful hosts as
    needed.

23
Manageability
  • Snapshot Technology
  • Provides a point in time recovery point for risky
    operations such as upgrading Server OS and or SQL
    Server.
  • Hot Add Disk Arrays
  • Allows Zero Down Time additions of new Storage
    LUNS as database grow in size
  • HBA Load Balancing
  • Allows Disk I/O Load balancing across redundant
    paths to the SAN storage.

24
Manageability
  • Rapid Scale Up
  • Adding additional vCPUs and Memory is only a
    reboot away, provided the host has available
    resources.
  • Easily upsize a server for end of month
    processing when it requires the most power while
    minimizing its footprint while under minimal load.

25
Manageability
  • VMWare Infrastructure Client
  • Immediate shared console level access to the SQL
    Server provides remote administration and rapid
    response for critical server outages.
  • No risk of a BSOD requiring physical access to
    the server or of a stuck ILO interface on a
    server.
  • Integrated performance monitoring of processor,
    memory, disk and networking counters.

26
Manageability
  • VMWare Infrastructure Client (contd)
  • Running history of Events occurring through the
    client including Server Resets, Migrations, and
    Reconfigurations.
  • Integrated Alarms are configurable for out of
    tolerance counter statuses.

27
Lessons Learned
  • 1 Dont lock pages in memory if you plan to do
    VMotion.
  • 2 Be cautious when implementing Microsoft
    Recommended Best Practices that affect system
    configurations and test in Development before
    deploying in Production. Some may not be
    compatible with VMotion.
  • 3 Dont accept a vendors statement that VMware
    is the problem look deeper, and you can
    generally disprove this statement.

28
Conclusions
  • Performance Metrics
  • I/O Metrics 10x performance, Processor and
    Performance Counters
  • High Availability Configuration
  • VMotion to load balance during end of month
    reconciliations keeps ahead of the business
  • Reduced downtime with hot add disk
  • Manageability Gains
  • SOX compliance for Disaster Recovery
  • Virtual Center Console for team collaboration
  • Cost Avoidance
  • Reduces need for Mirroring hardware and software
  • Consolidation of hardware both primary and DR
About PowerShow.com