Select * query taking long on 1500 records table and table size is huge. RRS feed

  • Question

  • Hi SQL Gurus,

    I'm investigating a performance issue in one of our prod environment. 

    1. There is table which contains little over 1500 records but the size is showing as over 3GB and index space over 51GB. I wonder why is that?

    2. Simple select * on that table is taking more than 11 seconds to retrieve all records and I see that there is a scan happening on non clustered index which is on Primary key. 


    hAny guidance would be appreciated. 

    • Edited by Shaddy_1 Friday, September 20, 2019 5:34 PM
    Friday, September 20, 2019 5:33 PM

All replies

  • Hey Shadd,

    How many index does this table have? At first look "this appers" to be a very fragmented index/table. Please check the index fragmentation and statitistics from this table.


    Friday, September 20, 2019 5:58 PM
  • Hi Rafael,

    It has 10 nonclustered and 1 clustered indexes. Fragmentation is very high at 98% almost. 

    Friday, September 20, 2019 7:02 PM
  • So Shaddy,

    You'll need to rebuild/Reorganize this indexes. Be careful when running the reindex because it can cause some overhead against the database. If your SQL Server version is Enterprise, you can do it online without generating exclusive locks.


    Friday, September 20, 2019 7:33 PM
  • Rafael,

    Thank again for your reply. So you are saying that the table size got huge due to high fragmentation of indexes? 

    I will run the index reorg for now as we do not have schedule downtime for this environment. 

    I will update once the Reorg is done. 

    • Edited by Shaddy_1 Friday, September 20, 2019 8:46 PM
    Friday, September 20, 2019 8:45 PM
  • Shaddy,

    Yes, most of the times fragmentation comes when you have a lot of insert/delete operations... specially deletes. Fragmentation means unused data pages because of deletion.
    Remember that Rebuild is more effective than Reorg but it more aggressive too (overhead/blocking).

    Let us know the results when you have finished your process.


    Friday, September 20, 2019 9:05 PM
  • I believe it is better to rebuild.
    Post table definition (columns, data types...)

    Friday, September 20, 2019 9:08 PM
  • Hi Shaddy_1,

    Could you try to use the scripts of Ola Hallengren to reduce index fragmentation without rebuilding all indexes? Please refer to Stop Worrying About SQL Server Fragmentation.

    Best regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact

    Monday, September 23, 2019 7:31 AM