3

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

0
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.