SELECT * Cost: Why Wide Result Sets Slow Exports

SELECT * cost is the cost of every column you never asked for. The query may start fast, yet the export crawls, because big columns still have to be read, packed and shipped to a client. Name the columns you need and the work shrinks.

The export that crawls

Here is a story you may know. The nightly export used to take minutes. Now it takes much longer, and everyone blames the query. Someone adds an index. Nothing changes. Then somebody notices that the table got a Notes column last quarter, and the export says SELECT *.

The export file only needs an Id and a Code. But SELECT * brings Notes along for every row. Let me build a small table to see it. It has an Id, a short Code, and a Notes column filled with 20,000 characters per row. It also has an index on Code. It uses a temp table, so nothing stays behind.

DROP TABLE IF EXISTS #WideExport;

CREATE TABLE #WideExport (Id int PRIMARY KEY, Code varchar(20), Notes varchar(max));

INSERT #WideExport (Id, Code, Notes)
SELECT TOP (20)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 2 = 1 THEN 'A' ELSE 'B' END,
       REPLICATE(CAST('x' AS varchar(max)), 20000)
FROM sys.all_objects;

CREATE INDEX IX_WideExport_Code ON #WideExport (Code);

See where the bytes live

Look at the size of each column across all 20 rows. I use DATALENGTH, which counts bytes, so you do not have to flood the grid with a wall of x characters.

SELECT COUNT(*)                AS TotalRows,
       SUM(DATALENGTH(Notes))  AS NotesBytes,
       SUM(DATALENGTH(Code))   AS CodeBytes
FROM #WideExport;

Notes is 400,000 bytes. Code is 20 bytes. The export needs the second number and SELECT * pays for the first one. Same rows, same query, a very different amount of data on the wire.

Count the reads

Now let SQL Server tell you the reading cost. STATISTICS IO prints reads per query. To keep the grid quiet, I send the rows into temp tables that stand in for the client. The wide copy takes every column. The narrow copy takes only Id and Code.

DROP TABLE IF EXISTS #WideCopy;
DROP TABLE IF EXISTS #NarrowCopy;
CREATE TABLE #WideCopy   (Id int, Code varchar(20), Notes varchar(max));
CREATE TABLE #NarrowCopy (Id int, Code varchar(20));

SET STATISTICS IO ON;

INSERT #WideCopy   (Id, Code, Notes) SELECT * FROM #WideExport;
INSERT #NarrowCopy (Id, Code)        SELECT Id, Code FROM #WideExport;

SET STATISTICS IO OFF;

Read the two messages for #WideExport. In my run, the wide query shows 60 “lob logical reads”, because the big text lives on separate pages. The narrow query shows 0. Your exact counts may differ, so look at the shape, not the number. Even 20 rows leave a clear gap, and a table with millions of rows widens it.

Name the columns

The fix is dull and effective. Write the column list. It also protects you from the future. When someone adds a huge column next year, a named list does not quietly grow. SELECT * does.

SELECT Id, Code
FROM #WideExport
WHERE Code = 'A'
ORDER BY Id;

This returns ten rows. The narrow read above touched none of the big text pages, and this one asks for the same two columns.

Check the client side too

Sometimes the query is fine and the receiver is slow. A client that reads one row at a time, or writes a file slowly, makes SQL Server wait. That wait is called ASYNC_NETWORK_IO. This query shows it for your session. In the demo it returns nothing, because the receiver is a temp table. On a real slow export, you would see waits.

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@SPID
  AND wait_type = 'ASYNC_NETWORK_IO';

In SSMS, you can also turn on “Discard results after execution” in the query options. It reads all rows but skips drawing the grid, which is a fair way to time the server side. Now clean up.

DROP TABLE IF EXISTS #WideCopy;
DROP TABLE IF EXISTS #NarrowCopy;
DROP TABLE IF EXISTS #WideExport;

Before you tune the query again, ask what the export really needs.

An export is not slow because of the query alone, it is slow because of everything it carries.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.