Transcript SQL2014NewFeaturesx

Presentation by Kevin G. Boles
[email protected]
Kevin G. Boles
SQL Server Consultant Extraordinaire
“The Wolf” for the Relational Engine
Indicium Resources, Inc.
AIM/Twitter: TheSQLGuru
GTalk/GMail: [email protected]
MVP 2007-2012, MCT, MCITP, yada-yada-yada





World-class Better-than-average relational
engine expert 
Almost 45,000 hours invested in SQL Server
Absolutely LOVE doing targeted performance
analysis and tuning work, and designing and
building high-scale, highly-performing
database applications
VERY good at making others better at
interacting with SQL Server
Couldn’t SSAS my way out of a paper bag
◦ … with both ends open! 

What’s being delivered

Main benefits
◦ New cardinality estimator
◦ Incremental statistics for partition!
◦ Parallel SELECT INTO
◦ Better query performance:
 Better choice of query plans
 Faster/more frequent statistics refresh at 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




SQL Express+ Feature
SQL Server transaction commits can now be either
fully durable or delayed durable (aka Lazy Commit)
Delayed transaction durability reduces the latency
due to log I/O by keeping the transaction log
records in memory and writing to the transaction
log in batches, thus requiring fewer I/O operations
When to use delayed transaction durability
◦ You can tolerate some data loss
◦ You are experiencing a bottleneck on transaction log
writes.
◦ Your workloads have a high contention rate.

The in-memory transaction log is flushed to
disk when:
◦ A fully durable transaction in the same database makes a
change in the database and successfully commits (KEY
GOTCHA HERE!)
◦ The user executes the system stored procedure
sp_flush_log successfully
◦ The in-memory transaction log buffer fills up and
automatically flushes to disk
ALTER DATABASE … SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }
DELAYED_DURABILITY = { OFF | ON } (with NATIVE_COMPILED sprocs ONLY
COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
Index Storage Design
• Column-Orientation
–
–
Store data vertically instead of per-row
String Dictionaries for variable-length data
–
–
–
–
DW queries usually pick only a subset of columns
You can do the IO only for those columns
We can also compress that data effectively since it often has lots of duplicates
Space savings of 1.5x-2x vs. a row-based page-compressed equivalent
• Segment data into groups (1 million rows/group)
• Benefits
IO Patterns for:
CI Scan
Column-based scan 3 cols
Column-based w/Compression




Column-Oriented
Sized to fit within L2 cache
(1000 rows at a time)
Multiple Operators work on a
batch sequentially
Goal: Reduce avg. per-tuple cost
◦ Reducing L2 data and instruction
cache misses
Latency Comparison Numbers
L1 cache reference
0.5
Branch mispredict
5
L2 cache reference
7
Mutex lock/unlock
25
Main memory reference
100
Compress 1K bytes with Zippy
3,000
Send 1K bytes over 1 Gbps network
10,000
Read 4K randomly from SSD*
150,000
Read 1 MB sequentially from memory
250,000
Round trip within same datacenter
500,000
Read 1 MB sequentially from SSD*
1,000,000
Disk seek
10,000,000
Read 1 MB sequentially from disk
20,000,000
Send packet CA->Netherlands->CA
150,000,000
ns
ns
ns
ns
ns
ns
ns
ns
ns
ns
ns
ns
ns
ns
14x L1 cache
20x L2 cache, 200x L1
0.01
0.15
0.25
0.5
1
10
20
150
ms
ms
ms
ms
ms
ms
ms
ms
4X memory
20x datacenter roundtrip
80x memory, 20X SSD
Credit
Peter Norvig: http://norvig.com/21-days.html#answers
Another Way to Look At It
L1 cache reference
Branch mispredict
L2 cache reference
Mutex lock/unlock
Main memory reference
Compress 1K bytes with Zippy
Send 1K bytes over 1 Gbps network
Read 4K randomly from SSD
Read 1 MB sequentially from memory
Round trip within same datacenter
Read 1 MB sequentially from SSD
Disk seek
Read 1 MB sequentially from disk
Send packet CA->Netherlands->CA
:
:
:
:
:
:
:
:
:
:
:
:
:
:
0:00:01 (one second)
0:00:10
0:00:14
0:00:50
0:03:20
!!!
1:40:00
5:33:20
3 days, 11:20:00
5 days, 18:53:20
!!!
11 days, 13:46:40
23 days, 3:33:20
231 days, 11:33:20 !!!
462 days, 23:06:40 !!!
3472 days, 5:20:00
Only One of these
The Column Store
Index IS the data now!
Hopefully lots of
these, vast majority
with ~1M row
segments!
Hopefully few of these







Table consists of column store and row
store
DML (update, delete, insert) operations
leverage delta store
INSERT Values
◦
Always lands into delta store
◦
◦
Logical operation
Data physically remove after REBUILD
operation is performed.
DELETE
UPDATE
◦
DELETE followed by INSERT.
◦
if batch < 100k, inserts go into delta store,
otherwise columnstore
BULK INSERT
SELECT
◦

Unifies data from Column and Row stores internal UNION operation.
“Tuple mover” converts data into columnar
format once segment is full (1M of rows)

What’s being delivered
◦
◦
◦
◦

Clustered and updateable columnstore index
Columnstore archive option for data compression
Global batchaggregation
MUCH better Batch Mode operations, fewer limitations
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
◦ Better performance and more efficient (less memory)
batch query processing using batch mode rather than
row mode
SQL Server engine
New high-performance, memory-optimized online
transaction processing (OLTP) engine integrated into
SQL Server and architected for modern hardware
trends
Memoryoptimized table
file group
Transaction log
Data file group









bit
All integer types: tinyint, smallint, int, bigint
All money types: money, smallmoney
All floating types: float, real
date/time types: datetime, smalldatetime,
datetime2, date, time
numeric and decimal types
All non-LOB string types: char(n), varchar(n),
nchar(n), nvarchar(n), sysname
Non-LOB binary types: binary(n), varbinary(n)
Uniqueidentifier








No DML triggers
No FOREIGN KEY or CHECK constraints
No IDENTITY columns (added in CTP 2)
8060 byte hard limit for row length
No UNIQUE indexes other than for the
PRIMARY KEY
A maximum of 8 indexes, including the index
supporting the PRIMARY KEY
No schema changes are allowed once a table
is created (drop/recreate)
Indexes are always and ONLY created at as
part of the table creation

What’s being delivered

Main benefits

Scalability improvements for systems with more than
eight sockets
◦ Use of non-volatile drives (SSD) to extend buffer pool
◦ NUMA-Aware large page and BUF array allocation
◦ 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)
◦ Standard Edition and up: 4X RAM Std., 32X RAM Ent. Ed.
Example:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE‘, SIZE = 50 GB)

Four new permissions
◦
◦
◦
◦

CONNECT ANY DATABASE(server scope)
IMPERSONATE ANY LOGIN (server scope)
SELECT ALL USER SECURABLES (server scope)
ALTER ANY DATABASE EVEN SESSION (database scope)
Main benefit
◦ Greater role separation to restrict multiple DBA roles
◦ Ability to create new roles for database administrators who are not sysadmin
◦ Ability to create new roles for users or apps with specific purposes
Examples:
1. Database administrator but cannot see user data
2. Database administrator but cannot modify logins
3. Database administrator but cannot impersonate any logins
4. New roles (e.g. auditors) to read all data but not admin rights
5. New roles to read all metadata for monitoring purposes (e.g. 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






Ability to differentiate workloads
Ability to monitor resource usage per group
Limit controls to enable throttled execution
or prevent/minimize probability of “runaways”
Prioritize workloads
Provide predictable execution of workloads
Specify resource boundaries between
workloads




Represents physical resources of server
Can have one or more workloads
assigned to pool
Pools divided into shared and nonshared
Pools control min/max for CPU/memory
CREATE RESOURCE POOL pool_name
and now IOPS
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 ])

What’s being delivered

Main benefits
◦ 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
◦ Better isolation (CPU, memory, and IO) for multitenant
workloads
◦ Guarantee performance in private cloud and hosters
scenario
Support SQL Server images in
Azure Gallery
-Provide quick and flexible SQL
Server provisioning for IaaS
scenarios
-Support SQL Server
configuration as part of the
provisioning process
-Need to be faster than full
installation
Remove limitations that currently
exist
This has been long requested
by customers
CREATE VIRTUAL MACHINE
VM OS Selection
Microsoft SQL Server 2012 Web Editi...
Microsoft SQL Server 2012 Standard ...
Microsoft SQL Server 2012 Evaluatio ...
Microsoft SQL Server 2008 R2 Web E...
Microsoft SQL Server 2008 R2 Standa...
VM OS
Selection
SQL Server 2012 Web Edition Service Pack
1 (64-bit) on Windows Server 2008 R2
Service Pack 2. This image contains the
full version of SQL Server, including all
components except Distributed Replay,
Always On, and Clustering capabilities.
Some SQL Server components require
additional setup and configuration before
use. Medium is the minimum
recommended size for this image. To
evaluate advanced SQL Server 2012
capabilities, Large or Extra-Large sizes are
recommended.
1
2
3

Sysprep support for

Delivered in SQL
Server 2012 SP1 CU2
Database engine
Reporting Services
Analysis Services
Integration Services
Management Tools
(SSMS)
◦ Other Shared Features
◦ Performance
Improvements
◦
◦
◦
◦
◦

What’s being delivered:

Main benefit:
◦ Extensions to SQL Server SysPrep functionality in order to
support image based deployment of clustered SQL Server
instances
◦ Supports full automation of SQL Server Failover Cluster
deployment scenarios
◦ Reduces deployment times for SQL Server Failover Clusters
◦ Combined together, these features allow customers to
automate the provisioning of SQL Server Failover Clusters
both on-premises and through Infrastructure as a Service
(IaaS)
◦ Built on top of SQL Server 2012 SP1 CU2 Sysprep
enhancements

What’s being delivered:

Main benefits:
◦ Increase number of secondaries to 8 (from 4), 2
SyncReplicas
◦ Increased availability of Readable Secondaries
◦ Support for Windows Server 2012 Cluster Shared
Volumes (CSV)
◦ Enhanced Diagnostics
◦ 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

What’s being delivered:

Main benefits:
◦ Single partition online index rebuild(!!)
◦ Managing Lock Priority for table SWITCH and
Online Index Rebuild
◦ Built-in diagnostics
◦ Increased concurrency and application availability
◦ New partition-level granularity for online index
rebuild allows lower resource (CPU/memory)
usage for customers who need 24/7 access
◦ Provide customers greater control over impacting
running transactions if using switch partition or
online index rebuild commands
Geo Replication
Restore to
VM
*CAPEX –(Capital Expendures -investments for IT Solutions)
**OPEX -(Operational Expendures - cost for operate the IT solutions

Backup Encryption

SQL Server Data & Log Files in
Windows Azure Storage (silly?)
◦
◦
◦
◦
AES 128/192/256, Triple DES
DO NOT LOSE YOUR CERTIFICATE!
No appending to backup
This IS Standard Edition Feature

TempDB write ‘deferal’
◦ See Bob Dorr blog post:
http://blogs.msdn.com/b/psssql/archive/2014/04/09/sql-server-2014tempdb-hidden-performance-gem.aspx
◦ Affects tempdb BULK OPERATIONS (Select into,
table valued parameters (TVP), create index with
SORT IN TEMPDB, …)
◦ This can be a VERY important performance
increase!!
◦ All editions
45
 Self-Serve
BI
◦ PowerQuery, PowerPivot, PowerView,
PowerMap, PowerPotty
 Tabular/BISM
in SSAS
 HDInsight
 PDW
V2, PolyBase

Kevin G. Boles
◦ @TheSQLGuru
◦ [email protected]


Don’t forget about the #sqlhelp hash tag
Don’t forget about free downloads on
SQLSaturday.com Schedule Pages