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.


The execution plan gives the following info:

enter image description here

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

Any suggestion would be appreciated.

  • 5
    Nothing will make that query more efficient - it selects every column of every row. A scan of the table is the best it can do.
    – SMor
    Sep 13, 2021 at 15:35
  • 5
    And sending the results to the client is probably the most expensive part. Sep 13, 2021 at 15:48
  • 3
    Obviously, if only certain columns are needed, selecting those columns would almost always be better than 'select *' Sep 13, 2021 at 15:49
  • 1
    Like @SMor said, you can't do much on the query plan for such a simple query. Did you check wait statistics while the query is running? Data pages must be read... You could also look at internal fragmentation (free space in pages, is there a fill factor configured for your clustered index?). If you pages are half empty, SQL Server must go through much more data pages it would need if there was more records packed per page. Sep 13, 2021 at 15:56
  • 1
    That is, most of the time is spent on the client side, your computer, rendering the results in SSMS.
    – J.D.
    Sep 14, 2021 at 2:37

1 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...

FROM   sys.dm_exec_session_wait_stats 
WHERE  session_id = @@spid;

FROM   MyTable;

 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
       JOIN #WAITS 
          ON T.wait_type = #WAITS.wait_type
WHERE  T.wait_time_ms > 0


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...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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