Transcript SQL Server 2014 Mission Critical Performance

SQL Server 2014
and the Data Platform
Karl-Heinz Sütterlin
Technology Solution Professional DataPlatform
[email protected]
Microsoft Switzerland
•
•
•
•
•
•
2014
2014
2012
2012
2014
New high-performance, memory-optimized OLTP engine integrated into SQL Server and designed for
modern hardware trends
Integrated into SQL Server relational database
Full ACID support
Memory-optimized indexes (no B-trees or buffer pools)
Non-blocking multiversion optimistic concurrency control (no locks or latches)
T-SQL compiled to native code
Customer benefits
•
•
•
•
Low latency
Up to 10x improvement in performance
2–5x improvement in scalability
Takes advantage of investments in SQL Server
Application is suited for in-memory processing
Application is “OLTP-like”
Application porting simplified under certain conditions
Benefits
In-Memory OLTP Tech Pillars
Drivers
High-performance data
operations
Frictionless scale-up
Efficient, business-logic
processing
Hybrid engine and
integrated experience
Main-memory
optimized
High concurrency
T-SQL compiled to
machine code
SQL Server integration
• Optimized for in-memory
data
• Indexes (hash and range)
exist only in memory
• No buffer pool
• Stream-based storage for
durability
• Multiversion optimistic
concurrency control with full
ACID support
• Core engine uses lock-free
algorithms
• No lock manager, latches, or
spinlocks
• T-SQL compiled to machine
code via C code generator
and Visual C compiler
• Invoking a procedure is just
a DLL entry-point
• Aggressive optimizations at
compile time
• Same manageability,
administration, and
development experience
• Integrated queries and
transactions
• Integrated HA and
backup/restore
Business
Hardware trends
Steadily declining memory
price, NVRAM
Many-core processors
Stalling CPU clock rate
TCO
Benefits
Main-memory
optimized
Drivers
High performance data
operations
In-Memory OLTP Tech Pillars
Table constructs
• Optimized for in-memory
data
• Indexes (hash and range)
exist only in memory
• No buffer pool
• Stream-based storage for
durability
Hardware trends
Steadily declining memory
price, NVRAM
Fixed schema; no ALTER TABLE; must drop/recreate/reload
No LOB data types; row size limited to 8,060
No constraints support (primary key only)
No identity or calculated columns, or CLR
Data and table size considerations
Size of tables = (row size * # of rows)
Size of hash index = (bucket_count * 8 bytes)
Max size SCHEMA_AND_DATA = 512 GB
IO for durability
SCHEMA_ONLY vs. SCHEMA_AND_DATA
Memory-optimized filegroup
Data and delta files
Transaction log
Database recovery
10
Benefits
In-Memory OLTP Tech Pillars
Drivers
Frictionless scaleup
High concurrency
• Multiversion optimistic
concurrency control
with full ACID support
• Core engine uses
lock-free algorithms
• No lock manager,
latches, or spinlocks
Hardware trends
Many-core processors
Impact of no locks or latches
Write-write conflict: design application for condition with try.catch
Applications dependent on locking; may not be a good candidate
Multiple versions of records
Increases the memory needed by memory-optimized tables
Garbage Collection used to reclaim old versions
Transaction scope
Support for Isolation Levels: Snapshot, Repeatable Read, Serializable
Commit time validation; again must retry logic to deal with failure
11
Multiversion
Optimistic
SNAPSHOT
REPEATABLE READ
SERIALIZABLE
Time
Transaction T1 (SNAPSHOT)
1
BEGIN
Transaction T2 (SNAPSHOT)
2
BEGIN
3
UPDATE t SET c1=‘bla’ WHERE c2=123
4
UPDATE t SET c1=‘bla’ WHERE
c2=123 (write conflict)
First writer
wins
Benefits
In-Memory OLTP Tech Pillars
Drivers
Efficient business-logic
processing
T-SQL compiled to
machine code
• T-SQL compiled to
machine code via C code
generator and Visual C
compiler
• Invoking a procedure is
just a DLL entry-point
• Aggressive optimizations
at compile-time
Hardware trends
Stalling CPU clock rate
Native compiled stored
procedures
Non-native compilation
Performance
High. Significantly less
instructions to go through
No different than T-SQL
calls in SQL Server today
Migration strategy
Application changes;
development overhead
Easier app migration as can
still access memoryoptimized tables
Access to objects
Can only interact with
memory-optimized tables
All objects; access for
transactions across memory
optimized and B-tree tables
Support for T-SQL
constructs
Limited
T-SQL surface area (limit on
memory-optimized
interaction)
Optimization, stats, and
query plan
Statistics utilized at CREATE
-> Compile time
Statistics updates can be
used to modify plan at
runtime
Flexibility
Limited (no ALTER
procedure, compile-time
isolation level)
Ad-hoc query patterns
15
• Interpreted T-SQL access
• Access both memory- and diskbased tables
• Less performance
• Virtually full T-SQL surface area
• When to use
• Ad-hoc queries
• Reporting-style queries
• Speeding up app migration
• Natively compiled stored
procedures
• Access only memory-optimized tables
• Max performance
• Limited T-SQL surface area
• When to use
• OLTP-style operations
• Optimize performance-critical business
logic
Client app
No improvements in
communication stack,
parameter passing,
result set generation
10–30x more efficient
Reduced log bandwidth
and contention; log
latency remains
Checkpoints are
background sequential
IO
TDS handler and session management
In-Memory
OLTP
compiler
Parser,
catalog,
algebrizer,
optimizer
Key
Proc/plan cache for ad-hoc TSQL and SPs
Interpreter for T-SQL, query
plans, expressions
Natively compiled
SPs and schema
Access methods
In-Memory OLTP engine for
memory-optimized tables and
indexes
Query
Interop
Buffer pool for tables and
indexes
SQL Server.exe
Memory-optimized table
filegroup
Transaction log
Data filegroup
Existing SQL
component
In-Memory OLTP
component
Generated .dll
SQL Server engine
New high-performance, memory-optimized OLTP
engine integrated into SQL Server and architected for
modern hardware trends
Memory-optimized
table file group
Transaction log
Data file group
Integration with SQL Server
Benefits
Hybrid engine and
integrated experience
Memory management
Use Resource Governor Pool to control InMemory OLTP memory
In-Memory OLTP Tech Pillars
SQL Server integration
Query optimization
Same SQL Server optimizer
HA/DR
Integrate with AlwaysOn FCI/AG
Backup/restore contains memoryoptimized tables (and data if durable)
Monitoring and troubleshooting
Integrated catalog views, DMVs,
performance monitor counters, extended
events, and more
Interaction with non-In-Memory
OLTP objects
Supported transaction interaction
(insert…select, JOIN, and more) with nonIn-Memory OLTP objects in database
Drivers
In-Memory OLTP component
• Same manageability,
administration, and
development experience
• Integrated queries and
transactions
• Integrated HA and
backup/restore
Business
TCO
19
Backup and restore
Failover clustering
AlwaysOn
DMVs, catalog views, performance monitor counters,
XEvents
SSMS
Symptom
Diagnosis
Solution
Data
Log
• What’s being delivered
• Main benefits
In-memory in the data warehouse
Data stored row-wise: heaps, B-trees, key-value
“By using SQL Server 2012 xVelocity, we were
able to extract about 100 million records in 2
or 3 seconds versus the 30 minutes required
previously. “
- Atsuo Nakajima Asst Director, Bank of Nagoya
•
Columnstore index
•
Both memory and disk
•
Built-in to core RDBMS engine
Customer Benefits:
C
1
Columnstore
index
representation
C C
2 3
C C
4 5
C6
•
10-100x faster
•
Reduced design effort
•
Work on customers’ existing hardware
•
Easy upgrade; easy deployment
25
Traditional storage models
Data stored row-wise: heaps, B-trees, key-value
Relational, dimensional, and map reduce
…
Columnstore storage model
Data stored column-wise: columnstore
• Each page stores data from a single
column
• Highly compressed
• More data fits in memory
• Each column can be accessed
independently
• Fetch only columns needed
• Can dramatically decrease I/O
C1
C2
C3
C4
C5
C6
Row groups and segments
Segments
• A segment contains values for one
•
•
•
•
column for a set of rows
Segments for the same set of rows
comprise a row group
Segments are compressed
Each segment stored in a separate LOB
A segment acts as a unit of transfer
between disk and memory
C1
C2
C3
C4
C5
C6
Row
group
Processing an example
Row groups
Segments
Compress each segment*
Some compress more than others
*Encoding and reordering not shown
Segment elimination
Segment elimination
Batch mode
Improving CPU utilization
• Biggest advancement in query processing in years
• Data moves in batch through query plan operators
• Highly-efficient algorithms
• Better parallelism
QP vector operators
Batch object
Bitmap of qualifying rows
Column vectors
The next generation
101-million row table and index space
19.7 GB
10.9 GB
6.9 GB
5.0 GB
4.0 GB
1.8 GB
1
2
3
4
5
6
How it works
• CREATE CLUSTERED COLUMNSTORE
• Organizes and compresses data into columnstore
• BULK INSERT
Partition
Columnstore
• Creates new columnstore row groups
• INSERT
• Rows are placed in the row store (heap)
• When row store is big enough, a new columnstore row group is created
• DELETE
• Rows are marked in the deleted bitmap
• UPDATE
• Delete plus insert
• Most data is in columnstore format
Row store
Deleted
bitmap
What’s new?
• Adds an additional layer of compression on top of
the inherent compression used by columnstore
• Shrink on-disk database sizes by up to 27 percent
• Compression applies per partition and can be set either during index creation or
during rebuild
Insert and updating data
Load sizes
• Bulk insert
• Creates row groups of one million rows; last row group is probably not full
• But if less than 100,000 rows, data will be left in a row store
• Insert/update
• Collects rows in a row store
• Tuple Mover
• When the row store reaches one million rows, convert to a columnstore row group
• Runs every five minutes by default
• Started explicitly by ALTER INDEX <name> ON <table> REORGANIZE
Columnstore enhancements summary
• What’s being delivered
• Clustered and updateable columnstore index
• Columnstore archive option for data compression
• Global batch aggregation
• Main benefits
• Real-time super fast data warehouse engine
• Ability to continue queries while updating without the need to drop and
recreate index or partition switching
• Huge disk space saving due to compression
• Ability to compress data 5–15x using archival per-partition compression
• Better performance and more efficient (less memory) batch query processing
using batch mode rather than row mode
• What’s being delivered
•
•
•
•
Increase number of secondaries from four to eight
Increase availability of readable secondaries
Support for Windows Server 2012 CSV
Enhanced diagnostics
• Main benefits
• Further scale out read workloads across (possibly geo-distributed) replicas
• Use readable secondaries despite network failures (important in geo-distributed environments)
• Improve SAN storage utilization
• Avoid drive letter limitation (max 24 drives) via CSV paths
• Increase resiliency of storage failover
• Ease troubleshooting
Failover Clustering
Database Mirroring
Log Shipping
Flexible
Integrated
Efficient
DB
DB
DB
AG
AG-VNN
AG-IP
•
•
•
Availability Group Virtual Name allow applications to failover seamlessly on availability group
failover
Readable secondary allows offloading read queries to secondary
Automatic redirect with Routing Lists or manual connect to Secondary
WSFC DENALIWINCLU1
CALGARY
TORONTO
VANCOUVER
DB2
DB2
DB2
DB1
DB1
DB1
SalesStore
AG1
SalesStore
1 (VNN)
AG-IP
Secondary
Primary
Primary
•
•
-server SalesStore1
•
Application retry during failover
-server SalesStore1;
ApplicationIntent=ReadOnly
Reports
Secondary
•
Connect to new primary/new
secondary once failover is complete
and the listener is online
become unavailable
• Temporarily (for example, gateway failure)
• Permanently (for example, flooding, fire….)
expensive
• Site rent + maintenance
• Hardware
• Ops
secondary replicas
• Replicas continuously synchronize
At best region:
• Political considerations
• Latency
TCO
• Virtual machine and storage
redmond.corp.microsoft.com
Availability group
uswest.internal.cloudapp.net
Virtual network
Windows cluster
VPN
device
• What’s being delivered
• Use of non-volatile drives (SSD) to extend buffer pool
• NUMA-Aware large page and BUF array allocation
• Main benefits
• BP extension for SSDs
• Improve OLTP query performance with no application changes
• No risk of data loss (using clean pages only)
• Easy configuration optimized for OLTP workloads on commodity servers (32 GB RAM)
Example:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE‘,
SIZE = 50 GB)
• Scalability improvements for systems with more than eight sockets
DMVs
XEvents
• What’s being delivered
• New cardinality estimator
• Incremental statistics for partition
• Parallel SELECT INTO
• Main benefits
• Better query performance:
• Better choice of query plans
• Faster and more frequent statistics refresh on partition level
• Consistent query performance
• Better supportability using two steps (decision making and execution) to enable better query plan
troubleshooting
• Loading speed into table improved significantly using parallel operation
• What’s being delivered
• Single partition online index rebuild
• Managing Lock Priority for table SWITCH and online index rebuild
• Built-in diagnostics
• Main benefits
• Increased concurrency and application availability
• New partition-level granularity for online index rebuild enables lower
resource (CPU/memory) usage for customers who need uninterrupted
access
• Provide customers with greater control over impacting running
transactions if using switch partition or online index rebuild commands
• What’s being delivered
• Three new permissions (CONNECT ANY DATABASE, IMPERSONATE ANY LOGIN, and SELECT ALL
USER SECURABLES)
• Main benefit
• Greater role separation
• Ability to create new roles for database administrators who are not sysadmin (super user)
• Ability to create new roles for users or apps with specific purposes
Examples:
1. Database administrators but cannot see user data
2. Database administrators but cannot modify logins
3. Database administrators but cannot impersonate any logins
4. New roles (for example, auditors) to read all data but not database administrators
5. New roles to read all metadata for monitoring purposes (for example, SCOM) but cannot see user
data
6. New roles for middle tier app to impersonate any login except sa, sysadmin, or other high
privilege logins
-- Create a login
CREATE LOGIN Mary WITH PASSWORD = 'strongpassword'
-- Create a new server role and we will limit the access
CREATE SERVER ROLE MyLimitedAdmin AUTHORIZATION sysadmin
-- Add login Mary into the newly created server role
ALTER SERVER ROLE MyLimitedAdmin ADD MEMBER Mary
-- Add CONTROL SERVER permission to the new server role to manage the database instance
GRANT CONTROL SERVER to MyLimitedAdmin
-- However, we deny the new server role to see any user data but can do other DBA tasks,
such as backup the databases
DENY SELECT ALL USER SECURABLES to MyLimitedAdmin
In-Memory Columnstore in PDW V2 Appliance and
SQL Server 2014
xVelocity in-memory columnstore in PDW columnstore index as primary data
store in a scale-out MPP Data Warehouse - PDW V2 Appliance
• Updateable clustered index
• Support for bulk load and insert/update/delete
• Extended data types – decimal/numeric for all precision and scale
• Query processing enhancements for more batch mode processing (for
example, Outer/Semi/Antisemi joins, union all, scalar aggregation)
Customer benefits
• Outstanding query performance from in-memory columnstore index
• 600 GB per hour for a single 12-core server
• Significant hardware cost savings due to high compression
• 4–15x compression ratio
• Improved productivity through updateable index
• Ships in PDW V2 appliance and SQL Server 2014
•
63
Overview
One standard node type
Host 1
Moving from SAN to JBODs
Host 2
Host 3
JBOD
IB and
Ethernet
Backup and Landing Zone are now reference architectures, and not in the
basic topology
Host 4
Direct attached SAS
Scale unit concept
Storage details
To take advantage of the fact that we have ~2x the number of spindles, we use more
files per filegroup to better align SQL Server activity to actual disks available and
Node 1: Distribution
– file 1
provide better parallelization
of disk BIO.
.
.
.
.
Replicated
.
. data
user
Node
V11: Distribution B – file 2
.
.
1 FG
. per DB,
.
.
.
8 files per FG
V2
1 FG per DB,
16 files per FG
Distributed
user data
1 FG per distribution,
1 file per FG
1 FG per distribution,
2 files per FG
TempDB and
Log
1 FG per DB,
1 file per FG
1 FG per DB,
16 file per FG
Overall, we expect to
.
.
see.
70 percent
.
.
.
higher
I/O bandwidth.
Fabric storage (VHDXs for node)
Hot spares
JBOD 1
Comparison with V1: the basic 1-Rack
• Pure hardware costs
are ~50 percent lower
Control Node
• Price per raw TB is
close to 70 percent
lower due to higher
capacity
Mgmt. Node
LZ
• 70 percent more disk
I/O bandwidth
Backup Node
CONTROL RACK
Infiniband
and Ethernet
•
•
•
•
Fibre Channel
Estimated total hardware
RACK 1
DATA RACK
160 cores on 10 compute nodes
1.28 TB of RAM on compute
Up to 30 TB of TempDB
Up to 150 TB of user data
$ component list price: $1 million
Infiniband
and Ethernet
•
•
•
•
128 cores on 8 compute nodes
2 TB of RAM on compute
Up to 168 TB of TempDB
Up to 1 PB of user data
total hardware
$ Estimated
component list price: $500,000
Hardware architecture
Modular design
Modular
components
Infiniband Switch
Ethernet Switch
Server
Server
Server
Base Scale Unit
Passive Scale Unit
Infiniband Switch
Base Scale Unit
Passive Scale Unit
Server
Server
Storage
Server
Server
Storage
Type 1
Up to 8 servers
Minimum footprint of
2 servers
Type 2
Up to 9 Servers
Minimum footprint of
3 servers
Storage
Storage
Server
Server
Server
Storage
Capacity Scale Unit
Capacity Scale Unit
Storage
Server
Server
Server
Server
Server
Storage
Server
Server
Server
Server
Server
Server
Server
Server
Storage
Ethernet Switch
Storage
Base Scale Unit
Base Scale Unit
Storage
Increased virtual processor and memory
Enables SQL Server virtual machine to use up to 64 virtual
processors and 1 TB of memory
Increased logical processor and memory
Enables SQL Server to use up to 640 logical processors
and 4 TB of memory
Increased cluster node scalability
Supports SQL Server clusters up to 64 nodes
Increased virtual machine density
Up to 8,000 SQL Server virtual machines per cluster
Support for up to 320 logical processors and 4 TB of memory
Support for NUMA
QoS – Network Bandwidth
Enforcing
Windows NIC Teaming
Cluster-Aware Updating (CAU)
Online
Dynamic Quorum
Windows Server Core
Online VHDX resize (Windows Server 2012 R2)
Online
Updating …
Online
• What’s being delivered
• Add max/min IOPS per volume to Resource Governor pools
• Add DMVs and perfcounters for IO statistics per pool per volume
• Update SSMS Intellisense for new T-SQL
• Update SMO and DOM for new T-SQL and objects
• Main benefits
• Better isolation (CPU, memory, and IO) for multitenant workloads
• Guarantee performance in private cloud and hosters scenario
CREATE RESOURCE POOL pool_name
[ WITH
( [ MIN_CPU_PERCENT = value ]
[ [ , ] MAX_CPU_PERCENT = value ]
[ [ , ] CAP_CPU_PERCENT = value ]
[ [ , ] AFFINITY {SCHEDULER = AUTO |
(Scheduler_range_spec) | NUMANODE =
(NUMA_node_range_spec)} ]
[ [ , ] MIN_MEMORY_PERCENT = value ]
[ [ , ] MAX_MEMORY_PERCENT = value ]
[ [ , ] MIN_IOPS_PER_VOLUME = value ]
[ [ , ] MAX_IOPS_PER_VOLUME = value ])
]
Category
Metric
Largest single database
350 TB
Largest table
1.5 trillion rows
Biggest total data 1 application
88 PB
Highest database transactions per second 1 130,000
server (from performance monitor)
Fastest I/O subsystem in production (SQLIO 18 GB/sec
64k buffer serial read test)
Fastest “real-time” cube
Millisecond latency
Data load for 1 TB
30 minutes
Largest MOLAP cube
24 TB
Download SQL Server 2014 CTP1
Call to action
Stay tuned for availability
www.microsoft.com/sqlserver
© 2013 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