Shop Talk: 2024-02-26 – Shop Talk with TriPASS

The Recording

The Panelists

Kevin Feasel
Mike Chrestensen

Notes: Questions and Topics

Performance Tuning Large Execution Plans

Our first topic comes from Mike and is all about how to tune queries that result in large execution plans. I went through a couple of techniques but mostly focused on using Solarwinds Plan Explorer. This free tool is fantastic for analyzing SQL Server queries and I talk about some of its coolest functionality. I used an old Brent Ozar post as an example of a, uh, large execution plan.

Anders mentioned a pair of Grant Fritchey books that are also quite helpful. SQL Server 2022 Query Performance Tuning is a powerhouse of a book and I highly recommend it. And his SQL Server Execution Plans book is free from Red Gate, so grab a copy of that for free.

A Book Review

After that, I talked about a book review of Peter Carter’s book 100 SQL Server Mistakes and How to Avoid Them. I have a lengthier blog post that covers my full thoughts so check that out. Short version: I recommend buying the book, even though I don’t agree with everything in it.

ORDER BY Not in SELECT

Our next topic involved a bit of trivia: in SQL Server, you can have columns in your ORDER BY clause that do not appear in your SELECT clause. For a long time, this was not possible in Oracle, as Mike points out, but by Oracle 19C, you now have that ability, too.

Terminology Time

The final topic we hit was a bit of terminology, where I helped explain to Mike the difference between a few terms. As I noted on the video, I’m definitely not the poster child for terminological exactitude, but I did have the answers for this one. My answers are:

Clause — A primary part of a SQL statement or query. Examples include SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, etc.
Statement — An operation that does not return a value. For example, I can run an UPDATE statement. It updates my table but does not return anything. In T-SQL, CASE is not a statement because it returns something. In F#, case is not a statement because it returns something. In C#, case is a statement because does not return anything.
Expression — An operation that returns a value of a particular type. In T-SQL, CASE is an expression because you have to return something: that’s the THEN part of a CASE expression, as in, CASE WHEN <predicate> THEN <thing to return> END. Speaking of predicates:
Predicate — An expression that returns TRUE, FALSE, or (in T-SQL) UNKNOWN. The contents of your WHERE clause is a predicate, but so is each bit within your WHERE clause: T1.Col1 > 3 is a predicate, because, for each row, T1.Col1 is either greater than 3, less than 3, or NULL. And the combination of T1.COL1 > 3 AND T2.Col2 < 17 is a predicate: the whole returns one of true, false, or unknown. Butt so do each of the parts making up the whole predicate.