24 Hours of PASS: Summit Preview Edition
- Download the slide deck and demos
(.zip, ~2 MB)
Everything went smoothly; Andrea Allred (@SQLRoyal) was a great moderator, and got onto a meeting with me earlier in the day to test out audio and video in both directions. I'm writing for two reasons: to share the deck and sample code, and to address questions that came up during the session.
Pretty bleepin' cool. Especially the orientationally correct hand-drawn Beaker, and the movie-poster-looking SILENT TRUNCATION headline (it is scary).
Q. What versions of SQL Server support OPTIMIZER_WHATIF and the weight-related DBCC commands?
DBCC SETIOWEIGHT / SETCPUWEIGHT / SHOWWEIGHTSis supported in SQL Server 2005 and up.
DBCC OPTIMIZER_WHATIF, on the other hand, is only supported starting in SQL Server 2008 (I incorrectly stated during the session that this was available in 2005, and that was just bad memory).
Q. What is the scope of a trace flag?
OPTION (QUERYTRACEON). I've blogged about tracing the use of trace flags, and that post includes links to several other posts and useful documents.
Q. What is the color scheme and font you are using in SSMS?
Q. What is the best book for learning about execution plan operators?
Q. Is there guidance for a ratio of clustered index seeks : lookups that indicates a problem?
Q. Is it recommended to use ad hoc queries or stored procedures in terms of performance?
Q. Which trace flags mentioned only work with the 2014 Cardinality Estimator?
Q. Does heap representation apply to temporary tables and table variables?
Q. Are execution plans used for ad hoc queries, or just stored procedures?
Q. Are table-valued parameters useful, or should I avoid them?
Q. What is the link for information about the implicit conversion case you mentioned?
Q. Could you go into more detail about spills and spools?
A spool is basically a copy of some set of data, and it is used either because the data will be used multiple times, or because it needs to be isolated from the original (e.g. for Halloween protection). Rob Farley (@rob_farley) goes into a lot of detail about spools in a T-SQL Tuesday post here; Paul White has a great four-part series on Halloween protection starting here; and Fabiano Amorim talks about various kinds of spools here, here, and here.
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.