Adaptive Joins and Join Hints

SQL Server 2017
At a recent all-day seminar on query performance tuning I was asked a question that I didn't know the answer to: "How do join hints affect adaptive joins?" I don't know. Let's find out together. Adaptive Joins Here's a query that we can run against AdventureWorks: [crayon-5bc89a94cf5f3957832128/] Without a columnstore index in SQL Server 2017, the execution plan looks like this: Let's introduce a columnstore index: [crayon-5bc89a94cf602198201005/] Now, if we run the same query, the execution plan changes to use an adaptive join like this: You can read more on adaptive joins here if this is new behavior. Now, what if we decide that we just want to see a hash join all the time? Adding a Join Hint I'm not a giant fan of any of the query hints.…
Read More

Restoring a Database in Azure

One of the many small things you don't have to worry about any more when working with Azure SQL Database are those pesky backups... Ha-ha-ha! Yeah, I can't keep a straight face when I type it. Microsoft takes on the role of DBA and creates backups and log backups for you when you're working in the Platform as a Service offering of Azure SQL Database. If that doesn't scare you, I'm not sure what will. However, there's good news. There are still ways for you to create your own backup (through the bacpac, I outlined it here, years ago). More good news is, Microsoft's backups actually work. Testing it out is easy. Let's walk through it once. I'm going to assume you have an Azure account on which you already…
Read More

FAST n Query Hint

SQL Server, T-SQL
Just because a doctor says take two aspirin, taking an entire case won't make you better. If one is good and two is better, 2 million might not be best.  What the heck am I talking about? We have an application that for one reason and another, has some overly complex procedures and queries. A lot of time and effort has been spent getting these queries to work well. Some of that time & effort came from one Microsoft consultant, Bill Sulcius. He's a great guy, very smart, very helpful and I learned a lot from him. On a few of the queries that Bill helped tune, he added some query hints, FAST 1 and KEEPFIXED PLAN. Where he added these hints, he could demonstrate, with good sets of data…
Read More