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 table 2 5391 27099 Wide table result 7203 34354 Subqueried narrow result table 3 5843 10321 Result from the theoretical data below: CPU Time (ms) Elapsed Time (ms) Wide table result 109 461 Subqueried narrow result table 1 32 441 Wide table result 78 441 Subqueried narrow result table 2 110 453 Wide table result 62 441 Subqueried narrow result table 3 63 458 Here is how the theoretical data was generated. /* create a dummy source table */ select * into tblSourceTable from(select top 100000 NewID()A, NewID()B from sys.columns)x Generate theoretical data: DECLARE @test2 TABLE ( [ID] [bigint] NULL, [A] [uniqueidentifier] NULL, [B] [uniqueidentifier] NULL, [C] [uniqueidentifier] NULL, [D] [uniqueidentifier] NULL, [E] [uniqueidentifier] NULL, [F] [uniqueidentifier] NULL, [G] [uniqueidentifier] NULL, [H] [uniqueidentifier] NULL, [I] [uniqueidentifier] NULL, [J] [uniqueidentifier] NULL, [K] [uniqueidentifier] NULL, [L] [uniqueidentifier] NULL, [M] [uniqueidentifier] NULL, [N] [uniqueidentifier] NULL, [O] [uniqueidentifier] NULL, [P] [uniqueidentifier] NULL, [Q] [uniqueidentifier] NULL, [R] [uniqueidentifier] NULL, [S] [uniqueidentifier] NULL, [T] [uniqueidentifier] NULL, [U] [uniqueidentifier] NULL, [V] [uniqueidentifier] NULL, [W] [uniqueidentifier] NULL, [X] [uniqueidentifier] NULL, [Y] [uniqueidentifier] NULL, [Z] [uniqueidentifier] NULL, [AA] [uniqueidentifier] NULL, [AB] [uniqueidentifier] NULL, [AC] [uniqueidentifier] NULL, [AD] [uniqueidentifier] NULL, [AE] [uniqueidentifier] NULL, [AF] [uniqueidentifier] NULL, [AG] [uniqueidentifier] NULL, [AH] [uniqueidentifier] NULL, [AI] [uniqueidentifier] NULL, [AJ] [uniqueidentifier] NULL, [AK] [uniqueidentifier] NULL, [AL] [uniqueidentifier] NULL, [AM] [uniqueidentifier] NULL, [AN] [uniqueidentifier] NULL, [AO] [uniqueidentifier] NULL, [AP] [uniqueidentifier] NULL, [AQ] [uniqueidentifier] NULL, [AR] [uniqueidentifier] NULL ) Make a dummy data set insert into @test2 select Top 50000 ID=ROW_NUMBER()over(order by C.A), NewID() AS A,NewID() AS B,NewID() AS C,NewID() AS D,NewID() AS E,NewID() AS F, NewID() AS G,NewID() AS H,NewID() AS I,NewID() AS J,NewID() AS K,NewID() AS L, NewID() AS M,NewID() AS N,NewID() AS O,NewID() AS P,NewID() AS Q,NewID() AS R, NewID() as S,NewID() AS T,NewID() AS U,NewID() AS V,NewID() AS W,NewID() AS X, NewID() AS Y,NewID() AS Z,NewID() AS AA,NewID() AS AB,NewID() AS AC,NewID() AS AD, NewID() AS AE,NewID() AS AF,NewID() AS AG,NewID() AS AH,NewID() AS AI,NewID() AS AJ, NewID() AS AK,NewID() AS AL,NewID() AS AM,NewID() AS AN,NewID() AS AO,NewID() AS AP, NewID() AS AQ,NewID() AS AR FROM test C cross apply test D cross apply test E cross apply test F cross apply test G cross apply test H cross apply test I cross apply test J cross apply test K cross apply test L cross apply test M cross apply test N cross apply test O cross apply test P cross apply test Q cross apply test R cross apply test S cross apply test T cross apply test U cross apply test V cross apply test W cross apply test X cross apply test Y cross apply test Z Create the tables: Select * into [FirstWideTable] FROM @test2 Select * into [SecondWideTable] FROM @test2 Select * into [ThirdWideTable] FROM @test2 CREATE UNIQUE CLUSTERED INDEX [PK_TEST_A_ID] ON [dbo].[FirstWideTable]([ID] ASC) CREATE UNIQUE CLUSTERED INDEX [PK_TEST_B_ID] ON [dbo].[SecondWideTable]([ID] ASC) CREATE UNIQUE CLUSTERED INDEX [PK_TEST_C_ID] ON [dbo].[ThirdWideTable]([ID] ASC) Run the query a few times allowing statistics data to be created Select a.ID, a.A, a.AB, b.A, b.B, b.C, b.D, c.A, C.B, c.D, c.AA From [FirstWideTable] a LEFT Join [SecondWideTable] b on a.ID=b.ID LEFT Join [ThirdWideTable] c on b.ID=c.ID WHERE a.ID BETWEEN 1 and 50000; Select a.ID, a.A, a.AB, b.A, b.B, b.C, b.D, c.A, C.B, c.D, c.AA From [FirstWideTable] a LEFT Join [SecondWideTable] b on a.ID=b.ID LEFT Join [ThirdWideTable] c on b.ID=c.ID WHERE a.ID BETWEEN 1 and 50000; Select a.ID, a.A, a.AB, b.A, b.B, b.C, b.D, c.A, C.B, c.D, c.AA From [FirstWideTable] a LEFT Join [SecondWideTable] b on a.ID=b.ID LEFT Join [ThirdWideTable] c on b.ID=c.ID WHERE a.ID BETWEEN 1 and 50000; Create the statistics update statistics [FirstWideTable] update statistics [SecondWideTable] update statistics [ThirdWideTable] Grab the results set statistics io,time on Select a.ID, a.A, a.AB, b.A, b.B, b.C, b.D, c.A, C.B, c.D, c.AA From (Select ID, A, AB from [FirstWideTable]) a LEFT Join(Select ID, A, B, C, D from[SecondWideTable]) b on a.ID=b.ID LEFT Join(Select ID, A, B, D, AA from[ThirdWideTable]) c on b.ID=c.ID WHERE a.ID BETWEEN 1 and 50000; set statistics io,time off set statistics io,time on Select a.ID, a.A, a.AB, b.A, b.B, b.C, b.D, c.A, C.B, c.D, c.AA From [FirstWideTable] a LEFT Join [SecondWideTable] b on a.ID=b.ID LEFT Join [ThirdWideTable] c on b.ID=c.ID WHERE a.ID BETWEEN 1 and 50000; set statistics time io,off Showplan: https://www.brentozar.com/pastetheplan/?id=H15IMMYaS