How can I reduce Estimated I/O and subtree cost in SQL Server Execution plan?

Question

I have a table with more than 1 million rows. When I execute a simple SELECT query it takes about 17 seconds to retrieve the data.

SELECT * FROM MyTable

The execution plan gives the following info:

Furthermore, I have 5 non-clustered indexes and one clustered index.

Any suggestion would be appreciated.

Answer 1

You can see by using the view ;

SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = @@spid

...before and after the execution of your query, that the waits are globally ASYNC_NETWORK_IO due to the fact that the engine must send the rows of the table from the RAM to the client application. The RAM is being accessed in few nanosecond, while the applications will use hundreds of milisecond to captue the data and place in into the object... and when this object has a graphical representation, this time is higher...

SELECT * 
INTO   #WAITS
FROM   sys.dm_exec_session_wait_stats 
WHERE  session_id = @@spid;

SELECT * 
FROM   MyTable;

WITH T AS
(SELECT * 
 FROM   sys.dm_exec_session_wait_stats 
 WHERE  session_id = @@spid)
SELECT T.wait_type, T.wait_time_ms - #WAITS.wait_time_ms AS WAITS_MS
FROM   T 
       JOIN #WAITS 
          ON T.wait_type = #WAITS.wait_type
WHERE  T.wait_time_ms > 0
ORDER  BY WAITS_MS;

--> DROP TABLE #WAITS

To see the cost of the graphical representation of data in SSMS, you can restart this test without viewing the data (Menue : Query / Query Options / Results / Grid / Discard results after execution...)