Yesterday I had the pleasure of presenting a PASS Summit teaser during 24 Hours of PASS (24HOP): a substantially abbreviated version of my pre-conference session, 50 Things All SQL Server Developers Need To Know.
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.
I also wanted to highlight a very cool thing that Matt Penny (@salisbury_matt) has been doing for many of these sessions: sketchnotes. Here’s his summary of my session (click to enlarge):
Pretty bleepin’ cool. Especially the orientationally correct hand-drawn Beaker, and the movie-poster-looking SILENT TRUNCATION headline (it is scary).
Questions
Q. What versions of SQL Server support OPTIMIZER_WHATIF and the weight-related DBCC commands?
DBCC SETIOWEIGHT / SETCPUWEIGHT / SHOWWEIGHTS is 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?
Cliché alert: It depends. Some trace flags can only be set at startup time, some can be set globally, some can be set per session, and some can be set per query using 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?
I’ve been asked this question before after presentations, both online and in-person. I wrote a blog post in response, where I explain my color choices and offer a .vssettings file you can import into your own environment: Making SSMS Pretty : My Dark Theme. During the session I also mentioned the open source “Hack” font, which I have been experimenting with; you can check it out here.
Q. What is the best book for learning about execution plan operators?
Grant Fritchey and Benjamin Nevarez have both put out great books on query tuning. If you want to go deep into the rabbit hole, many blog posts by Paul White (on SQLPerformance.com or on sqlblog.com) will stretch your brain. For general query efficiency, I would grab any of Itzik Ben-Gan’s modern books on T-SQL.
Q. Is there guidance for a ratio of clustered index seeks : lookups that indicates a problem?
I don’t think there is any magic threshold, no. Index operational stats can certainly be useful information, but I don’t know that any metrics there can serve as a useful warning metric in isolation. There are so many other things to look at when trying to solve a performance problem, never mind trying to invent one!
Q. Is it recommended to use ad hoc queries or stored procedures in terms of performance?
In ancient versions of SQL Server, a stored procedure was compiled or recompiled all in one shot. These days, there really is little to no difference between ad hoc queries and statements inside stored procedures, and it is a myth that procedures will provide better performance (in general; as with many things, there are always edge cases. However, I still prefer stored procedures from a manageability perspective – but will not be opening that can of worms here.
Q. Which trace flags mentioned only work with the 2014 Cardinality Estimator?
Of the trace flags I mentioned in my session, only two have different behavior under the new 2014 CE. 2301 no longer works there (but others that tell the optimizer to spend more time/effort finding the optimal plan still do), and 4137 has been replaced in the new CE with 9471.
Q. Does heap representation apply to temporary tables and table variables?
I’m not quite sure I understand the thrust of the question, but yes, even though temporary objects can possibly be memory-resident and never touch disk in some scenarios, there can still be benefits to use clustered indexes, particularly if you are joining or seeking against the index key. (And if you’re thinking about using a clustered index on a table variable because you suspect the lack of one is leading to performance issues, it’s quite likely you’re putting way too much data into your table variable and you should be using a #temp table instead.) For a great analysis of the differences between #temp tables and table variables, see this fantastic answer from Martin Smith.
Q. Are execution plans used for ad hoc queries, or just stored procedures?
Execution plans are generated for both ad hoc queries and queries inside stored procedures.
Q. Are table-valued parameters useful, or should I avoid them?
There are definitely classes of use cases where TVPs are beneficial; namely, passing sets of data into SQL Server from applications. Typically the way people do this today, is they’ll have a DataTable or some other structure in C#, then they’ll have to convert that data into comma-separated lists or XML, and then have SQL Server parse that incoming data and turn it back into a set in order to do anything with it. I wrote a blog post about why TVPs are better in the string splitting case: Splitting Strings : Now with less T-SQL.
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?
Spills generally happen because of an insufficient memory grant – which may be due to poor statistics, Resource Governor settings, or lack of available memory. The plan needs to perform some operation in memory (typically a sort), and when the operation can’t fit into the memory that’s been allocated, it spills to tempdb. This impact can be much more pronounced when the operator that spills is parallel, because every thread will then compete for tempdb usage. Paul White ( @SQL_Kiwi) goes into some detail here.
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.
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.
