Free Fundamentals of Query Tuning Week: Part 3, How Parameters Influence Cached Plans

While you practice physical distancing (words I never thought I’d daisy-chain together), I’m giving away free SQL Server training. So far, we’ve covered building a query plan and how to find the right queries to tune. Make sure to keep up with the series – on April 1, these posts will magically disappear.

When you’re tuning queries, picking the right parameters is crucial. So often, you can tune for one particular set of parameters – only to result in different parameters performing poorly.

We’ll start with the query we used in the first module, searching user comments by location & date range, and then start changing the location parameter. You’ll see how different parameters radically affect the shape of the plan: which table SQL Server chooses to process first, and how it chooses to access those tables (seeks vs scans.)

We’ll finish up by talking about the kinds of parameters to look for when you’re preparing to tune a query, and how to get them.

Part 1: Lecture (41m)

Part 2: Homework (7m)

If you like this, you can keep learning this year with:

But you, dear reader, are too smart to pay full price. You can score my Live Class Season Pass for $995 or the recordings of the Mastering classes for $795. Use coupon code ReallyBadWeek for another 10% off that price, and I’ll donate 10% of the sales to Doctors Without Borders.

Demo Script

Later this week, I’ll cover improving cardinality estimation accuracy, then common T-SQL anti-patterns.

Previous Post
Free Fundamentals of Query Tuning Week: Part 2, How to Find the Right Queries to Tune
Next Post
Free Fundamentals of Query Tuning Week: Part 4, Improving Cardinality Estimation Accuracy

9 Comments. Leave new

  • Hi Brent, I started doing the homework and executed the updated select from the stored procedure, I get the following tempdb error
    Msg 1101, Level 17, State 10, Line 330
    Could not allocate a new page for database ‘TEMPDB’ because of insufficient disk space in filegroup ‘DEFAULT’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    My temdb data file was limited to 300M, I increased up to 1200M but still get the same error, this is the script that causes the error which I have never seen

    SELECT TOP 100 c1.UserId, c2.UserId, COUNT(*)
    FROM dbo.Comments c1 INNER JOIN dbo.Comments c2 ON c1.PostId = c2.PostId AND c1.Id c2.Id
    GROUP BY c1.UserId, c2.UserId
    ORDER BY COUNT(*) DESC;

    Reply
    • Salam – first, whenever you have a question, make sure to actually ask it as a question. The sentence should have a question mark at the end of it, like, “Can a SELECT fill up TempDB?”

      If that’s your question, then yes, if you’re running on underpowered hardware, it absolutely can. Go back and watch the How to Think Like the Engine class where I talk a little about TempDB spills. You probably need a larger machine. It’s not unusual to need 20-25% of the database’s space in TempDB. If you’re using the 50GB StackOverflow2013 database, that means you would need ~13GB of TempDB space, or more.

      Reply
  • Thanks Brent, No, I have the 50GB, it is a dev VM. OK I will review your course. However, all other SPs and statements of part 1,2 are running fine. Why this one is not going through?

    Reply
  • Yes, I will do, You were as always correct, just after your 1st response, I increased the tempdv limit file size to 3G and the statement went through as it needed 2.7G

    Reply
  • Really loving these sessions, and the scripts make it super easy to test things like this out.
    I did a bit of testing with the debug table – thought this was very clever, and it re-affirmed what I’ve learned from watching your videos already….

    If i change the stored proc to log to the debug table, it creates a new plan (or the subsequent running of the query does). (guessing this is why i’ve seen some people just alter the proc keeping the code the same).
    So, by making the change to log the results, there’s a chance i no longer have the same scenario i began with (unless i get the first execution to use the original Cached Execution Parameters.
    Suspect extended events has some clever way to capture the same detail.

    Reply
  • Brent, I’m enjoying these courses. Thanks for making them available. I really appreciate your remote tutelege and unraveling the mysteries of the database engine.
    Hope things are going well for you as this lockdown continues!

    Reply

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.

Menu
{"cart_token":"","hash":"","cart_data":""}