What I learned in Cambridge
Last week, I attended SQL Saturday No. 520 in Cambridge, UK as well as a full day precon session. This post is a mockup of all the things, I learned during these awesome days.
For this conference, I chose to attend the full day precon session “Analyze and tune SQL Server like a BOSS” taught by Uwe Ricken
(b|l|t). I really enjoyed the training. Uwe is a SQL Server MCM (Microsoft Certified Master), the now retired certification for people, who really wanted to dive deep into SQL Server, and he sure knew his stuff. I personally used the study material for MCM when I started learning about SQL Server, and I therefore knew (or used to know) a lot of the material in the session. But it was still very rewarding to get a full refresh on SQL server seen from CPU, memory, networking, storage, security, execution plans and more. I also picked up a few gold nuggets during the day, of which some are:
An explanation of the task queue, explained further in this blog post http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/ by Remus Rusanu
Query patterns by MS Access and SSMS RBAR (Row By Agonizing Row)
The 2371 trace flag can be used to control autostat (AUTO_UPDATE_STATISTICS) behavior for large tables
Use 64k block size for disk partitions with data files. Windows will by default create partitions with 4k block size
Exclusive lock escalation always goes directly to the table after 5000 locks on it
Be aware of differences in Tempdb collation vs. user database collations
On the conference day on Saturday, I gave a presentation called “Partitioning as a design pattern” in the first session morning slot. My first presentation at SQL Saturday in Cambridge in 2013 was also the my first international speaker experience, and I remember that I took a taxi from the airport directly to a user group meeting on the night before the conference. Here, I presented parts of the very same presentation. Well, people still liked the topic in 2016, and I got some nice feedback on the session.
In case you want to watch it online, there exists a recording of it from Microsoft Campus Days 2013 here on Channel9:
I have also uploaded handouts on the conference website here:
The keynote was delivered by Steve Jones (b|l|t), who did a good job taking us on a historical ride from floppy disks to 3D SSD drives, focusing on evolution of data and the importance of people with data(base) skills and interests.
After the keynote, I attended Steve Jones’ session on “End to End Encryption and Security in SQL Server 2016”. Key takeaways for me were
In the SQL2016 feature AlwaysEncrypted, the responsibility for security is now with the client. And there are limitations on collations, datatypes, select queries and default constraints
Row level security leaks data through statistics, queries and CDC
Setting for Dynamic Data Masking is on the database, not on table level
Slides and demo files from the session can be downloaded from the conference website here:
Then, I spent time with Mark Wilcock (b|l|t) in a session called “Visualising Financial Risk - some project examples.” In the session he showed different ways of displaying risk and financial data with Excel and R and showed ways to visualize this using PowerBI. I liked the presentation for the technical parts, but also for showing and explaining terms from risk management and banking. My first IT job was in the Treasury department in Unibank, where I was working with calculations of ValueAtRisk (VaR) for changes in interest rates. So it was also a nice trip back to 1998 with PL/SQL on Oracle7 and VAX…
During lunch, Justin Langford (l|t) from Coeo gave an inspiring talk on hybrid cloud. Before SQL2016, hybrid cloud was probably mostly a thing for enterprise customers, and Coeo have most of their customers in this category.
After lunch, I attended a great double session on Azure Data Lake by three german boys (that’s how they presented themselves) Sasha Engels (b|l|t), Sasha Tillman (b|l|t) and Sasha Dittmann (b|l|t) - the names have been changed to protect the innocent. The first session was on platform and architecture, and the second one more focused on the U-SQL language. It was interesting to the approach of mixing SQL and C# into one powerful scripting language. I wonder how organizations using this platform will be able to govern a portfolio of U-SQL scripts without getting into copy-and-paste hell with spaghetti-code on top. It reminded me of stories from my past as a data warehouse consultant, where customers had been using SAS Institute software to create “data warehouses” over many years. Maintaining SAS scripts in such an environment and/or migrating to a RDBMS-based data warehouse was very difficult. Let’s see how it goes with solutions on the Datalake platform.
Read more about Azure Data Lake here:
In the last session slot of the day, I gave a talk called All things time-related, with topics from temporal relational algebra, SQL2016 temporal, processes, Six Sigma, process data warehousing and process mining. At least three participants were inspired by the talk to investigate topics more on their own hand. So, I call that a success.
Read more on these topics here on my blog:
Handouts for the talk is available from the conference website here:
That was it. As always for SQL Saturdays, there was of course all the great social stuff like speaker dinner in an awesome college dining hall, chatting with sponsors, the raffle and a post-event punting experience on the Cambridge river. Special for this SQL Saturday was also the Hitchhikers Guide to the Galaxy theme, it was fun to bring a towel, and have references to 42, pan galactic gargle blasters and Vogon poetry. And I also got to do a little sightseeing in London on the Sunday. But as always, that’s not what this blog is for, we’ll save that for Facebook and Twitter.
So long, and thanks for all the fish (skip to 0:25 in the video to go directly to the fun parts) !