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 
  • lets take a look at the plan: brentozar.com/pastetheplan . Also, perhaps your statistics are out of date. – James Dec 5 '19 at 13:23
  • 1
    @Jamie - it would be helpful if you included a minimal reproducible example for the affected tables, including any indexes that are presently defined. A minimal reproducible example will help since it would define the column types, etc., and allows us to easily reproduce your issue. – Max Vernon Dec 5 '19 at 13:40
  • there are some effective indexes the index from your question doesn't look more or less useful for the query from the question, I doubt it is even used. – Denis Rubashkin Dec 5 '19 at 14:05
  • I've added some information about the results - I used the table with statistics against it - the subqueried result does not have statistics - it is there to show that the results are better - I'm concluding, perhaps incorrectly - that wide tables with statistics have a greater CPU time as well as a greater Elapsed time. The results vary in the theoretical data - there line is much clearer in the actual result. The time it takes to run the query drops to less than half the time while the query without the subquery against fewer columns doesn't change much at all. – Jamie Dec 6 '19 at 17:08
  • Showplan: brentozar.com/pastetheplan/?id=H15IMMYaS – Jamie Dec 7 '19 at 11:52
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.

|improve this answer|||||
  • Max, thanks. It's difficult to explain why the result is what I see. CPU time is 35, 33 then 34 seconds on the wide tables that have their statistics in place. The query that specifies only the columns that are used reduces the run time from 31 to 27 and then 10 seconds. Why can't a query with statistics be as fast as one without. Shouldn't the includes on the indexes that mention the predicate fix that problem - given the indexes contain the columns in question? – Jamie Dec 7 '19 at 17:15
  • 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. – Max Vernon Dec 9 '19 at 19:33
  • Struggling a bit with your statisticsScience queries but I get the idea. Would it not be as effective to drop statistics in a dev database and rebuild from scratch, then compare with Production? One other item - I notice that the worst query in the db I'm tackling, is nearly all scans and no seeks in spite of many relevant indexes (perhaps there are too many foreign keys?). I expect to find a way around it... not sure what that answer is yet. I've reduced the logical reads from about a million to less than 25,000 and scans from around 100,000 to 50. But without a performance boost. – Jamie Dec 10 '19 at 20:02

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.