pdanes (7/22/2014)

---

murnilim9 (7/22/2014)

---

pdanes (7/22/2014)

---

twin.devil (7/22/2014)

---

I think you should consider Jeff suggestion, as he described above that if your 1st column have very low number of value, which i can see in the procedure, i-e 1, and the a high number of newly added rows.

Question did you create this index on query adviser suggestion? OR you were actually having performance issues? can you share what was the performance before this index and after implementing this index ?

By the way, RNT = 1 (hardcoded), how many values does it have? if its only 0's and 1's, then remove this column from the index as it will not that match of help after all. If YES then Try to remove this column from the index, rebuild the index, insert new data in this table and then check the fragmentation of this index.

Absolutely - the first question really should be: Do you even need this index? Here is an article on that to get started: http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/

If it is being used rarely, or perhaps not at all, you should probably simply drop it and forget it.

How big is the index? Small ones can show high fragmentation quickly, even immediately after being rebuilt. The algorithm that calculates fragmentation is not very accurate when the index is small. I experimented with that a few weeks back and found a small index that showed around 40% fragmentation IMMEDIATELY after being rebuilt, before ANY activity had taken place.

If it is being used, and it is substantial in size, look at the queries that are using it. Are they being hampered by the fragmentation? If not, let it be. A fragmented index is something to consider, but not -necessarily- a hindrance to performance.

Finally, if the queries are dragging, can they be re-coded to avoid that index? Can you add or remove fields from the index to make it behave better? Look at included columns - would they serve just as well, perhaps on another index, and avoid the problematic one? Could the order of columns in the index be changed?

There are many possibilities for tweaking, but you should first investigate whether you actually have a problem.

Btw I just wonder how to set the fillfactor it self .. I run a script that will rebuild all the indexes in all tables in a certain databse with fillfactor = 90 , for example ..but as a matter of fact ..there are some tables that are often updated but some are only used by READ

so i think we cant set all indexes with the same fillfactor ..dont you think so ?

so I just wonder that i can get a script that can solve my problem ?? ...

any idea ?

Certainly, you should not set all fill factors the same, especially if you are having trouble with one. The default fill factor is a best guess, a starting point, but only that. If your table is primarily for reading, you can tighten it up to save space. If you have lots of insert and update activity, you should leave more room, to cut down on fragmentation. As always, experimentation is in order. Try various percentages and see what you get.