Query Tuning – Page 21

Why Don’t I Have Any Missing Index Requests In My SQL Server Database?

Posted on December 17, 2019September 21, 2024 by Erik Darling

This was originally posted by me as an answer here. I’m re-posting it locally for posterity.

There are many reasons why you may not have missing index requests!

We’ll look at a few of the reasons in more detail, and also talk about some of the general limitations of the feature.

General Limitations

First, from: Limitations of the Missing Indexes Feature:

It does not specify an order for columns to be used in an index.

As noted in this Q&A: How does SQL Server determine key column order in missing index requests?, the order of columns in the index definition is dictated by Equality vs Inequality predicate, and then column ordinal position in the table.

There are no guesses at selectivity, and there may be a better order available. It’s your job to figure that out.

Special Indexes

Missing index requests also don’t cover ‘special’ indexes, like:

Clustered
Filtered
Partitioned
Compressed
XML-ed
Spatial-ed
Columnstore-d
Indexed View-ed

What columns are considered?

Missing Index key columns are generated from columns used to filter results, like those in:

JOINs
WHERE clause

Missing Index Included columns are generated from columns required by the query, like those in:

SELECT
GROUP BY
ORDER BY

Even though quite often, columns you’re ordering by or grouping by can be beneficial as key columns. This goes back to one of the Limitations:

It is not intended to fine tune an indexing configuration.

For example, this query will not register a missing index request, even though adding an index on LastAccessDate would prevent the need to Sort (and spill to disk).

SELECT TOP (1000) u.DisplayName FROM dbo.Users AS u ORDER BY u.LastAccessDate DESC;

Nor does this grouping query on Location.

SELECT TOP (20000) u.Location FROM dbo.Users AS u GROUP BY u.Location

That doesn’t sound very helpful!

Well, yeah, but it’s better than nothing. Think of missing index requests like a crying baby. You know there’s a problem, but it’s up to you as an adult to figure out what that problem is.

You still haven’t told me why I don’t have them, though…

Relax, bucko. We’re getting there.

Trace Flags

If you enable TF 2330, missing index requests won’t be logged. To find out if you have this enabled, run this:

DBCC TRACESTATUS;

Index Rebuilds

Rebuilding indexes will clear missing index requests. So before you go Hi-Ho-Silver-Away rebuilding every index the second an iota of fragmentation sneaks in, think about the information you’re clearing out every time you do that.

You may also want to think about Why Defragmenting Your Indexes Isn’t Helping, anyway. Unless you’re using Columnstore.

Adding, Removing, or Disabling Indexes

Adding, removing, or disabling an index will clear all of the missing index requests for that table. If you’re working through several index changes on the same table, make sure you script them all out before making any.

Trivial Plans

If a plan is simple enough, and the index access choice is obvious enough, and the cost is low enough, you’ll get a trivial plan.

This effectively means there were no cost based decisions for the optimizer to make.

Via Paul White:

The details of which types of query can benefit from Trivial Plan change frequently, but things like joins, subqueries, and inequality predicates generally prevent this optimization.

When a plan is trivial, additional optimization phases are not explored, and missing indexes are not requested.

See the difference between these queries and their plans:

SELECT * FROM dbo.Users AS u WHERE u.Reputation = 2; 

SELECT * FROM dbo.Users AS u WHERE u.Reputation = 2 AND 1 = (SELECT 1);

The first plan is trivial, and no request is shown. There may be cases where bugs prevent missing indexes from appearing in query plans; they are usually more reliably logged in the missing index DMVs, though.

SARGability

Predicates where the optimizer wouldn’t be able to use an index efficiently even with an index may prevent them from being logged.

Things that are generally not SARGable are:

Columns wrapped in functions
Column + SomeValue = SomePredicate
Column + AnotherColumn = SomePredicate
Column = @Variable OR @Variable IS NULL

Examples:

SELECT * FROM dbo.Users AS u WHERE ISNULL(u.Age, 1000) > 1000; 

SELECT * FROM dbo.Users AS u WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 5000;

SELECT * FROM dbo.Users AS u WHERE u.UpVotes + u.DownVotes > 10000000; 

DECLARE @ThisWillHappenWithStoredProcedureParametersToo NVARCHAR(40) = N'Eggs McLaren';
SELECT * 
FROM dbo.Users AS u 
WHERE u.DisplayName LIKE @ThisWillHappenWithStoredProcedureParametersToo OR @ThisWillHappenWithStoredProcedureParametersToo IS NULL;

None of these queries will register missing index requests. For more information on these, check out the following links:

You Already Have An Okay Index

Take this index:

CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);

It looks okay for this query:

SELECT p.OwnerUserId, p.Score 
FROM dbo.Posts AS p 
WHERE p.CreationDate >= '20070101' 
AND p.CreationDate < '20181231' 
AND p.Score >= 25000 
AND 1 = (SELECT 1) 
ORDER BY p.Score DESC;

The plan is a simple Seek…

But because the leading key column is for the less-selective predicate, we end up doing more work than we should:

Table ‘Posts’. Scan count 13, logical reads 136890

If we change the index key column order, we do a lot less work:

CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);

And significantly fewer reads:

Table ‘Posts’. Scan count 1, logical reads 5

SQL Server Is Creating Indexes For you

In certain cases, SQL Server will choose to create an index on the fly via an index spool. When an index spool is present, a missing index request won’t be. Surely adding the index yourself could be a good idea, but don’t count on SQL Server helping you figure that out.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

All The Performance Problems With Select * Queries In SQL Server

Posted on December 10, 2019May 16, 2022 by Erik Darling

This was originally posted by me as an answer here. I’m re-posting it locally for posterity.

The two reasons that I find the most compelling not to use SELECT * in SQL Server are

Memory Grants
Index usage

Memory Grants

When queries need to Sort, Hash, or go Parallel, they ask for memory for those operations. The size of the memory grant is based on the size of the data, both row and column wise.

String data especially has an impact on this, since the optimizer guesses half of the defined length as the ‘fullness’ of the column. So for a VARCHAR 100, it’s 50 bytes * the number of rows.

Using Stack Overflow as an example, if I run these queries against the Users table:

SELECT TOP 1000 
       u.DisplayName 
FROM dbo.Users AS u 
ORDER BY u.Reputation;


SELECT   TOP 1000
         u.DisplayName,
         u.Location
FROM     dbo.Users AS u
ORDER BY u.Reputation;

DisplayName is NVARCHAR 40, and Location is NVARCHAR 100.

Without an index on Reputation, SQL Server needs to sort the data on its own.

But the memory it nearly doubles.

DisplayName:

DisplayName, Location:

This gets much worse with SELECT *, asking for 8.2 GB of memory:

It does this to cope with the larger amount of data it needs to pass through the Sort operator, including the AboutMe column, which has a MAX length.

Index Usage

If I have this index on the Users table:

CREATE NONCLUSTERED INDEX ix_Users ON dbo.Users ( CreationDate ASC, Reputation ASC, Id ASC );

And I have this query, with a WHERE clause that matches the index, but doesn’t cover/include all the columns the query is selecting…

SELECT   u.*,
         p.Id AS PostId
FROM     dbo.Users AS u
JOIN     dbo.Posts AS p
    ON p.OwnerUserId = u.Id
WHERE    u.CreationDate > '20171001'
AND      u.Reputation > 100
AND      p.PostTypeId = 1
ORDER BY u.Id;

The optimizer may choose not to use the narrow index with a key lookup, in favor of just scanning the clustered index.

You would either have to create a very wide index, or experiment with rewrites to get the narrow index chosen, even though using the narrow index results in a much faster query.

CX:

SQL Server Execution Times: CPU time = 6374 ms, elapsed time = 4165 ms.

NC:

SQL Server Execution Times: CPU time = 1623 ms, elapsed time = 875 ms.

Thanks for reading!

Going Further

Why Functions Are Bad in SQL Server Where Clauses

Posted on December 5, 2019May 14, 2022 by Erik Darling

Raised Right

It seems like every time I check out a server, the query plans are a nightmare. Users are freaking out, and management’s coffee is more Irish than Colombian.

Many times, the issue is that people are using presentation layer functions for relational processes. The where clause, joins, group by, and order by parts of a query.

Think about built-in string and date functions, wrapped around columns, and the problems they can cause.

These are things you should actively be targeting in existing code, and fighting to keep out of new code.

Nooptional

When you’re trying to get rid of them, remember your better options

Cleaning data on input, or via triggers: Better than wrapping everything in RTRIM/LTRIM
Using computed columns: Better than relying on runtime calculations like DATEADD/DATEDIFF
Breaking queries up: Use UNION ALL to query for either outcome (think ISNULL)
Using indexed views: If you need to calculate things in columns across tables
Creating reporting tables: Sometimes it’s easier to denormalize a bit to make writing and indexing easier
Using #temp tables: If you have data that you need to persist a calculation in and the query to generate it is complicated

Note the things I’m not suggesting here:

CTEs: Don’t materialize anything
@table variables: Cause more problems than they solve
Views: Don’t materialize unless indexed
Functions: Just no, thanks

More Work

Yes, finding and fixing this stuff is more work for you. But it’s a whole lot less work for the optimizer, and your server, when you’re done.

If that’s the kind of thing you need help with, drop me a line.

Thanks for reading!

Going Further

Why DATEDIFF Returns Surprising Results In SQL Server

Posted on December 4, 2019May 16, 2022 by Erik Darling

All Day

If I sent you these three queries and asked you if they’d return 0 or 1, what would you guess?

SELECT DATEDIFF(YEAR, '2019-12-31', '2020-01-01');
SELECT DATEDIFF(MONTH, '2019-12-31', '2020-01-01');
SELECT DATEDIFF(DAY, '2019-12-31', '2020-01-01');

I’ll give you a second to think about it.

One.

There.

Good Job!

If you guessed that they’d all return 1, you were right. That’s because DATEDIFF isn’t always very smart about measuring time.

All it measures is that the unit of time you’re interested in has increased or decreased.

Even though January 1st is the day after December 31st, the year is different, so it says there’s a year difference between them. Same with the month query.

For day it makes total sense here, but if you wanted to see a 24 hour difference, it might not go so well.

Anyway, it may not be measuring what you think it’s measuring.

Precision

If you want more precise measurements, you’re gonna have to get on that datemath post I wrote recently.

For example, to replicate DATEDIFF for this query:

SELECT COUNT(*)
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON  p.Id = c.PostId
WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) > 1
AND   p.PostTypeId = 1
AND   c.Score > 0;

You’d have to do something like this:

SELECT COUNT(*)
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON  p.Id = c.PostId
WHERE p.CreationDate < DATEADD(YEAR, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, c.CreationDate), 0))
AND   c.CreationDate > DATEADD(YEAR,  1, DATEADD(YEAR, DATEDIFF(YEAR, 0, p.CreationDate), 0))
AND   p.PostTypeId = 1
AND   c.Score > 0

But all that tells you is that the creation dates have different years. It doesn’t tell you if those creation dates are fully a year apart, either by measuring 12 months or 365 days (I know, leap years. Can it, Smokey.).

If you want dates that are a year apart, you need to do something like this:

SELECT COUNT(*)
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON  p.Id = c.PostId
WHERE p.CreationDate < DATEADD(YEAR, -1, c.CreationDate)
AND   c.CreationDate > DATEADD(YEAR,  1, p.CreationDate)
AND   p.PostTypeId = 1
AND   c.Score > 0;

But to illustrate how inaccurate DATEDIFF can be, let’s look at the first few lines of this query:

SELECT DATEDIFF(YEAR, p.CreationDate, c.CreationDate) AS YearDiff,
       DATEDIFF(MONTH, p.CreationDate, c.CreationDate) AS MonthDiff,
	   DATEDIFF(DAY, p.CreationDate, c.CreationDate) AS DayDiff
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON  p.Id = c.PostId
WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) = 1
AND   p.PostTypeId = 1
AND   c.Score > 0
ORDER BY YearDiff, MonthDiff, DayDiff;

The beginning of the results look okay. But towards the end of the dates with “one year” difference, things look uh…

Admitting Is The First Step

If you need precise date measurements, you can’t always rely on DATEDIFF.

Especially for larger gaps, you can get some rather odd results depending on how you’re defining what qualifies for your requirements.

Thanks for reading!

Going Further

SQL Server Index Spools When You Have An Index

Posted on December 3, 2019May 14, 2022 by Erik Darling

PAUL WHITE IS WRONG

Ha ha. Just kidding. He’s never wrong about anything.

But he did write about Eager Index Spools recently, and the post ended with the following statement:

Eager index spools are often a sign that a useful permanent index is missing from the database schema.

I’d like to show you a case where you may see an Eager Index Spool even when you have the index being spooled.

Funboy & Funboy & Funboy

Let’s say we’ve got a query that, for better or worse, was written like so:

SELECT SUM(records)
FROM dbo.Posts AS p 
CROSS APPLY 
(
    SELECT COUNT(p2.Id)
    FROM   dbo.Posts AS p2
    WHERE  p2.LastEditDate >= '20121231'
	AND    p.Id = p2.Id

    UNION ALL

    SELECT COUNT(p2.Id)
    FROM   dbo.Posts AS p2
    WHERE  p2.LastEditDate IS NULL
	AND    p.Id = p2.Id
) x (records);

Right now, we’ve got this index:

CREATE INDEX to_null_or_not_to_null ON dbo.Posts(LastEditDate);

Which means we’ve effectively got an index on (LastEditDate, Id), because of how clustered index key columns are inherited by nonclustered indexes.

The APPLY section of the query plan looks like so:

Each spool runs for nearly 53 seconds. The entire plan runs for 1:52.

There have been times when I’ve seen index spools created to effectively re-order existing indexes.

Perhaps that’s the case here? Let’s add this index.

CREATE /*UNIQUE*/ INDEX that_is_a_question ON dbo.Posts(Id, LastEditDate);

I’ve got UNIQUE in there in case you’re playing along at home. It makes no difference to the outcome.

I’d expect you to ask about that. I have high expectations of you, dear reader. I love you.

PLEASE DON’T LEAVE ME.

Get Out

The new execution plan looks uh.

That’s frustrating, isn’t it? Why would you do that?

When I asked Paul why the optimizer was wrong (I understand that many of you confuse Paul with the optimizer. To wit, they’ve never been seen together.), he said something along the lines of:

The issue is that you have a unique clustered index that prevents the index matching logic from finding the better nonclustered index.

Well okay yeah lemme just go drop that clustered index or something.

Workarounds

There are several workarounds, like using FORCESEEK inside the APPLY logic.

Of course, the better method is just to write the query so there’s no need for the optimizer to join a table to itself a couple times.

SELECT SUM(x.records)
FROM (    
	SELECT COUNT(p.records)
    FROM 
    (
        SELECT 1 AS records
        FROM   dbo.Posts AS p2
        WHERE  p2.LastEditDate >= '20121231'
    ) AS p
    
    UNION ALL
    
    SELECT COUNT(p.records)
    FROM 
    (
        SELECT 1 AS records
        FROM   dbo.Posts AS p2
        WHERE  p2.LastEditDate IS NULL
    ) AS p
) AS x (records);

Which’ll finish in about 1.5 seconds.

But hey, nifty demo.

Thanks for reading!

Going Further

The Right Way To Compare Date Columns In Two Different SQL Server Tables

Posted on November 28, 2019May 16, 2022 by Erik Darling

A Certain Ratio

I have a lot to say about this demo on SQL Server 2019, which I’ll get to in a future post.

For now, I’m going to concentrate on ways to make this situation suck a little less for you wherever you are.

Let’s talk.

Pinky Blue

Let’s get a couple indexes going:

CREATE INDEX something ON dbo.Posts(PostTypeId, Id, CreationDate);
CREATE INDEX stuffy ON dbo.Comments(Score, PostId, CreationDate);

And look at a maybe kinda sorta stupid query.

SELECT COUNT(*)
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON  p.Id = c.PostId
WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) > 1
AND p.PostTypeId = 1
AND c.Score > 0;

We wanna find questions where a comment was left a year after they were posted, and the comment was upvoted.

BIG DATA!

What We Know

From yesterday’s post, we know that even if we put our date columns first in the index, we wouldn’t have anything to seek to.

Unlike yesterday’s post, these columns are in two different tables. We can’t make a good computed column to calculate that.

The indexes that I created help us focus on the SARGable parts of the where clause and the join conditions.

That query takes about 2 seconds.

Switch Hitter

You might be tempted to try something like this, but it won’t turn out much better unless you change your indexes.

SELECT DATEDIFF(YEAR, p.CreationDate, c.CreationDate) AS Diffo
INTO #t
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON  p.Id = c.PostId
WHERE p.CreationDate < DATEADD(YEAR, -1, c.CreationDate)
AND   c.CreationDate > DATEADD(YEAR,  1, p.CreationDate)
AND p.PostTypeId = 1
AND c.Score > 0

SELECT COUNT(*)
FROM #t AS t
WHERE t.Diffo > 1

DROP TABLE #t;

Moving CreationDate to the second column helps the first query quite a bit.

CREATE INDEX something_alt ON dbo.Posts(PostTypeId, CreationDate, Id);
CREATE INDEX stuffy_alt ON dbo.Comments(Score, CreationDate, PostId);

Opternatives

You could try an indexed view here, too.

CREATE VIEW dbo.Sneaky
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) AS records
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON  p.Id = c.PostId
WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) > 1
AND p.PostTypeId = 1
AND c.Score > 0
GO 

CREATE UNIQUE CLUSTERED INDEX whatever ON Sneaky(records);

But, you know. That might be overkill.

Depending.

Thanks for reading!

Going Further

The Right Way To Compare Date Columns In SQL Server Queries

Posted on November 27, 2019May 16, 2022 by Erik Darling

Tell It To The Judge

A common dilemma is when you have two date columns, and you need to judge the gap between them for something.

For instance, say you have a table of orders and you want to figure out how long on average it takes an ordered item to ship, or a shipped item to be delivered.

You’re not left with many good ways to write the query to take advantage of indexes.

Let’s have a look-see.

Iron Mask

We’re gonna skip the “aw shucks, this stinks without an index” part.

CREATE INDEX gloom ON dbo.Posts(CreationDate, LastActivityDate);

SELECT COUNT(*) AS records
FROM dbo.Posts AS p
WHERE DATEDIFF(YEAR, p.CreationDate, p.LastActivityDate) > 9;

Because it still stinks with an index. Check it out.

Though we have a predicate, and an index on both columns, we don’t have anything to seek to.

Why? Because our predicate isn’t on anything that the index is keeping track of.

Indexes don’t care how many years, months, days, hours, minutes, seconds, milliseconds, or microseconds difference there are between these two columns.

That’d be a really cool kind of index to have for sure, but insert a shrug that fills your screen the way dark matter fills the universe here.

All we got is workarounds.

Another For Instance

We can use a computed column:

ALTER TABLE dbo.Posts 
    ADD despair AS DATEDIFF(YEAR, CreationDate, LastActivityDate);

CREATE INDEX sadness ON dbo.Posts(despair);

The result is something we can seek to.

Which is probably the type of plan that you’d prefer.

Thanks for reading!

Going Further

How To Do Date Math In Your Where Clause To Avoid SQL Server Performance Problems

Posted on November 26, 2019May 16, 2022 by Erik Darling

Pattern Baldness

I see this pattern quite a bit in stored procedures, where the front end accepts an integer which gets passed to a query.

That integer is used to specify some time period — days, months, whatever — and the procedure then has to search some date column for the relevant values.

Here’s a simplified example using plain ol’ queries.

Still Alive

Here are my queries. The recompile hints are there to edify people who are hip to local variable problems.

DECLARE @months_back INT = 1;

SELECT COUNT(*) AS records
FROM dbo.Posts AS p
WHERE DATEADD(MONTH, @months_back * -1, p.CreationDate) <= '20100101' --Usually GETDATE() is here
OPTION(RECOMPILE);
GO 

DECLARE @months_back INT = 1;

SELECT COUNT(*) AS records
FROM dbo.Posts AS p
WHERE p.CreationDate <= DATEADD(MONTH, @months_back, '20100101') --Usually GETDATE() is here
OPTION(RECOMPILE);
GO

The problem with the first query is that the function is applied to the column, rather than to the variable.

If we look at the plans for these, the optimizer only thinks one of them is special enough for an index request.

Sure, there’s also a ~200ms difference between the two, which is pretty repeatable.

But that’s not the point — where things change quite a bit more is when we have a useful index. Those two queries above are just using the clustered index, which is on a column unrelated to our where clause.

CREATE INDEX etadnoitaerc ON dbo.Posts(CreationDate);

Side by side:

The bad query uses >10x more CPU
Still runs for >3x as long
Scans the entire index
Reads 10x more rows
Has to go parallel to remain competitive

At MAXDOP 1, it runs for just about 2.2 seconds on a single thread. Bad news.

Qual

This is one example of misplacing logic, and why it can be bad for performance.

All these demos were run on SQL Server 2019, which unfortunately didn’t save us any trouble.

In the next post, I’m going to look at another way I see people make their own doom.

Thanks for reading!

Going Further

No Column Store Indexes With In-Memory Tempdb For SQL Server? No Problem!

Posted on November 19, 2019May 16, 2022 by Erik Darling

Old Habits

Let’s say you’re on SQL Server 2019. No, seriously. It’s been out for a couple weeks now.

You could be.

I say that you could be because you’re the kind of brave person who tries new things and experiments with their ~~body~~ server.

You may even do crazy things like this.

Stone Cold

CREATE TABLE #t ( id INT, INDEX c CLUSTERED COLUMNSTORE );

SELECT    COUNT_BIG(*) AS records
FROM      dbo.Users AS u
JOIN      dbo.Posts AS p
    ON u.Id = p.OwnerUserId
JOIN      dbo.Comments AS c
    ON u.Id = c.UserId
LEFT JOIN #t AS t ON 1 = 0;

Woah ho ho. What happened there? A #temp table with a clustered column store index on it left joined on 1 = 0?

Yes. People do this.

People do this because it’s getting some batch mode operations “for free”, which have the nasty habit of making big reporting queries run a lot faster.

Yonder Problem

When you enable 2019’s new in memory tempdb, which can really help with stuff tempdb needs help with, you may find yourself hitting errors.

Msg 11442, Level 16, State 1, Line 14
Columnstore index creation is not support in tempdb when memory-optimized metadata mode is enabled.
Msg 1750, Level 16, State 1, Line 14
Could not create constraint or index. See previous errors.

The good news is that this works with *real* tables, too.

CREATE TABLE dbo.t ( id INT, INDEX c CLUSTERED COLUMNSTORE );

SELECT    COUNT_BIG(*) AS records
FROM      dbo.Users AS u
JOIN      dbo.Posts AS p
    ON u.Id = p.OwnerUserId
JOIN      dbo.Comments AS c
    ON u.Id = c.UserId
LEFT JOIN dbo.t AS t ON 1 = 0;

And you can get plans with all sorts of Batchy goodness in them.

Yeah, you’re gonna have to change some code, but don’t worry.

You’re the kind of person who enjoys that.

Right?

Thanks for reading!

Going Further

When SELECT * Doesn’t Matter In SQL Server Queries

Posted on November 18, 2019August 6, 2023 by Erik Darling

Pavlovian

There’s a strange response to some things in the SQL Server community that borders on religious fervor. I once worked with someone who chastised me for having SELECT * in some places in the Blitz scripts. It was odd and awkward.

Odd because this person was the most Senior DBA in the company, and awkward because they didn’t believe me that it didn’t matter in some cases.

People care about SELECT * for many valid reasons, but context is everything.

One For The Money

The first place it doesn’t matter is EXISTS. Take this index and this query:

CREATE INDEX specatular_blob ON dbo.Posts(PostTypeId, OwnerUserId);

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  EXISTS ( SELECT * 
                FROM dbo.Posts AS p 
				WHERE p.OwnerUserId = u.Id 
				AND p.PostTypeId = 2 );

The relevant part of the query plan looks like this:

We do a seek into the index we created on the two columns in our WHERE clause. We didn’t have to go back to the clustered index for everything else in the table.

That’s easy enough to prove if we only run the subquery — we have to change it a little bit, but the plan tells us what we need.

SELECT *
FROM   dbo.Posts AS p
WHERE  p.OwnerUserId = 22656
AND    p.PostTypeId = 2;

This time we do need the clustered index:

You can even change it to something that would normally throw an error:

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  EXISTS ( SELECT 1/0 
                FROM dbo.Posts AS p 
				WHERE p.OwnerUserId = u.Id 
				AND p.PostTypeId = 2 );

Two For Completeness

Another example is in derived tables, joins, and apply.

Take these two queries. The first one only selects columns in our nonclustered index (same as above).

The second one actually does a SELECT *.

/*selective*/
SELECT     u.Id,
           u.DisplayName,
		   ca.OwnerUserId, --I am only selecting columns in our index
		   ca.PostTypeId,
		   ca.Id
FROM       dbo.Users AS u
CROSS APPLY( SELECT TOP (1) * --I am select *
             FROM dbo.Posts AS p 
			 WHERE p.OwnerUserId = u.Id 
			 AND p.PostTypeId = 2 
			 ORDER BY p.OwnerUserId DESC, p.Id DESC) AS ca
WHERE U.Reputation >= 100000;

/*less so*/
SELECT     u.Id,
           u.DisplayName,
		   ca.* --I am select *
FROM       dbo.Users AS u
CROSS APPLY( SELECT TOP (1) * --I am select *
             FROM dbo.Posts AS p 
			 WHERE p.OwnerUserId = u.Id 
			 AND p.PostTypeId = 2 
			 ORDER BY p.OwnerUserId DESC, p.Id DESC) AS ca
WHERE U.Reputation >= 100000;

The first query only touches our narrow nonclustered index:

The second query does a key lookup, because we really do select everything.

Trash Pile

I know, you’ve been well-conditioned to freak out about certain things. I’m here to help.

Not every SELECT * needs to be served a stake through the heart and beheading.

Thanks for reading!