top 5 t-sql improvements in sql server 2014
TRANSCRIPT
Boris Hristov
SQL Server MVP, Speaker, Trainer, Blogger and DBA
Top 5 T-SQL Improvements
in SQL Server 2014
Sponsors
That’s not a Hekaton Talk!
So who am I?
@BorisHristov
So who am I?
things that can make your life better
Here’s how this will go…
time
Delayed Durability
A Atomic
Isolated
Consistent
Durable
C
I
D
Data pages are located in,
or read into, the buffer cache
and then modified
2
Modification is recorded
in transaction log on disk3
Later, checkpoint writes
dirty pages to database4
Buffer Cache
Transaction’s lifecycle
Here’s the reason why it’s slow
Data pages are located in,
or read into, the buffer cache
and then modified
2
Modification is recorded
in transaction log on disk3
Later, checkpoint writes
dirty pages to database4
Buffer Cache
Transaction’s lifecycle
Yes,
you can lose data!
DEMODelayed Durability
SELECT INTO
Prior SQL Server 2014:
Serial Execution Plans
SQL Server 2014:
Parallel Execution Plans
Why don’t we speed up a bit?
Talk with the DBAs
to design a proper
disk and data layout!
You want that speed, right?
DEMOSELECT INTO
Cardinality Estimator
Why is the CE so important?Why is the CE so important?
The CE
has not been
changed since
SQL Server 7.0
…and at the same time
Queries with Multiple
Predicates
Queries Joining Multiple
Tables
New Data Not Presented
in the Stats
Almost everywhere…
(this, oh btw, means you have
to test a lot!)
Where to expect changes?
DEMOCardinality Estimator
Inline Index Definitions(remember this one for later)
SQL Server 2012
SQL Server 2014
Remember this improvement
DEMOInline Index Definitions
Partitioning Improvements
Numbers
0
…
8000
Partition 2
2501
…
4000
Partition 3
4001
…
8000
Why is partitioning both cool and not?
Partition 1
0
…
2500
DEMOPartitioning Improvements
Temporary Objects Caching
In summary
Delayed Durability
Parallel SELECT INTO
Cardinality Estimator
Inline Index Creation
Partitioning Improvements
Testing is
important!
Resources you can use
Click me!
Please give feedback to us
http://speakerscore.com/sqlsaturday376
Thank you!
Thank you!
@BorisHristov
www.borishristov.com