SQL Server - SQL2014 MVP Roadshow
Part of Addpro's DBA team was at the SQL2014 MVP Roadshow in Malmö where Johan Ålen from SQLUG ran a demo on InMemory tables. My colleague Pontus has previously blogged about the news in 2014 which you can read here.
But first some information about new products and services. A lot of talk about the cloud of course and Microsoft's CloudOS product. Data volumes are doubling every year, i.e. this year we will store twice as much compared to what we previously stored in total. However unclear how much of this is Youtube clips. Another interesting statistic is that 87% of all performance problems are due to increased data growth, something we will return to in future blog posts shortly. Biggest news in SQL2014 is in SSDS (the database engine), exception to this is the news that SSRS now works with Chrome. In the Standard edition, the maximum memory that can be used has been increased from 64 to 128 GB.
InMemory
The InMemory technology started to be developed in PowerPivot which came in 2008 R2 and has since evolved to Columnstore index in 2012. Columnstore index is now called InMemoryDW and the big improvement is that it is possible to write to the index i.e. no longer needs to be read only. In 2014 InMemory tables are also added, a way to increase OLTP performance. In SQL 2000 we could make DBCC PINTABLE to "lock" a table in memory and only now has the technology evolved as well as the prices of memory modules dropped so that they meet in a line chart among some number converters and we have access to this technology. However, with a lot of counter demands from developers and DBAs. To get maximum performance we should use stored procedures that are compiled as "NATIVE", this means among others SCHEMABINDING i.e. we cannot change underlying tables/objects, we also have to choose which language and thus how formatting and other things work. We also cannot copy data from another database into a memory optimized table. Some of these limitations may possibly be released in future service packs.
Security
We can now encrypt backups directly in SQL and we can choose between AES 128, AES 192, AES 256, and Triple DES. Kudos to anyone who can explain which one we should choose to avoid the prying eyes of foreign intelligence while minimizing performance cost. Some improvements in AlwaysOn, e.g. 8 instead of 4 secondary replicas.
Performance
In Resource Govenour (please read colleague Björn's blog series about it - click here), in addition to CPU and memory, we can now also check disk I/O per disk volume. With the Buffer Pool Extension you can use an SSD disk as nvRAM to improve disk I/O. Statistics can be created/updated per partition. Something else that can give better performance is to use inmemory table variable instead of tempdb (which is written to disk). We also have the option to set DELAYED DURABILITY which means delayed write to disk, better performance but worse security.
Other improvements
In SQL2014 you can specify a URL to data files, e.g. to Azure. It is also possible to make a backup to a URL (this feature was updated in 2012 but not in the GUI)
/Fredrik
