PASS Summit 2018 Highlights

Another year down and PASS Summit 2018 in Seattle was an immersive learning and networking experience yet again. This year my time was mostly dedicated to sessions around query performance tuning and Azure high availability technologies.

Before listing the highlights, I’ll say again, that if you aren’t attending PASS, you are really missing out.  I understand that not everyone can find the funding to go, as the total bill gets up there to the $4-6k range depending on whether or not you do pre-cons, purchase the recordings (which I highly recommend) and where you stay.  But being persistent and doing your homework before talking to your company can really pay off if they understand the value you will bring back from just a few days at PASS.

Highlights:

  • On Monday I sat through a pre-con on Performance-Centric T-SQL with Itzik Ben Gan and it was one of the better sessions I’ve ever attended.  He has such a clear way of communicating, answering questions with ease along the way, and seems to just flow through the entire eight hours. We went over Window Functions, the APPLY operator uses, OFFSET/FETCH, and much more.  If you ever have the chance to see him speak, take it.
  • Tuesday’s pre-con on Fixing Query Performance from Statistics, Heuristics and Cardinality was presented by Kimberly Tripp and was a brain melting masterpiece. As Kimberly says, we often blame statistics for everything without really knowing what is going on. Very frequently the issue turns out to be a bad cached plan or a parameter sniffing issue. In this session she goes over everything from why you should use sp_executesql to using OPTION (RECOMPILE) at the statement level even in stored procs instead of WITH RECOMPILE, SQL Servers Auto Pilot capability, pseudo partitioning using views and more.
  • How to use dbatools to the fullest
  • Preview of one of the best improvements I’ve seen come out in a long time, Accelerated Database Recovery. ADR makes all operations versioned, meaning it can rollback a transaction and cancel a query almost immediately.  It also means that recovery of a secondary after a failover with Availability Groups is nearly instant, as is recovery from a reboot. This is huge when you have multi-terabyte databases!
  • SQL 2019 (and I’m assuming Azure sooner) will introduce Resumable Index Creation (and the existing Resumable Online Rebuilds).  This is great because you can build a new index online and stop it and resume it as many times as is necessary to get it created.  You can even enable a trace flag that can make it the default.
  • The Azure Architecture Center (https://docs.microsoft.com/en-us/azure/architecture/) is a great place to get all the architectural documentation and examples you can think of.
  • Query Tuning Internals tidbits
    • < or > works the same as <> but has better performance and query plans because it is set based
    • DBCC HELP (‘?’) will give you all available DBCC commands, and if you use trace flag 2588 it will also give you the undocumented ones
    • When you want to test query plans based on different hardware, you can close the database with no data by doing a generate scripts and enabling the “Script Statistics” advanced option.  Then be sure to turn off the Auto Create/Update Statistics so they don’t get updated after you create them on another machine.  Using DBCC OPTIMIZER_WHATIF() commands you can make the optimizer run execution plans as if you were using much larger or smaller hardware, giving a much better sense of how production might act, but from your laptop.
  • If you get MAX() quite often, you can look at rewriting it as a CROSS APPLY to get better query plans and performance.
    • Example:
      • SELECT s.shipperid, a.maxod FROM dbo.shippers as s CROSS APPLY (SELECT TOP (1) FROM o.orderdate FROM dbo.orders as o WHERE o.shipperid = s.shipperid ORDER BY o.orderdate desc) as a(maxod);
  • Writing an INSERT statement with a GO {INT} will loop the statement
    • Example:
      • INSERT #Temp (ROWID) VALUES (NEWID())
        GO 1000

These are just some of the awesome things that I gleaned from the sessions at PASS this year and can’t wait to go back next year!

By |2018-11-10T12:06:53+00:00November 10th, 2018|Career, I'm Your DBA Blog, SQL Server|