question

Pds-2061 avatar image
0 Votes"
Pds-2061 asked SeeyaXi-msft commented

Sql server 2014 - Select * query optimization

Hello,
We have Sql server 2014 server and currently running Select * from table taking more then 2 minutes and it has almost 2M rows, is it we can improve it so it will take less time?

I know this one not having any Where condition to use the index but from execution plan perspective, it's using Cluster Index Scan so cost is 100%.



Thank you!

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Update...
I ran with all the column listing and using Select * from table, both have same results.
Table don't have any Primary key column but have Cluster Index with 4 columns.
Stats are updated, every day.

0 Votes 0 ·

Hi @Pds-2061 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer" or vote answer. If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
Pds-2061 avatar image
0 Votes"
Pds-2061 answered

I have also turned in SET STATISTICS IO ON
It shows mostly logical reads
Scan count 1, logical reads 147518, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Pds-2061 commented

There is not much you can update with such a query. You can do ALTER INDEX REBUILD on the clustered index. This can help if the table is heavily fragmented, particularly in a way so that pages are only half-full.

Else you will need to look at that * and ask yourself, do I really need all those columns? Particularly, if there are LOB columns, can you leave these out?

It also matters where you do the SELECT. If you are running this in SSMS, this is just the wrong place to test. Most time of those two minutes may be spent in SSMS rendering the grids.

Network is of course also an important factor. If the database is on a remote server and you only have a 10 Mbit connection, it is not going to be fast...

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland.
We don't have any LOB, Ntext kind of columns.
I am agreed with you to use Select col1, col2... instead of Select * from table.
For Testing, We have main SSMS to check so we can have some idea, right?
I have checked cluster index on this table and fragmentation is only <2% , Total pages is 144866 and Page Fullness 96 %.
For CPU, % Signal CPU Waits is also <20%
Stats are up to date and I also just updated.
I also tried with RECOMPILE OPTION.

Anything else you suggest?

0 Votes 0 ·

For Testing, We have main SSMS to check so we can have some idea, right?

No that is not right. For this type of operation, the client is just as much as part of the equation as the server is. You need to run this test in the context where you intended to run this query.

If you are on SQL 2016 or later, you can use sys.dm_exec_session_wait_stats to look at the waitstats for the session (Run the query from a fresh connection.) What is your top wait type? My bets are on ASYNC_NETWORK_IO.

0 Votes 0 ·
Pds-2061 avatar image Pds-2061 ErlandSommarskog ·

Thanks once again Erland.
Ima testing in SSMS because this query is part of our Reporting tool so instead of testing there i ma checking on my SSMS as don't want to run in Reporting server.
Unfortunately, We are on Sql Server 2014.

anything you aware about migration from 2008R2 to Sql server 2014 have any kind of performance issue?
I have changed compatibility level to 120 also.
WE are also running Update stats regularly and Index Rebuild every week (based on Fragmentation%)

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Pds-2061 commented

Ima testing in SSMS because this query is part of our Reporting tool so instead of testing there i ma checking on my SSMS as don't want to run in Reporting server.

If you don't want to run it where you are going to run it, you cannot test it. It is that simple.

For this query there is not that much you can to in SQL Server. Well, you can compress the table (if on Enterprise) or even slap on a columnstore index. But if the bottleneck is in the client, which is very much likely the case, it will have zero effect.

As for the waitstats, well you can clear sys.dm_os_wait_stats with

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

just before you run the query, and then inspect the DMV directly after the query.

· 15
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland.
In Reporting tool, We don't have access to run it but use showed us while running the query taking time so we are testing in SSMS to check as it's simple select.

I also ran the "DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);" before running select.
It's not showing any ASYNC_NETWORK_IO.

"But if the bottleneck is in the client, which is very much likely the case" what you mean by this and how we can prove it or check it out?

0 Votes 0 ·

Try running the query in SSMS with the discard results after execution option (menu under Query-->Options-->Results-->Grid). This will reduce expensive rendering time.

0 Votes 0 ·

Thanks Dan,
Result is almost same just 5 seconds faster.

0 Votes 0 ·

I also ran the "DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);" before running select.
It's not showing any ASYNC_NETWORK_IO.

So what are your top waits?

0 Votes 0 ·
Pds-2061 avatar image Pds-2061 ErlandSommarskog ·

Please see the WaitStat

135191-image.png


0 Votes 0 ·
image.png (5.4 KiB)
Show more comments
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @Pds-2061,

Here are a few tips about optimizing queries (some of them are also mentioned by Erland):
Indexing: Ensure proper indexing for quick access to the database.
Select query: Specify the columns in SELECT query instead of SELECT* to avoid extra fetching load on the database.
Running queries: Loops in query structure slows the sequence. Thus, avoid them.
Matching records: Use EXITS() for matching if the record exists.
Subqueries: Avoid correlated sub queries as it searches row by row, impacting the speed of SQL query processing.
Wildcards: Use wildcards (e.g. %xx%) wisely as they search the entire database for matching results.
Operators: Avoid using function at RHS of the operator.
Fetching data: Always fetch limited data.
Loading: Use a temporary table to handle bulk data.
Selecting Rows: Use the clause WHERE instead of HAVING for primary filters.

For more information, please see: https://www.mantralabsglobal.com/blog/sql-query-optimization-tips/

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pds-2061 avatar image
0 Votes"
Pds-2061 answered

Seeya,
Thank you very much for sending some tips regarding query optimization.
I have implemented some of above tips and it was always benefited applying it.
In some cases, when we run through Reporting tool, need to use Select * as including column list not viable option because of sometimes we are adding columns into base table/views.

Agreed, Elrand suggested some useful tips and always he had responded and provided useful tips.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.