0

I have a table with more than 5,000,000 records. The SQL Query performance on this table impacted by index fragment a lot. Looks like everyday, the fragment increasing so fast. I need to rebuild index every day for user's complain.

How to figure it out the possible reason? How to resolve this issue? Is it a hardware issue or need recreate the table with partition option?

KentZhou
  • 24,805
  • 41
  • 134
  • 200
  • First question: do you have a clustered index on that table, and if so, on what column (what datatype does the column have)? – marc_s Nov 22 '13 at 20:04
  • Yes, I have one clustered index(type: int) on this table and 2 non-cluster index(also on int) and one PK index(int too). – KentZhou Nov 22 '13 at 20:22
  • What are the actual table and index definitions? Also [per my comments on this question too](http://stackoverflow.com/q/19998471/73226) what you have told us so far doesn't necessarily point to the fragmentation being the issue. – Martin Smith Nov 22 '13 at 20:48
  • Per the information given here I recommend the same counter-measures that I would always recommend against fragmentation. They are well-documented on the web. Search: "SQL Server fragmentation mitigation". – usr Nov 23 '13 at 00:02

0 Answers0