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."

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.