Last Season’s Performance Tuning Techniques: Slides & Demos

#SQLPass, Development

Right now, Erik and I are presenting at the 24 Hours of PASS. We’re talking about Last Season’s Performance Tuning Techniques:

Wanna play along with us as we show how your performance skills might be a little out of date? Here’s the demos and the slides:

Fill Factor: Doing the Page Splits

You can do this one in any database, but you’ll want to do it on a server with very low load. If anybody else is doing any deletes/updates/inserts at all, it’s going to skew your numbers.

Questions to think about:

  • What does a page split really mean?
  • Is there such a thing as a good or a bad page split?
  • How do you know which ones you’re having?
  • Would setting fill factor have prevented that page split?

Missing Indexes

This one requires the Stack Overflow demo database. If you don’t already have a copy of that, don’t try to download it live during the session – it’s too big (~15GB torrent, then expands to a ~100GB SQL Server database.)

Get the estimated execution plan for this:

And then ask yourself:

  • What index am I told to create?
  • Does that index make sense?
  • Is there anything SQL Server isn’t telling me?

Now try the estimated plan for this:

And ask yourself those same questions.



Sometimes a CTE won’t change anything at all. This is the case with simple predicates.

CTEs don’t materialize results. What do you think this is, Oracle?

If you join a CTE to itself, you’ll run the CTE query again.

Thankfully, nested CTEs don’t exhibit the same problem.

CTEs and derived tables will behave similarly as far as performance and query plans go.

One difference is that you can’t reference a derived table more than once, where you can do that with CTEs.

CTEs are cool though. You can filter on things on the outside that you can’t filter on the inside.


This query runs without a function and finishes pretty quickly.

If we turn that string aggregation expression into a scalar valued function…

Now we can crap up all our queries effortlessly.

Checking on query performance with sp_BlitzQueryStore…

Computed columns with Scalar Valued Functions in them will be similarly crappy.

Crappiness doesn’t depend on whether or not we select the computed column. It’s there no matter what.

Let’s see what happens when we add in a check constraint based on a UDF.

What to look for in XE: executions of function after inserting rows. Executions after selecting data.

Using an inline TVF makes things faster for the query, but we can’t use it in a computed column. Other downsides: inline TVFs aren’t tracked in DMVs (2016 has a function_stats DMV that doesn’t catch them).

This is the same in Query Store.

Temp Tables or Table Variables

Table variable modifications are forced to run serially.

They’re also not guaranteed to be in memory. Backed by temp objects which may spill to disk.

Bad estimates may prevent parallel plans from happening when they should have.

Does recompiling always make things better?

Temp tables generally work better!

Do indexes change anything?


Are your performance skills out of fashion?

If you learned things during the webcast, and you’re starting to question your taste, have no fear: we’re here to help. We’re doing an all-day pre-con class before the PASS Summit called Expert Performance Tuning for SQL Server 2016 & 2017. We specifically designed it to update your performance skills for today – and a lot of the techniques are even useful on currently patched versions of 2012 & 2014, too. Learn more and register for the pre-con.

Previous Post
sp_BlitzCache: Eventual Compatibility With Azure
Next Post
What Do You Think About ORMs?

6 Comments. Leave new

  • Great Session, really enjoyed the flashbacks!

  • Noah Engelberth
    July 19, 2017 8:53 am

    From what I’ve seen with a recent Inline TVF that I did (to replace three scalar UDFs), I wonder if part of the reason that TVFs aren’t tracked in DMVs is because the optimizer treats them like a regular view. I definitely was seeing the I/O and TIME statistics in my “outer query” once I switched to the I-TVF, whereas when it was doing UDFs, the statistics for the UDF weren’t visible from the outer query and you had to check the DMVs to see how ugly they were.

  • Doug Ledbetter
    July 21, 2017 4:38 pm

    I just wanted to say that putting Groove VARCHAR(100) into table TheHeart made me laugh out loud. I appreciate the subtle humor! Keep it up!

  • Wow – i really enjoyed this session. I wish every learning video session would have this much laughing and funny pictures in it – it would help me not falling asleep and actually stay focused on the session and not check social media during watching.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.