How Does SELECT * Affect Query Store?

I live for questions and recently, I had someone ask me, does using SELECT * affect Query Store. My immediate gut reaction was, hell no. Of course it doesn’t. Then, yeah, I started thinking. It might. Let’s test it and see.

The Setup

The trick here is to get a good setup. I need a query that’s… interesting. Meaning, I need the query to do more than just SELECT * from a table. Although, let’s start there:

SELECT *
FROM dbo.Customers AS c;
GO 20

I ran it several times so that it will, for sure, get captured by Query Store (when on Auto, in 2022, one execution of a query may not be enough to see it captured by QS). Then, I took a look at the plan:

SELECT CAST(qsp.query_plan AS XML)
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id
    JOIN sys.query_store_plan AS qsp
        ON qsq.query_id = qsp.query_id
WHERE qsqt.query_sql_text = 'SELECT *
FROM dbo.Customers AS c';

Which results in this plan:

Nothing exciting to talk about here, but, I want to add a wrinkle. The original question wasn’t simply, can you capture SELECT * in Query Store, because, of course you can. No, the original question was, will changing columns while using SELECT * affect Query Store. Let’s find out.

First, let’s modify the table:

ALTER TABLE dbo.Customers
ADD TestingSelectStar VARCHAR(50) NULL;

Now, I’ll go back up and run my initial query again, 20 times again. Looking at Query Store, same plan. No additions. However, this gets fun. The result set clearly shows my new ‘TestingSelectStar’ column. However, check the output of the Clustered Index Scan:

See what’s not there? No ‘TestingSelectStar’. The plan is the same. Adding the column didn’t cause an immediate recompile. OK. Let’s yank that plan out of cache:

DECLARE @PlanHandle VARBINARY(64);

SELECT  @PlanHandle = deqs.plan_handle
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text = 'SELECT *
FROM dbo.Customers AS c;'

IF @PlanHandle IS NOT NULL
    BEGIN
        DBCC FREEPROCCACHE(@PlanHandle);
    END
GO

SIDE NOTE: Notice that to search for the code in Query Store, the semi-colon is stripped away. Yet, for the DMVs, I have to use it.

Now, did we get a new plan in Query Store? No. Did we get a new plan in cache? Yes. If I look at the output as before, it now has the column I added:

So, what’s the deal? Well, if we look at the query_plan_hash values for both plans, they’re the same: 0xAB122F1D385944BC. This means, despite some small changes, a difference in the Output, the plan is effectively identical. Because of this, the plan stored in Query Store doesn’t get updated.

Right, so what about a more complex plan?

Does SELECT * Affect Query Store?

Let’s start with a more complex query. These are all running against the WestWind database (I had it sitting around from a class with Bob Ward):

SELECT *
FROM dbo.Orders AS o
WHERE o.CustomerID = 'WARTH'
      AND o.EmployeeID = 2;

This results in a much more interesting execution plan:

We’re getting an index join between the two nonclustered indexes on EmployeeID and CustomerID. Because of the SELECT *, it then goes to the Key Lookup to retrieve the rest of the columns. Right, let’s change the table, but I’m going to add… more, to see if we can affect plan behavior:

ALTER TABLE dbo.Orders
ADD UniqueColumn VARCHAR(20) NULL CONSTRAINT UniqueColumnConstraint
                                  DEFAULT 'Hmmm...',
    NotherColumn INT NULL,
    OneMore INT CONSTRAINT OneMoreDefault
                DEFAULT 42;

Right, so, after clearing the plan out of cache and forcing a recompile, what do we see? Same plan.

Conclusion

So, does SELECT * affect Query Store? Short answer, no, not really. We can change the underlying structures, and we’ll end up with the same plan because, simply changing the output, at least in these examples, wasn’t enough to change the plan hash. No change in plan hash, no new stuff in the Query Store.

An interesting note, as I kept running the final query over & over to see how the plan was affected, ultimately I kicked off Intelligent Query Processing and got a new plan from Query Store: OPTION(USE HINT(‘ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS’)). I thought that was a giggle. The plan changed to a single Index Seek and a Key Lookup because of the forced hint. Fun stuff.