SQL Centre of Excellence

On the back of an awesome Sql Saturday here in Dublin I have been inspired/motivated set myself a goal of blogging much more than I have been doing lately (So just one blog post hits that goal as I havnt been blogging!)

What better topic to discuss but the recent release of SQL Server 2014 CTP1

For anyone who has not been following, SQL Server is adding In Memory OLTP, aka Hekaton to the SQL Server arsenal. At a high level, the idea behind Hekaton is to deliver databases and data structures optimized to operate completely in memory, as opposed to all previous versions of SQL Server which were designed from a Disk Storage perspective. A much better description & in depth view of the workings of Hekaton are available in Kalen Delaneyspaper on the subject

Installation
Installation was pretty standard SQL Server GUI installer, but with one caveat, you cannot install CTP 1 on a sever with any previous version of SQL Server. In my haste to get it installed I hit this issue. A quick search turned up Aaron Bertrand’s blog explaining that this is as per design, the recommendation is to install on a new VM.

Creating In Memory Databases & Tables
The syntax for creating an in memory capable database is as follows:

CREATE DATABASE [MemOptDB]
ON  PRIMARY
( NAME = N'MemOptDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MemOptDB.mdf' , SIZE = 1048576KB),
FILEGROUP [MemOptFG] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'MemOptDB_File', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MemOptDB_File.ndf' , MAXSIZE = 1048576KB)
LOG ON
( NAME = N'MemOptDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MemOptDB_log.ldf' , SIZE = 1341696KB )
GO

Pretty standard except for the phrase CONTAINS MEMORY_OPTIMIZED_DATA

Once a Memory optimized database is created, a memory optimized table can be added:

CREATE TABLE InMemoryTable
(
Col1 bigint  not null ,
Col2 char(4000) null ,
Col3 char(4000) null 
PRIMARY KEY NONCLUSTERED HASH(Col1) with (BUCKET_COUNT = 200000),
) with (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

A few things to note here:

  • The table must be flagged as Memory_Optimized.
  • The durability can be SCHEMA_ONLY, in which case any data is lost after a server restart, or SCHEMA_AND_DATA, persisting both schema and data.
  • In memory tables must have a primary key.
  • Clustered indexes are not supported, so the primary key needs to be defined as a nonclustered index.
  • The index is not a standard B-Tree, but a new memory optimized hash index.


If you are trying to populate the table with some unimportant test data, you cannot use the standard trick of INSERT INTO TABLE DEFAULT VALUES, as in memory tables do not support default constraints. It is worth noting that IDENTIY columns are also currently not supported.

To get a quick view of the potential performance increase we decided to try a simple table scan of a standard table and an in memory table.

 

50,000 rows were inserted into each table and each table was queried twice, once with a cold buffer, and once with a warm buffer

 

Resultsimage

Note that SET STATISTICS IO ON returns nothing for the query on the in memory table, perhaps indicating the underlying structure is very different and cannot be read with SET STATISTICS IO.

 

With a cold buffer the in memory table scan was 6x quicker than a scan of  a standard table.
With a warm buffer the improvement dropped to 3x.

I expect that when I start playing with more complex queries, heavy transaction loads particularly when locking and blocking come into play, there will be a much wider gap in performance, but that's for another day.

 


Conclusion
That's a super quick skim over the new in memory tables in SQL 2014. We can see that there is significant performance improvement to be had against querying standard disk based tables.

We haven't even scratched the surface here, but will hopefully there is enough information, and links to some great sources to get you tinkering.


Thanks for reading!

The Dublin events lightning talks were much anticipated after last year, and I am delighted to say did not disappoint. They were an eclectic collection of topics, much enjoyed by the audience.

They were started off by Niko Neugebauer who had a photoroll of famous speakers who started off life with very different paths, qualifications or lack thereof, making the point that it does not matter what has happened in your past, because today and tomorrow are yours to shape. Niko then demonstrated the speed and performance of SQL Professionals with his speedy dash to the airport, while his applause was still ringing.

Mark Broadbent then did a lightening talk on Always On, and Clustering. His talk somewhat resembled a magicians show, except that the box was invisible, his assistant (Me) did not get chopped in half, and he has highlighted the benefits of cloning red heads.

We then had Sladjan Kutijevac who showed an SSIS package he had written for importing complex excel spreadsheets for the local basketball teams he trains.His session also very effectively demonstrated the overcoming the difficulties of Extracting data from your computer, Transforming it into something visual that a projector can handle, and then Loading your audience with that information. Sladjan deserves a special mention for taking to the floor surrounded by people who speak for a living – one of the hardest things to do, and much appreciated by our committee, as it embodies the spirit of the event for us.

Following on from Sladjan, Kevin Kline presented on….Hmmm….Chocolate. The specifics of this talk may have been slightly lost on me, as my eyes tracked that bar of chocolate around the session, as Kevin slowly unwrapped it, eyed it up, and waved it around, before slowly starting to eat that bar, square by square. He may have been talking about tasting sugar for the first time, but WHO CARES about sugar, when you can race out and buy a bar of that AWESOME chocolate at the end of the session, and swallow it without chewing, because it is now all you can think of, focus on, and nearly taste by the end of his 5 minutes (It felt like an hour of torture and longing for that chocolate, to me).

Bob Duffy then demonstrated how the cream of the SQL community in Ireland secretly uses Microsoft Access. He built and deployed an entire web solution to manage our own SQL Saturday using Access and Sharepoint. The fact that he could do this in 5 minutes may worry a lot of consulting firms. Very few of us charge by the minute these days…

Matt Masson, our overall winner based on feedback, did the next session, and I have to say the word genius does not begin to describe this. I had verbal feedback from the audience after this session about how they are going away to look at Data Explorer – and others who are inspired to use this technology outside the workplace, as well as in it. The power of technology often becomes apparent in small ways, and Matts application, written for his gorgeous 4 year old son, on Skylanders spoke to the audience in a way its rare to see. As Niko said, tomorrow we can be whoever we want to be. Tomorrow, we all want our children to think we talk about Cats and play with Skylanders at work, while our colleagues realise, through this, that what we work with can impact everyone for the better.

Neil Hambly presented a session on how to break rules with SQL Server. Personally, I already thought Neil had broken all the rules by being such an awesome dancer, while being a SQL Guru, however, Neil showed us some rules that were definitely not made to be broken, and that his performance on the SQL floor is on a par with his performance on a dance floor, even if his databases may not be up to much as a result!

Our final speaker was Chris Testa-O’Neill who did a 5 minute Q&A on Certification. His first question was courtesy of Bob who asked if we’re going to get  BI MCM, and what is up with the new MCSE. This got an interesting reaction from Chris, who seemed to feel like there was a plant in the audience(!?!). Some very useful discussion took place afterwards, and this probably helped a lot of people to better understand the new certification process. I was sort of disappointed that Chris didn’t burst into song at the end, as he is a very entertaining performer.

The audience were polled at the end of the session, and the results were as below:

Matt Masson, a resounding First Place

Niko Neugebauer, Second Place

Kevin Kline and Bob Duffy Joint Third Place

A huge thanks to all the speakers for this amazing session, on behalf of myself and my fellow SQL Saturday Dublin committee members – Bob, Inga, Marco, Neill and Sandra. A lot of work goes into the preparation, it is a huge success with the audience, and for many, makes the event.

Finally, a massive shout for all our sponsors – Fusion-IO, Microsoft, PASS, Redgate, EMC, Confio, SQL Sentry, Inspired, SpanishPoint, Ergo, Codec DSS, and Prodata, without whom we could not run these events.

Page List

Page List