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!