Time costly Last operation in Query Plan

Time costly Last operation in Query Plan

Post by Steve Champea » Tue, 04 Nov 1997 04:00:00



Hello,

I am new to SQL Server and am trying to optimize some multi-table
queries. My question involves the very time costly step the ShowPlan
indicates is being done at the end of my query.

My query joins 6 tables together, does some GROUP BY action, and dumps
the results (1148 rows) in Worktable 4. The ShowPlan shows the following
final step for my query:

STEP 5
The type of query is SELECT
FROM TABLE
Worktable 4
Nested iteration
Table Scan

The table hit stats show the following very large numbers for Worktable
4:

Table: Worktable  
        scan count 109715,
        logical reads: 577378,
        physical reads: 69056,
        read ahead reads: 0

This query takes 17 minutes (one of the tables has 3.6 million rows),
and judging by the large number of physical reads being done to
Worktable 4, a good part of this time may be spent doing this last step.
What exactly is this step doing? Is this what it does to read each row
from Worktable 4 and send it to my query window in Enterprise Manager?

Also, the table stats are given for what is called disk READS, but how
many of these logical and physical reads are actually occurring in the
WRITING of rows into Worktable 4?

Thanks a lot for any help,

Steve C.

 
 
 

Time costly Last operation in Query Plan

Post by Stuart Ming » Wed, 05 Nov 1997 04:00:00


On Mon, 03 Nov 1997 15:38:49 -0800, Steve Champeau


>This query takes 17 minutes (one of the tables has 3.6 million rows),
>and judging by the large number of physical reads being done to
>Worktable 4, a good part of this time may be spent doing this last step.
>What exactly is this step doing? Is this what it does to read each row
>from Worktable 4 and send it to my query window in Enterprise Manager?

... basically, SQL Server has created a temporary table. If you have
GROUP BY's or ORDER BY's on a table the server has to create a temp.
table into which it can insert the data in the order you want. It then
reads this table with a table scan to return your result set. If you
can have a clustered index on the big table that effectively pre-sorts
the data

Quote:

>Also, the table stats are given for what is called disk READS, but how
>many of these logical and physical reads are actually occurring in the
>WRITING of rows into Worktable 4?

this only shows the reads from the table, both from memory and from
disk.

... I'm sure someone will point out my mistakes if I've got anything
wrong!

Stuart



 
 
 

Time costly Last operation in Query Plan

Post by Steve Champea » Thu, 06 Nov 1997 04:00:00


Stuart-

Thanks a lot for your response. In your answer you stated "[SQL Server]
then reads this table with a table scan to return your result set."

Just to make sure I've go it: any query has to include a final step in
which the results are read from the final table and sent to the user.
For example, a query I'm working on now does a bunch of joins and group
by's and dumps the results in Worktable 3. The final step in the
showplan for this query is the final step you describe:

FROM TABLE
Worktable 3
Nested iteration
Table Scan

The stats info includes the following:

Table: Worktable  scan count 1,  logical reads: 29257,  physical reads:
0,  read ahead reads: 0

So the bottom line is that one will always have to deal with the
overhead of scanning/reading the final temporary table once in order to
return the resulting rows to the user.

Does this sound right?

Thanks,

Steve C.


> On Mon, 03 Nov 1997 15:38:49 -0800, Steve Champeau

> >This query takes 17 minutes (one of the tables has 3.6 million rows),
> >and judging by the large number of physical reads being done to
> >Worktable 4, a good part of this time may be spent doing this last step.
> >What exactly is this step doing? Is this what it does to read each row
> >from Worktable 4 and send it to my query window in Enterprise Manager?

> ... basically, SQL Server has created a temporary table. If you have
> GROUP BY's or ORDER BY's on a table the server has to create a temp.
> table into which it can insert the data in the order you want. It then
> reads this table with a table scan to return your result set. If you
> can have a clustered index on the big table that effectively pre-sorts
> the data

> >Also, the table stats are given for what is called disk READS, but how
> >many of these logical and physical reads are actually occurring in the
> >WRITING of rows into Worktable 4?

> this only shows the reads from the table, both from memory and from
> disk.

> ... I'm sure someone will point out my mistakes if I've got anything
> wrong!

> Stuart



 
 
 

Time costly Last operation in Query Plan

Post by Erland Sommarsk » Sun, 09 Nov 1997 04:00:00


[Reposted due to problems at my provider.]


Quote:>My query joins 6 tables together, does some GROUP BY action, and dumps
>the results (1148 rows) in Worktable 4. The ShowPlan shows the following
>final step for my query:

>STEP 5
>The type of query is SELECT
>FROM TABLE
>Worktable 4
>Nested iteration
>Table Scan

>The table hit stats show the following very large numbers for Worktable
>4:

>Table: Worktable  
>        scan count 109715,
>        logical reads: 577378,
>        physical reads: 69056,
>        read ahead reads: 0

>This query takes 17 minutes (one of the tables has 3.6 million rows),
>and judging by the large number of physical reads being done to
>Worktable 4, a good part of this time may be spent doing this last step.
>What exactly is this step doing? Is this what it does to read each row
>from Worktable 4 and send it to my query window in Enterprise Manager?

It would have been easier to answer your questions if you had given the
entire query and the complete output from SET STATISTICS IO.

But anyway, as pointed out by another poster, SQL Server often creates
temporary work tables, and they usually do not have index, so you
get a table scan. Now 1148 rows for a table scan is not a disaster
if you do it once, but your worktable is being read no fewer than
109715 times, and that's an awful lot.

Sometimes however, SQL Server will create a worktable solely for the
reason to create a clustered index. In this case you will see WITH
REFORMATTING somewhere in the showplan output. As this is a fairly
expensive operation, this is an alert that you might want to have
a look at the query or the indexes.

Quote:>Also, the table stats are given for what is called disk READS, but how
>many of these logical and physical reads are actually occurring in the
>WRITING of rows into Worktable 4?

I'm a little uncertain, but I think it should be only one. Your table
is scanned so many times, because you get so many hits in another
able which is ahead of the worktable in the join order. (I have a
feeling that the showplan step does not match the statistics.)

[Posted and mailed.]

--

 
 
 

1. Last Step in QueryPlan very costly

Hello,

I am new to SQL Server and am trying to optimize some multi-table
queries. My question involves the very time costly step the ShowPlan
indicates is being done at the end of my query.

My query joins 6 tables together, does some GROUP BY action, and dumps
the results (1148 rows) in Worktable 4. The ShowPlan shows the following
final step for my query:

STEP 5
The type of query is SELECT
FROM TABLE
Worktable 4
Nested iteration
Table Scan

The table hit stats show the following very large numbers for Worktable
4:

Table: Worktable  
        scan count 109715,
        logical reads: 577378,
        physical reads: 69056,
        read ahead reads: 0

This query takes 17 minutes (one of the tables has 3.6 million rows),
and judging by the large number of physical reads being done to
Worktable 4, a good part of this time may be spent doing this last step.
What exactly is this step doing? Is this what it does to read each row
from Worktable 4 and send it to my query window in Enterprise Manager?

Also, the table stats are given for what is called disk READS, but how
many of these logical and physical reads are actually occurring in the
WRITING of rows into Worktable 4?

Thanks a lot for any help,

Steve C.

2. DAte conversion

3. Changing Query Plans - Slow one time/Fast Another Time

4. SQL question - Sorting by release/version numbers.

5. Query Planning time increased 3 times on 7.1 compared to

6. Paradox 4.0 application solution

7. Run time error 3073 -Operation must use an updatable query

8. US-NY-DATABASE ADMINISTRATOR

9. Same query different time (and execution plan) between TSQL and SProc

10. knowing last modification / last access times of tables..

11. Why is Stored Proc plan slower than query plan

12. Execution plan,Query plan?