Sql-server – Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server

execution-planquery-performancesql server

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?

Best Answer

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.

Related Question