Posted by: virusno1 September 17, 2015
SQL - Select Statement - Performance Issue
Login in to Rate this Post:     0       ?        
NepaliBhai, putting a clustered index may help not much because you are not putting any filter in the select statement. When you mention all the columns and selecting all of them then sql will use table scan as an execution plan.
You said you have 100 columns which is terrible design. Your total row size should be less than 8K and if you go over that limit then sql will put your data into external storage and retrieving from external storage other than your actual in row data page is very costly operation.
It seems like you can't do much to change the table schema at this time. If you already have a clustered index then I would suggest you to check fragmentation. Your index fragmentation should not be more than 30%. You can either use dbcc showcontig or sys.dm_db_index_physical_stats dmv. Check to see extend fragmentation or page fragmentation. When you have lot of fragmentation then it's nothing but your query is running hard and switching back and forth to get the data from different pages which is really bad. Please paste your dbcc result here I may help. If you see lot of fragmentation then I would suggest to rebuild the index but please consult all the users while rebuilding it. You can do online rebuild or offline rebuild depending upon your sql server version but both of them affects the performance while rebuilding.


Last edited: 17-Sep-15 11:51 AM
Last edited: 17-Sep-15 11:54 AM
Read Full Discussion Thread for this article