Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)

Assume that a query in question has a very wide set of columns. The query looks like the following:

set statistics io,time on;

Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5
From FirstWideTable a
   Join SecondWideTable b on a.Col6=b.Col7
   Join ThirdWideTable c on b.Col8=c.Col9
WHERE a.Col10=@SomeVariable;

set statistics io,time off;

The above query uses 10 columns of 100 or so (more "selective" queries). SQL Server created statistics on these tables and tables contain effective indexes adressing the predicate (Col10) and while not showing here, assume the PK's are created on the three tables (see demo below):

CREATE NONCLUSTERED INDEX NC__a_Col1_Col2_Col6 
ON FirstWideTable(Col1 ASC,Col2 ASC, Col3 ASC) 
INCLUDE (Col10) 
ON PRIMARY;

and similar indexes are available for the Second and Third Wide Tables.

If the query is written as a subquery with only the 10 columns required:

set statistics io,time on;

Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5
From (SELECT a.Col1, a.Col2,a.Col6,a.Col10 FROM FirstWideTable) a
    Join (SELECT b.Col4,b.Col5, b.Col7 FROM SecondWideTable) b on a.Col6=b.Col7
    Join (c.Col5, c.Col9 FROM ThirdWideTable) c on b.Col8=c.Col9
WHERE a.Col10=@SomeVariable;

set statistics io,time off;

resulst improve. Elapsed time on the wide tables with statistics show terrible (30 second) elapased time. Each time the query (without statistics running against it) has the actual columns listed in a subquery, the elapsed time drops considerably.

QUERY RESULTS - Wide table has statistics - Wide table is the baseline.
subqueries results do not have statistics against them, the timing was taken after adding in the subquery that examines only the columns necessary rather than the wide table.

```
CPU Time (ms) Elapsed Time (ms)
Wide table result 7265 35459
Subqueried narrow result table 1 5125 31271
Wide table result 6765 33446
Subqueried narrow result

CPU time can vary significantly between plans without you necessarily seeing an improvement in elapsed time taken, especially if statistics are out of date. I wrote a short answer here that discusses the relationship between CPU time and elapsed time.

Try manually updating the statistics using the UPDATE STATISTICS T-SQL command for the tables affected.

SQL Server statistics provide invaluable details to the query engine. Those details are critical for optimal performance. It's typically a good idea to update statistics nightly or weekly, and to allow SQL Server to create statistics as needed. See my blog for a script that will update stats - you could run this nightly via a SQL Server Agent Job.

StackExchange Database Administrators Q#254891, answer score: 2