It’s rare to get a call from a customer telling you that the application is fast today and thanks for taking such good care of the database. Instead, you are more likely to hear complaints when things go wrong like slow running queries and timeouts. There is a lot to consider when trying to figure out performance issues, from hardware resources to server settings to query and index tuning. Improving performance can be a lot of work and requires some expertise.

In recent editions of SQL Server, Microsoft has added features that make things better (i.e., go faster) without requiring code changes or even spending a lot of time figuring out what to do on your own. Beginning with 2017, Microsoft began adding to a feature set called “Intelligent query processing” that overcomes some common problems. These features can make queries run faster without changes to code. Note that the compatibility level of your database may need to be adjusted, and that some of the features are only available with Enterprise edition.

Here’s a list of the features released so far:

2017

• Adaptive joins (batch mode): The optimizer can choose the join type (i.e., hash join or nested loop) during query execution based on row count. The feature applies only to queries using a columnstore index. This is an Enterprise Edition feature.
• Interleaved Execution for multi-statement table valued functions (MSTVFs): This feature enables the optimizer to determine the cardinality of MSTVFs during query execution instead of assigning a guess of 100 rows.
• Memory Grant Feedback (batch mode): When the optimizer has determined to use batch mode, it can adjust the memory grant used in subsequent executions of the query. This decreases the likelihood of spilling to disk or underusing the memory assigned. In 2017, batch mode is used only on queries that involve a columnstore index. This is an Enterprise Edition feature.

2019

• Approximate Count Distinct: Use a new function called APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT) when responsiveness is more important than precision.
• Batch Mode on Rowstore: Queries that do not involve a columnstore index can now take advantage of batch mode. This is especially helpful when calculating aggregations over large numbers of rows. This is an Enterprise Edition feature.
• Memory Grant Feedback (row mode): This feature extends the memory grant feature to queries that do not include a columnstore index. This is an Enterprise Edition feature.
• Scalar UDF Inlining: This feature removes some of the pain when including a multi-value UDF in a query by making the operation set-based instead of iterative.
• Table Variable Deferred Compilation: Allows the optimizer to correctly estimate the number of rows in a table variable instead of estimating 1 row.

I have to admit that some of these optimizations make me want to say “don’t do those bad things!” I’ve seen both scalar and multi-statement table valued UDFs cause so many problems over the years, and the optimizations won’t fix everything that you can throw at them.

Today’s Microsoft is serious about SQL Server, and I can’t wait to see what’s next!