0

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:

enter image description here

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

Any suggestion would be appreciated.

11
  • 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 at 15:35
  • 5
    And sending the results to the client is probably the most expensive part. Sep 13 at 15:48
  • 3
    Obviously, if only certain columns are needed, selecting those columns would almost always be better than 'select *' Sep 13 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 at 15:56
  • 2
    Not sure plan cache has any relevance here. Buffer pool might, though. What is the size of the clustered index? What is SQL Server's max server memory? It might take 17 seconds both the first time and subsequent times because the whole table doesn't fit in memory. It also might just be that trying to render 1 million rows in Management Studio for some reason is guaranteed to be slow. What happens if you do DECLARE @c char(1); SELECT @c = CONVERT(char(1), <some column> FROM dbo.MyTable;? This allows SSMS to just run the query without render. Could also play with the "discard results" option. Sep 13 at 16:01
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...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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