This lab is left as an extra exercise for the reader if you’d like to do it later on your own. So far in the class, we’ve been using 2017 compatibility mode. If you’d like to see how 2019 performs differently, you can turn on all of the cutting edge features on your version of…
In our last module, we hit a wall when we tried to use index tuning to solve a problem where SQL Server had to choose between two different indexes for the same table. Sometimes a date range was more selective, and sometimes a score was more selective. When you’re facing the problem of which index…
We’ll talk through the WHERE, FROM, SELECT, JOIN, GROUP BY, and ORDER BY components to understand how they affect parameter sniffing. Then, we’ll layer on more complexity to our parameter sniffing problem by first adding an index (which backfires catastrophically), and then adding more parameters (making SQL Server’s choices even tougher.) Demo Script Transact-SQL /*…
Each new version of SQL Server has brought a few new automated performance tools: adaptive joins, adaptive memory grants, interleaved execution for MSTVFs, and “automatic tuning” (which is a laughable name given what the feature actually does.) We’ll talk through what each feature does and what it requires, and you’ll see why it didn’t fix…
