We all like to play with the latest technology. Often as developers we find a lot of upgrades being sold to us with a heap of features we just won’t use. Sometimes it’s nice to be able to have a plain English list of the more useful items in the upgrade. I’ve formulated a small list below to highlight some of these features. Not intended to be a “How to”, but more of a “Heads Up” on the most useful features of SQL Server.
- From 2012 upwards there is a hidden option to compress the database when creating backups.
- Saves a shed load of disk space
- Faster to transfer the backups locally or to another server on standby
- Backups don’t generally take any longer
- New Design for Cardinality Estimation (never heard of it)
- The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator.
- In our experience this makes queries 10-15% faster.
- Delayed Durability
- Sounds rubbish doesn’t it? Well it’s not. The best example I have on this is statitics logging on a website. Most of the time while the data is useful it’s not essential. You don’t want to slow down the users load of the page just to wait while indexes are updated in your stats table. By using Delayed Durability you can return back to the user instantly while in the background SQL will spend the time committing the operation. It does this at the expense of reliability, but TBH you’ll only really loose anything if you restart the server, which you’d hope not to be doing that often.
- High Availability Groups
- This is a brilliant addition to SQL 2012 and improved for SQL 2014
- With Microsoft stating that Mirroring may not be an option in the next version, it’s well worth taking a look
- One of the most exciting new feature in SQL Server 2014 is In-Memory OLTP, which allows you to move individual tables to special in-memory structures. The performance boost can be as huge as 30x. There are a number of limitations and special requirements for these tables, so they won’t work under every circumstance. But when they do, your OLTP performance will go through the roof. This is better than other in-memory solutions that require the entire database to be placed in memory. You can get more performance by converting existing stored procedures into in-memory procedures, too. You’ll need to test to make sure your tables are compatible, but if they are, you’ll love this feature.