Blog - comments

Bravo, il fallait y penser Mery Christmas

Eric

Great !! Thank you very much.

SEK

Great !! Thank you very much.

SEK
Franck, thank you for the thorough explanation on diagnosing these types issues. It showed me exac...
TimL
Hello Sahid,I think that for ILM you need the partitionong option and the Advanced Compression optio...
pierre
Blog Stéphane Savorgnano Microsoft TechEd Madrid: Optimizing T-SQL & SQL Server 2014 Futures and Features

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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.

b2ap3_thumbnail_blog1.jpg

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
      b2ap3_thumbnail_blog3.jpg

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.


b2ap3_thumbnail_blog1_20130626-125414_1.jpg

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:

b2ap3_thumbnail_blog5.jpg

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:

b2ap3_thumbnail_blog4.jpg

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.

Enjoy!

Rate this blog entry:
1

Stéphane Savorgnano is Consultant at dbi services. He has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. Stéphane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. Stéphane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking / Financial Services, Chemicals & Pharmaceuticals, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Friday, 19 December 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter