#TSQL2sday: AI-Assisted Query Optimization in SQL Server

As a SQL Server professional, I’m always on the lookout for ways to improve my efficiency and effectiveness in my daily tasks. Recently, I’ve been exploring how artificial intelligence (AI) can assist me in optimizing my SQL queries. I wanted to share my experience and insights as part of this month’s T-SQL Tuesday episode – Has AI Helped You with Your SQL Server Job?

The Query Optimization Journey

I had written a query to find products that hadn’t been sold:

SELECT p.ProductID, p.Name
FROM Production.Product AS p
LEFT JOIN Sales.SalesOrderDetail AS s ON p.ProductID = s.ProductID
WHERE s.ProductID IS NULL
ORDER BY p.ProductID
GO

While the query worked, I wondered if there were more efficient alternatives. I turned to a generative AI tool for suggestions. To my surprise, it provided two alternative queries:

Alternative 1:

SELECT p.ProductID, p.Name
FROM Production.Product AS p
WHERE NOT EXISTS (
SELECT 1
FROM Sales.SalesOrderDetail AS s
WHERE p.ProductID = s.ProductID
)
ORDER BY p.ProductID
GO

Alternative 2:

SELECT p.ProductID, p.Name
FROM Production.Product AS p
WHERE p.ProductID NOT IN
(SELECT s.ProductID FROM Sales.SalesOrderDetail s)
ORDER BY p.ProductID
GO

The Execution Plan Surprise

Excited about these alternatives, I checked their execution plans, expecting significant improvements. To my excitement, the execution plans indicated that the alternative queries had much lower costs than my original query.

The original query had a total query cost (relative to the batch) of 77%, while Alternative 1 and 2 had query costs of 11% and 11%, respectively. This was a great revelation, as I expected the AI-suggested queries to be more efficient.

Perplexed, I asked the generative AI tool why it suggested these queries if they were less efficient. It provided several reasons:

1. The NOT EXISTS and NOT IN operators can be more efficient in specific scenarios, mainly when dealing with large datasets and well-indexed tables.
2. These operators can short-circuit the evaluation process, potentially reducing the number of rows needing processing.
3. The AI tool suggested that the alternative queries might perform better in execution time despite the higher query costs.

While the explanations seemed plausible, I needed concrete evidence. To get more insights, I ran the queries with SET STATISTICS IO, TIME ON.

The Revealing Statistics

The statistics shed light on the performance of each query:

Original Query:
Table ‘SalesOrderDetail’. Scan count 1, logical reads 273, …
Table ‘Product’. Scan count 1, logical reads 15, …

Alternative 1:
Table ‘SalesOrderDetail’. Scan count 504, logical reads 1089, …
Table ‘Product’. Scan count 1, logical reads 15, …

Alternative 2:
Table ‘SalesOrderDetail’. Scan count 504, logical reads 1089, …
Table ‘Product’. Scan count 1, logical reads 15, …

The alternative queries had significantly higher logical reads than the original query: 288 logical reads, while Alternative 1 and 2 had 1104. This was indeed very much the opposite of what we have just seen in the execution plan. In reality, while the execution plan cost was higher for the original query with join, it was indeed much more efficient than the other two queries in terms of resource consumption.

Lessons Learned

This experience taught me a valuable lesson: AI can be a powerful tool for query optimization, but it’s not always perfect. While generative AI tools can provide useful suggestions, it’s crucial to validate their recommendations through thorough testing and analysis.

As SQL Server professionals, we should leverage AI to explore alternative approaches and gain new insights. However, we must also exercise due diligence and verify the performance impact of any changes using tools like execution plans and statistics.

AI is a valuable addition to our optimization toolkit but doesn’t replace our expertise and judgment. By combining AI-generated suggestions with our knowledge and rigorous testing, we can unlock new levels of efficiency and performance in our SQL Server environments.

Embrace AI, but always validate and verify. Happy optimizing!

Reference: Pinal Dave (https://blog.sqlauthority.com)