Infrastructure at your Service

Stéphane Savorgnano

Microsoft TechEd Madrid: Optimizing T-SQL & SQL Server 2014 Futures and Features

Today, I am beginning my second TechDay by a session about Optimizing T-SQL. You need to know about this if you are using SQL Database or Microsoft SQL Server 2012. Tobias Ternström, Lead Program Manager, was the speaker of this session. His team, five hundred persons, works on SQL Server engine and mostly focuses on performance.

There are 3 three different ways to run SQL:

  • Physical machine
    • can be scale-up
    • full control
    • roll your own HA/DR scale
  • Virtualized machine
    • 100% of API Virtualized
    • roll your own HA/DR scale
  • Virtualized Database
    • auto HA fault tolerance
    • friction free-scale
    • self-provisioning management and scale

The problem is to find the good balance between dedicated/shared resource and high/lower Friction or Control.
Taking one of these ways depends on your applications and needs. The older an application is, the more difficult it is too optimize it so we will scale up the machine to boost performance.

It’s good when you have just one major application working on a machine but if you have lot of application on this machine it will be difficult to buy scale-up all or it will cost a lot. In this case the best scenario should be to go for VM.

Lots of companies have said that you do not have the right to buy new hardware… they want to push you to the virtualized world.
Also if you need lots of databases with a short life cycle you can also virtualize the databases, it will be cheaper and will provide an easy way to create and delete each database.
We have the possibility to virtualize, which is really cost saving.


Best practices:

  •  instrument your application: easier to know if you have a problem and from where it is coming
    • use DMV to retrieve information periodically: You can automate a dmv execution each 5 minutes to monitor a problem
    • Capture trace to XML file for monitoring trends you need and want check
  • automate, automate, and automate: the more you do it the more you can spend your time intelligently instead of searching for problems…
  • minimize roundtrip: the less roundtrip you have the less problem you have
    • do one select instead of multiple selects
    • batch updates should also be done in one time when it’s possible instead of sending multiple…b2ap3_thumbnail_blog2.jpg
  • take care to connection reliability: you have to manage that in your code
    • multiple commit
    • safe reply

This is a resume of this session, I hope it will help.
Another very interesting session, done by Jos de Bruijn, was the SQL Server 2014 Futures and Features.
This session was about all new features of SQL Server 2014. There was a spectacular demo using columnstore indexes and buffer pool extension.


Mission critical performance

  • In-memory Built-In: quickly obtain 10x speed performance – even 60x has been seen
  • Secure & scalable: most secure with enterprise scale with using windows server
  • High Availability: AlwaysOn and Availability Group
  • Mission Critical Support: Live support designed to mission critical solutions

Here is a small picture:


As you can read above, columnstore indexes have been enhanced to be even more powerful.
Columnstore index put tables in readonly state before SQL Server 2014, now it will not be the case anymore.
SQL Server 2014 will also be able to take advantage of the new Windows Server 2012 R2 feature: the buffer pool extension to SSD drive.
We saw an impressive demo based on a table with twelve millions rows which is queried to create a report. This report creation takes two minutes with a normal table.

First, Jos added a nonclustered columnstore index based on all rows.
The report with this nonclustered columnstore index takes 2 seconds to finish and the place used by the table is 300MB. With SQL Server 2014 we are now able to create a clustered columns store index. With a clustered columnstore index, the response is immediate and when you check the place taken by the table on the disk you see that initially the table took 400MB and now just 220MB.

So it is not only very fast: it is also less place-consuming due to the fact that data is stored by rows instead of pages and also because of better performance compression mechanism.

Afterwards, Jos showed us an interesting picture:


Yes! You don’t dream: SQL Server is the most secure database since a long time. It is also for that reason that a number of clients have moved from Oracle to SQL Server!

For scalability, a good point is that Resource Governor adds IO governance.
Now, for High Availability, we can have up to 8 secondary replicas, back-ups on secondary and you have a replica wizard to quickly create replicas.

The Geoflow feature is also amazing when you see a worldwide travel drawn in a 3-dimensional earth. You can also create a small animation with a cursor to show moves per period of time…

The platform for Hybrid Cloud has also been enhanced with

  • Simplified Cloud backup
    • manual or automatic
    • at an instance level with point in time restore
  • Cloud disaster recovery
    • Fast disaster recovery
    • easy to deploy manage
  • Extend On-premise Apps

Here is the new features which will come with SQL Server 2014. You are already able to download the CTP1 version here.



Leave a Reply

+ one = 5

Stéphane Savorgnano
Stéphane Savorgnano