The Pyle Center on the UW Madison Campus at the end of SQL Saturday #604.
Salty and squeaky make a good curd. Informative sessions and engaging conversations make a good conference. SQL Saturday #604 in Madison, Wisconsin this past weekend had plenty of both. I’m happy to have contributed with my session, “XML vs JSON – Battle Royale”.
At the speakers’ dinner on Friday, I met Chris and Gigi Bell and I learned as much as I could from their experience running SQL Saturday events and user groups, as I’ll be organizing one in Minnesota in October. I also bounced some ideas for the event off Tim Plas who’s been involved in past events here in Minnesota. He joined several of our PASSMN friends who traveled down to speak or attend, including Paul Timmerman, Jim Dorame, Mike Donnelly (back temporarily from Japan), and Joshuha Owen.
My first session of the day was about transactions internals with Frank Gill. I’ve never run fn_dblog but it’s clear that Frank knows his way around its output. His demos drove home the impact of page allocation and reminded us that for every log action it saves space for the reaction (in case you abort the transaction). Oh, and before you try to kill that delete which has been running for hours, remember that every one of those rows has to be inserted again.
Next I sought PowerShell wisdom from Drew Furgiuele. Working at a smaller shop mostly on the development side means that I get what I need from pointing and clicking, but I realize database administration at scale means PowerShell. Brimming with energy, Drew disabused us of the idea that he could teach us PowerShell in a session; his goal was to excite us to learn more. First, he demonstrated how PowerShell exposed SQL Server as a file system by navigating around using the “cd” commands of yore. Then he cautioned us to remember that PowerShell is a *scripting* language– that we shouldn’t try to write it with full object oriented vigor. He also gave some style tips– parametertize your code for reuse; avoid screen output unless the caller asks for verbosity; and if you are going to expose results, make sure the caller can pipe them into the form they need. Then he ran through some examples of sophisticated scripts he has created, including one which clones a database (inspired by the need to reinitialize a replication target after a schema change). He also drove home the usefulness of PowerShell (over T-SQL scripting) by demonstrating a database permission audit script which combines SQL Server and Active Directory data.
#SQLSatMadison: @tf3604 on the optimizer: "We're giving SQL Server some requirements and asking it to write a program for us."
— Riley Major (@RileyMajor) April 8, 2017
After lunch, I was humbled by Brian Hansen’s knowledge of SQL Server’s optimizer. He walked through its process of creating a logical plan; simplifying it by using common querying concepts (e.g. treating an EXISTS like an IN) and recognizing logical impossibilities (a < 25 and a > 50); using meta data to short circuit unnecessary work (e.g. relying on a check, unique, or foreign key constraint); then performing cardinality estimates using statistics. He explained how for non-trivial queries, the optimizer could never evaluate every possible query method, instead relying on heuristics and Microsoft proprietary magic to figure out the best option before time runs out. If you really want to geek out, he’s built a program to visualize the in depth output you can tease out of SQL Server using sys_dm_exec_query_optimizer_info and trace flags. The overarching point is that you are telling SQL Server what you want and it’s figuring out the best way to execute the request– writing a little program to do your bidding.
Closing remarks at SQL Saturday 604 in Madison, Wisconsin.
After that intense topic, I sat in Ross LaForte’s session on The Modern Enterprise Data Warehouse, where he explained the current options from Microsoft for data warehousing, ranging from SQL Server 2016 on premises (with Polybase to read flat files and work with Hadoop) to Azure SQL Data Warehouse in the cloud. Just remember not to leave your maxed out Azure instance running when you’re demo is complete.
Then it was time for my showdown between stodgy old XML and hip new JSON. Scripts were run. Data was parsed. Knowledge was spilled.
Want to know more? Review the presentation and my previous blog post.
Who will emerge victorious? #SQLSatMadison pic.twitter.com/y70OcdusBX
— Mike Donnelly (@SQLMD) April 8, 2017
Side note: I just found out today that I’ll also be speaking at That Conference 2017 on stored procedure refactoring. Get your early bird tickets now!