Can AI Read Execution Plans?

Yeah, yeah, second AI post in a row. I promise not to make a habit of it. But I saw someone else mention that you can feed them XML and the AI will read the execution plan. I had to test it out and then overshare my results with all of you.

We Need A Query

Here’s a query:

SELECT c.CustomerID,
       a.City,
       s.Name,
       st.Name
FROM Sales.Customer AS c
    JOIN Sales.Store AS s
        ON c.StoreID = s.BusinessEntityID
    JOIN Sales.SalesTerritory AS st
        ON c.TerritoryID = st.TerritoryID
    JOIN Person.BusinessEntityAddress AS bea
        ON c.CustomerID = bea.BusinessEntityID
    JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
    JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE st.Name = 'Northeast'
      AND sp.Name = 'New York';

This query results in this execution plan:

There are some tuning opportunities here. There are two index scans that could benefit from a new index to retrieve data and there’s the key lookup that could be eliminated. Otherwise, this isn’t a query and plan with crazy obvious problems.

As we should all know by now, using one of the LLMs (AI) effectively, is very much about building the prompt. Here’s what we’ll try:

Using the attached SQL Server execution plan in XML, please identify any performance tuning opportunities that are visible within the plan.

CoPilot Reading Execution Plans

Oof. Well, it’s not going well. I don’t want to fill the post with lots of nonsense. Let’s just say, I tried CoPilot and Perplexity (using Claude) to get answers. I attached the XML and used the question as described. The answers were… not unhelpful, but also, not specific. Certainly I don’t feel like my job as a query tuner is in danger here immediately.

CoPilot had a couple of good suggestions. Here for example:

Index Seek and Scan Usage: While index seeks are preferable for efficient query execution, the presence of index scans suggests that specific indexes may be missing or poorly designed. Examine if the indexes on the involved tables (like Sales.Customer, Sales.Store, etc.) align with the query’s filtering and joining logic

It’s right about the Customer table. Not the Store table though. It missed the Key Lookup entirely, but it did spot something I hadn’t looked for when I first ran this query (despite being on my list of things to look at when reading execution plans):

Timeouts and Suboptimal Optimization Levels: The “StatementOptmEarlyAbortReason” reflects a timeout during optimization. This could lead to suboptimal plans being generated. Increase query timeout limits if applicable.

Yeah, the optimizer timed out making this plan. You never know exactly why, but the easiest explanation is because the optimizer didn’t have the tools it needed, good indexes, so it was working out all sorts of ways to deal with that lack.

Perplexity Reading Execution Plans

With Perplexity and Claude, I opted for the deep research option to see what it came up with. Hoo boy, it’s a document and a half. It jumped on the timeout immediately:

Upon analyzing the attached SQL Server execution plan, a significant optimization issue is immediately apparent. The execution plan reveals that the query optimizer reached its timeout threshold before completing a full optimization, which may be preventing the query from achieving optimal performance. This report examines this issue and other potential performance optimization opportunities.

CoPilot went into a pretty good explanation of the issue, most of it pulled from this document at Microsoft. It then made a whole bunch of generic suggestions. There wasn’t any specificity at all. It did discuss Key Lookups as one of the tuning opportunities though. All the other suggestions and discussions were good and accurate. I don’t have any quibbles with the answer as such. However, I’m no closer to making that query run faster. Lets try one more prompt.

Please Help Me Tune My Query!

Here’s the follow up prompt I’m going to put in to both AIs:

Are there any specific indexes you can suggest based on this query and execution plan that would improve performance?

And now I am nervous. The output this time was pretty good. Both engines made a number of suggestions for indexing improvements that I absolutely agree with. They focused on JOIN conditions as well as the WHERE clause. Again, I agree. Both spotted one of the big performance bottlenecks in this query, the scan on the BusinessEntityAddress table.

A covering index on (BusinessEntityID, AddressID) can optimize the join condition.

Perplexity went farther than CoPilot, discussing all it’s decisions and why it focused where it did. It’s an excellent, and pretty accurate, assessment of where indexes could improve the query. It was also extremely cautious saying that testing and consideration of existing indexes is important. It even gave me a lovely table:

Conclusion

People, we have competition. Do I agree with everything the AIs have said about these indexes? Nope. Is it likely that these indexes will help performance? Oh yes. Absolutely. They’re dead on right about the BusinessEntityAddress table, just as one example. So, yeah, AI can read execution plans and then it can make pretty good suggestions for indexing to improve performance. I’m not saying we’re out of a job. I am saying two things. First, you have another tool in your toolbox. Throw that execution plan at an AI to get some quick suggestions on tuning. Second, be prepared for other people to be doing this and maybe, having too many or repetitive indexes in your database. After all, the AI is operating in a bit of a vacuum here. It can only see the query and the plan, not the whole database.

Exciting times ahead. Remember, this is as dumb as these AIs will ever be.