What I learned in Dublin
Last week, I attended SQL Saturday No. 501 in Dublin as well as a full day precon session there. This post is a mockup of all the things I learned during these awesome days.
I chose to attend the full day precon session on SSIS taught by Matt Masson. This was an amazing training day. Matt has worked as a developer on SSIS for a long time and really, really knows the good, bad and ugly parts of the product. Even though I have worked quite a lot with SSIS and read many of the SQLCAT team white papers on it, I still picked up quite a few gold nuggets during the day. Here are some of them:
Use simple data flows (Source->Row count) to measure the how fast you can read from source system tables.
Use the SSISDB catalog for ETL execution metadata (and use custom logging in SQL Server 2016 to easily capture row counts for source and destinations)
The codeplex project SSIS reporting pack can be used as a great starting point of reporting on the ETL execution part of the SSISDB catalog
Understand how SSIS deals with LOB type data (NVARCHAR(MAX) and her friends)
Datatype conversion at the source might double read performance
The SQL Destination is deprecated and we should (almost always) use OleDb destinations with the fast load option instead
Use the Oracle components from Attunity, if you need to read/write from/to Oracle. They are bundled with SQL Server Enterprise Edition
SSIS lookup cache files are written in the RAW data format. They can therefore be read using a RAW source adapter
When hashing row values to check for changed rows, consider using a field delimiter string like “|” when concatenating values before inserting into the hash function to avoid collisions. Also substitute NULL values with a string value, eg. “NULL”
On the conference on Saturday, I first attended Brian Carrigs ( @briancarrig ) excellent session Managing Very Large Databases with SQL Server. A few things worth repeating from his session are
Online Piecemeal Restore – make PRIMARY file group small/with no data
SQL 2016 supports TRUNCATE TABLE WITH (PARTITIONS (…)
Later, I attended a good introductory session on things cloud related Cloud Adapting your career for the era of the cloud by Gavin Payne. I knew most of the cloudy stuff, but learned that of agile methods, Scrum is mostly used in software development whereas Kanban is more used within operations.
Mark Souza did a great and inspiring keynote with starting with the history of SQL Server and his involvement in the product, and afterwards going into details about various internals in SQL Server 2016. He recommended a new internals blog SQL Server According to Bob (and Bob), which I definitely will need to spend some time studying.
After the keynote, I let Chris Adkin fry my brain in his hardcore level 500+ session Super Scaling Clustered Index Inserts. The session was a tour de force in internals for the SQL server database engine, with a particular focus on CPU architecture and spinlock wait statistics. Through various examples, Chris showed us “how to go from 13,000 to 550,000 inserts per second on the same hardware.” Awesome. I learned that even though I have read the SQL Server internals book by Kalen Delaney et. al., I’m still a complete newbie when it comes to hardcore stuff like this. But that is the whole point of going to conferences: learn about what you don’t know… J
During lunch, I attended the sponsor session from Pyramid Analytics. Nick Barth gave us a quick 45-minute tour of the product in his session BI Office from Pyramid Analytics Delivering Better Decision for Everyone. During my time working with Effektor as a product owner/partner owner/trainer and presales consultant, I have delivered many, many product demos, and I know how hard it is to make that look seamless and casual (especially when you hit a bug, and need to get around it without exposing this to the audience). I must say that I was impressed with Nicks presentation and also with the product suite. It looks like Pyramid have succeeded in creating the enterprise edition of self-service BI, including the important aspect of governance (who can read, change and administer this report/dashboard I just created).
After this session, I took time off to go through my presentation a last time, and made sure that all my demos were working. And then it was my turn to present. I did a session called WTF with SQL, which is a collection of examples of where the SQL standard clashes with common sense, especially around semantics on NULL. During the session, I asked the participants to count their inner WTF moments, and write them down of the evaluation forms. After the session, I learned of a new example of WTF with semantics of LIKE vs. equality (=), and I will extend the presentation with this example, before next time I present it. The session got good evaluations (many lovers and a few haters) and all was good (scores were on a 1-4 scale, where 4 was best):
From the evaluations, I also learned that I can improve on my abstracts and that I should work on my presentation style.
That was it. There was of course all the great social stuff like speaker dinner, helping out with registrations, chatting with sponsors, the raffle and the post-event BBQ. And I also got to do a little sightseeing in Dublin. But that’s not what this blog is for, we’ll save that for Facebook and Twitter.
2016-07-01 update: some of the other sessions were recorded and are now available on Channel 9:
Get the SSIS reporting pack here:
Read Bob and Bobs blog here:
Visit Chris Adkins blog here:
Get handouts for SQL Saturday sessions (including my own) here: