Boris Hristov

Founder of 356labs

Founder of 356labs
and PowerPoint MVP.
Speaker, Trainer & Author.
A guy that loves to do sports, have fun and enjoy life. Gallery / Blog / Contact

Copyright © 2021 ·

T-SQL Tuesday #055: The Good Ones Almost None Of Us Talk About

June 10, 2014 By Boris Hristov Leave a Comment

TSQL2sDay

T-SQL Tuesday is here again and what a fantastic topic to discuss – what we like and what we do not like in SQL Server 2014! It’s interesting for me to talk about this because I just recently delivered a presentation on the biggest Microsoft conference in my country, so here is my opinion on five improvements that mostly affect the developers (as the conference’s audience were only developers) that I consider enormous advantages in SQL Server 2014.

1. Delayed Durability – yes, I know. There is even a bigger chance now to lose data if we enable that option for our database, but hey, the advantages are also a lot. By being able to “optimise” how the log flushing process occurs, we can win quite some performance, especially if we see our transaction log as our biggest bottleneck(you know that WRITELOG wait type, huh?). My tests showed 10x improvements in DML operations and that is something I would definitely consider if I know and if the business confirms that losing some data will not affect the business operations. + let’s don’t forget that we can control that option for each and every transaction, so it is flexible!

2. SELECT INTO gets parallel – nothing to say here except that I pretty much like that because it will speed up the huge data movements.

3. The New Cardinality Estimator – that’s huge, but not only this – it’s important! I pointed it out at the conference during my presentation and I will do it here again – if you are planning migration to SQL Server 2014, please plan more days for testing your queries and workload in general. The new cardinality estimator is not guaranteed to help you(even though the chances are that it will) and you do not want to see plan regressions for your mission critical app! For more information on what changed, take a look at the series of blog posts from one of my friends from bwin – Miloš Radivojević (t|b) here.

4. Inline Index Creation – cool improvement that I really think whoever is the database designer (meaning the developers or the DBAs) will enjoy. Not to mention that it brings some “hidden” improvements with itself. Unfortunately, you will not be able to inline every single definition you may think of. For example, if you try to inline the index definition of an index that uses included column(s) or is filtered, this operation will still fail and you will need to define that index separately. However, the options for fill factor and on creating the index on a specific filegroup work 🙂

5. Partitioning Improvements – rebuilding a single partition online? Yes, it is possible now(previously either the whole table online or a single partition offline). I mentioned this improvement (and did not even touched on the other improvements – the managed lock priorities, the improvements in the statistics –  incremental stats on partition level + the option to update the stats on single partitions) because I think it is high time for more companies to start thinking of that direction. Table partitioning is now a mature feature and Microsoft are obviously very aggressively improving it, so we should all start consider it more and more with the release of SQL Server 2014.

Yes, I haven’t included Clustered columnstore indexes and Hekaton, but I don’t believe it’s only those improvements that we have to spend our attention at. I also discussed only improvements, so forgive me if you expected to hear what I wanted to be removed from the product (who screamed Auto Close and Auto Shrink out there?!?!). So, thanks for the invitation and for hosting #55, Joseph! Looking forward to seeing the summary post!

Share this:

  • Email
  • Facebook
  • Twitter
  • LinkedIn

Filed Under: SQL Server Tagged With: SQL Server 2014, T-SQL Tuesday

What do you think? Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.