A couple years back, I realized there were at least Ten Ways to Set MAXDOP. These days, as I write and tune queries, I realize there are an awful lot of ways to change the Cardinality Estimator (CE) and Query Optimizer, too. Let’s take a quick survey of five intriguing options.

1. Change your SQL Server version. As you upgrade to 2014 and beyond, new options become available to you. A lot of what I’m about to discuss changes from version to version, plus changes up in Azure SQL DB and Managed Instances, too. I know that’s not a trivial change, but I mention it because you’re going to be hearing a lot about how SQL Server 2019 makes your queries faster by injecting things like deferred compilation for table variables, or batch mode for rowstore indexes.

2. Set your database compatibility level. In SSMS, right-click on the database, go into Options, and there’s that Compatibility Level. For years it’s been like the hallway light switch that you flipped back and forth, and it never seemed to do anything, so you just left it wherever. Starting in 2014, it triggers newer Cardinality Estimator logic.

3. Set the database-scoped options. While you’re in there right-clickin’ on databases, starting in SQL Server 2016, there are new database-scoped configuration options. You can set them at either the primary or secondary replica level, too, giving you different behavior for reports running on your replicas. (Very nice touch, but one I confess I’ve never actually used.) One of these options is Legacy Cardinality Estimation – but also note the option for Query Optimizer Fixes. I always chuckle at that – who wouldn’t want fixes? (Well, because they’re not always fixes – sometimes they’re breaks.)

4. Use a server-level trace flag like 4199, which at first sounds really simple, but buckle up. The behavior you get changes based on your compatibility level, too, as shown in Konstantin Taranov’s excellent trace flag documentation:

Trace flag 4199 is probably the best-known plan-influencing flag, but it’s by no means the only one. Do a search for “cardinality” or “optimizer” on Konstantin’s trace flag list, and you’ll be stunned at the number of known options out there. You certainly wouldn’t want to enable them all on a server level.

5. Use a trace flag at the query level. Since 2005 SP2, you’ve been able to use OPTION (QUERYTRACEON 4199) at the query level to turn on a trace flag for a specific query. These days, it’s an officially supported tactic – here’s the knowledge base article with documentation – as long as you’re using trace flags 4199, 2335, 2340, 2389, 2390, 4136, 4137, 4138, 9481, or 2312.

That does require changing the query in the sense that you’re tacking a trace flag on to the end of it, but at least you’re not changing the logic or the indexes, so it can be a good option for experimenting.

I probably wouldn’t try these if I only had 1-2 hours to tune a query – I’d focus on conventional query and index tuning techniques first. However, when you’ve got a day to tune a query, these are fun tricks to try.