Transcript 14_Features_in_SQL_2014_Talk_-_Copyx

14 FEATURES
IN
SQL SERVER 2014
YOU HAVEN’T CONSIDERED
Tony Milne
ABOUT TONY MILNE;
GO
Twitter: @chmilne
Living in Columbus, Ohio for 14 years
Father of 2
Big Fan of The Ohio State Buckeyes
Treasurer and Board Member of
Dog Food Conference
www.DogFoodCon.com
Director, Applications Development at
SSIS
Replication
SSRS
SO WHAT HAS CHANGED?
QUESTION:
What has changed?
A BUNCH OF
COOL STUFF IN THE ENGINE
…AND 1 THING IN BI
DELAYED DURABILITY
(AKA LAZY COMMIT)
 ACID
 Database level
 Durability -> Axed
 How do you feel about data loss?
 Commits are asynchronous
 Durable when the log is flushed to disk
 Transaction Log Bottleneck?
 Turn this on
 Check for WRITELOG Waits
 Transaction Level
CLUSTERED COLUMNSTORE INDEXES
 Improve Data Compression and Query Performance
 Bulk Loads
 Read-only queries
 All of the data is compressed
 Updatable
 Great for Fact Tables
 No need to go to the table; all of the data is in the index
 Limitations
 Either the clustered index or other indexes, not both
 No unique, PK, or FKs on table
WINDOWS AZURE DEPLOYMENT WIZARD
 Host instance of SQL Server in a Windows Azure Virtual Machine
 Full database backup operation
 Copies the complete schema and data
 Takes care of Azure VM configuration; no manual pre-config needed
 Keep in mind
 Cloud Services, VM location, and Data Disk Storage Service MUST be in same geo
CARDINALITY FEATURE
 After 15 years of same old CE, it was time for an update
 What makes a better query plan?
 Lower latency
 Fewer read pages
 Check out for this blog post for some of the gritty details
 http://blogs.technet.com/b/dataplatforminsider/archive/2014/03/17/the-new-andimproved-cardinality-estimator-in-sql-server-2014.aspx
 Let’s check out the difference…
BUFFER POOL EXTENSION
 Goal: Improve I/O efficiency
 Seamless integration of buffer pool with SSD
 Great for those of us with less memory than data
 Also great for virtual environments where memory is a premium
RESOURCE GOVERNOR
 Limit Incoming Application Request
 CPU
 Memory
 Physical I/O
 Physical I/O
 I/O level isolation between multiple
workloads
 I/O level predictability
 Two parameters
 MIN_IOPS_PER_VOLUME
 MAX_IOPS_PER_VOLUME
 DMV changes for monitoring
 Add columns to
 Sys.dm_resource_governor_resource_pools
 Add columns to
 Sys.dm_resource_governor_configuration
 NEW
 Sys.dm_resource_governor_resource_pool_volumes
 New XEvents
 6 New performance counters
BACKUP AND RESTORE
 SQL Server Backup to URL
 Can now use SSMS not just T/SQL, PowerShell, or SMO
 SQL Server Managed Backup to Windows Azure




Built on SQL Server Backup to URL
Service manage and schedule database and log backups
Database or instance level
Available for on-prem or Windows Azure VMs
 Backup Encryption
 AES 128, AES 192, AES 256, and Triple DES
INCREMENTAL STATISTICS
 Previously, only sample method or full
scan method
 Now
 Update statistics per partition
incrementally
 No need to scan the entire, just update
the affected partition
T SQL ENHANCEMENTS
 Create CLUSTERED and NONCLUSTERED indexes on disk-based tables inline
T SQL ENHANCEMENTS
 SELECT….INTO
ALWAYSON
 Multiple secondary replicas
 Bumped up from 4 to 8
 Active secondary replicas
 Take the load of the primary, use it for backups
 Read workloads continue even if the primary fails
 Integrate with Windows Azure IaaS
 On-prem to Azure
 Reporting abuse in the cloud
 Additional diagnostics
 Columns added to DMVs
 New functions available
SQL SERVER DATA FILES IN AZURE
 Keep that engine the garage; put the storage in the cloud
 Using Windows Azure Blob storage, of course
 Why?




Migrations
Storage
High Availability
Security
https://msdn.microsoft.com/en-us/library/dn385720.aspx
MEMORY-OPTIMIZED TABLES
 In-Memory OLTP
 Optimized for OLTP, Memory Data Access
 Fully Transactional and Durable
 No, this is not DBCC PINTABLE
 That was depreciated
 These Tables Don’t use the Buffer Pool or B-tree Structure for Indexes
 Row Storage is Completely Different
 Uses Timestamps for Row Versions
 Prepare for 2x the Data for Memory
 Let’s keep it to 256GB (aka a dull roar)
 5 – 20 times Performance Improvements
INSTALLATION
 No more support for Windows Vista
 Bummer!
 SysPrep
 Previously limited to certain features
 Now, ALL features
 Failover cluster installation
 Repairing is still not supported
 Standard Edition max memory = 128 GB
ANALYSIS SERVICES AND BI
 Native support
 Power View Reports against Multidimensional Models
 DAX queries
 Released in SQL Server 2012 SP1
 Now available in SQL Server 2014
 Take advantage of OLAP cubes




Tables
Matrices
Bubble charts
Geo maps
https://msdn.microsoft.com/en-us/library/bb522628.aspx
Multidimensional to Tabular Object Mapping
Multidimensional Object
Tabular Object
Cube
Model
Cube Dimension
Table
Dimension Attributes (Key(s), Name)
Column
Measure Group
Table
Measure
Measure
Measure without Measure Group
In a table named Measures
Measure Group Cube Dimension Relationship
Relationship
Perspective
Perspective
KPI
KPI
User/Parent-Child Hierarchies
Hierarchy
Display Folder
Display Folder
https://msdn.microsoft.com/en-us/library/jj969574.aspx
THANK YOU!
REFERENCES
 https://msdn.microsoft.com/en-us/library/bb500435.aspx
 http://channel9.msdn.com/series/sqlserver-2014-mission-crit-performace
 STUFF(‘copiousfreetime’, 1, 15, ‘hoursofcontent’)
 http://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eatsbad-tsql-breakfast/
 http://sqlmag.com/database-high-availability/changes-sql-server-2014-alwaysonavailability-groups
 http://blogs.technet.com/b/dataplatforminsider/archive/2014/03/17/the-new-andimproved-cardinality-estimator-in-sql-server-2014.aspx