1

This is driving me bananas.

According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and in the second it takes 35 SECONDS.

Here's the details.

I created test table named TEST with only two columns - LastName and FirstName. It's a heap with no indexes of any kind. It has exactly 1 Million rows and no nulls.

(I run DBCC DROPCLEANBUFFERS before each query)

When I execute "SELECT FirstName FROM Test WHERE LastName = 'Smith'" the execution plan shows a Table Scan and Statistics IO shows the total logical reads as 25,923. The query takes less than 1 second to execute.

Then, I execute "SELECT * FROM Test" and again the execution plan shows a Table Scan and Statistics IO shows logical reads of 25,923. BUT, this query takes 35 seconds to complete.

I've tried reversing the order of the queries, restarting the whole server in between queries, etc. But, the results are always the same.

Unless those execution plans or statistics are lying in one of the cases, it makes no sense that there should be a 35x times difference.

How can I figure out what accounts for this difference?

Improve this question
2
  • 1
    Please select @@version and edit the question to include the output.
    – Michael Green
    Commented Jun 27, 2020 at 2:01
  • 1
    you need to post the execution plans and STATISTICS IO output for both queries
    – SE1986
    Commented Jul 1, 2020 at 16:17

2 Answers 2

Reset to default
4

Am I missing something obvious here? :-)

In one case you return all rows. In the other case, you have a WHERE clause to limit the rows to be returned. Even with the same plan, there would be a difference in the number of rows to be returned to the client app. That difference can certainly explain the difference you see.

In the actual execution plan, you can see wait stats (assuming a pretty recent version of SQL Server). This is in the outermost SELECT operator. If you have bunch and bunch of ASYNC_NETWORK_IO, then SQL Server is waiting for the client to consume the result.

You can also see wait stats at the session level level using sys.dm_exec_session_wait_stats.

Improve this answer
4
  • 1
    Tibor, Thanks for responding. I've seen your articles over the years. I realize of course that the filtered query result is only returning a small fraction of the full table contents that the SELECT * query returns, but the point I'm emphasizing is that both queries execute a table scan and I infer (perhaps wrongly?) that the process of the table scan is 99% of the work. It seems that you may be saying that writing the result set to the SSMS client could be 99% of the work and the table scan of the 1 Million rows takes less than 1 second?
    – F.S.
    Commented Jun 30, 2020 at 23:37
  • 1
    Note: The SSMS client here is running on the SQL Server - not a seperate client machine. I used SET STATISTICS TIME ON and observed roughly the same result as I observed just measuring the query via the clock in SSMS. The SELECT * query took about 34 seconds and the filtered query took half a second. It's hard to me to belive that the table scan of 1 million rows took only half a second yet it took 34 seconds to write the results to the locally based SSM client. But, if it is true, how can I verify?
    – F.S.
    Commented Jun 30, 2020 at 23:37
  • 1
    Turn off the execution plan and the stats io and time. Use Extended Events to capture each query. The measure there is just the performance on the server. No database movement to the client is included in that measure. That'll show you where the bottleneck lies. 100% positive that @TiborKaraszi has it right.
    – Grant Fritchey
    Commented Jul 1, 2020 at 11:32
  • 1
    Use the XE suggestion from Grant and/or look at wait stats as I suggested in my answer above. As for wait stats, I suggest looking at both session and plan level. If you can produce a repro, we can check it out as well, see if we can or cannot repro it. Without details/repro, we're just making qualified guesses. :-)
    – Tibor Karaszi
    Commented Jul 2, 2020 at 6:50
3

Logical reads is only an indicator of how much data SQL Server reads from disk (or the buffer pool). There are a bunch of other factors that can contribute to a long-running query.

The most obvious suspect is how much time it takes to move the data from your server to Management Studio and/or the time it takes to render the data in a table. To eliminate those two, try inserting the results in a new temp table:

SELECT a, b INTO #new_table FROM #temp_table;

You can also set

SET STATISTICS TIME ON;

to see the amount of CPU and Elapsed times respectively.

Improve this answer

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