Recreating sane index not improve the performance

Question

Recreating sane index not improve the performance

jkramprakash

SSCommitted

Points: 1870
More actions
March 16, 2019 at 12:29 am

#377691

I have five indexes and i dropped the indexes one by one which is not used.When i dropped the one index it affects the performance so i recreated the same index,but it will not improve performance.Why it is not taking?whether we have to rebuild the index or refresh the db?or any order to recreate the index?Please explain

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

jkramprakash - Saturday, March 16, 2019 12:29 AM
I have five indexes and i dropped the indexes one by one which is not used.When i dropped the one index it affects the performance so i recreated the same index,but it will not improve performance.Why it is not taking?whether we have to rebuild the index or refresh the db?or any order to recreate the index?Please explain

I'm curious... how did you determine the indexes weren't being used?

Have you checked the execution plan to see what's going on with the indexes? And how do you know you rebuilt the index exactly the same as it was before? Did you script it out before you dropped it?

Also, this should be a lesson for you. Don't drop indexes and then see if the train wrecked. Just disable them until you've proven there is no impact on performance. Only drop them after a week or two. Disabling them also clears them of data to save space. All you need to do to reactivate them is to rebuild them.

Also, if you've not been doing index maintenance and the index is still set to "0" for a Fill Factor, you may be running into the issue of massive page splits after the rebuild because the index no longer has any room to grow where page splits in the past made room to grow. In the past, that's actually been a major source of blocking for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jkramprakash SSCommitted Points: 1870 More actions · Answer 2

yes.i checked the actual execution plan,it is not showing the particular index in the plan and i am using the query in my stored procedure.Instead of dropping the index i will try to disable the index and check the performance.I want to learn more about the execution plan for index use.is there any good link ?to learn about execution plan.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

jkramprakash - Sunday, March 17, 2019 4:38 AM
yes.i checked the actual execution plan,it is not showing the particular index in the plan and i am using the query in my stored procedure.Instead of dropping the index i will try to disable the index and check the performance.I want to learn more about the execution plan for index use.is there any good link ?to learn about execution plan.

No... don't disable the index. Disabling indexes was a suggestion as to see if the index made a difference before you actually dropped it.

The fact that the index that you think was helping performance (and you put back) isn't in the actual execution plan indicates to me that you are making a mistake as to which index you should be looking for to put back to solve your performance issue.

I recommend that you recreate all of the indexes that you dropped, verify that performance has returned to what it previously was, and then look at which index is actually being used in the execution plan.

You also need to change you method for looking for "unused indexes" because that method obviously didn't work so well. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jkramprakash SSCommitted Points: 1870 More actions · Answer 4

Thank you.I disabled the index and testing the performance,but i want to know whether any order or sequence to recreate the dropped indexes.

This reply was modified 5 years ago by jkramprakash.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Why do you ask? After almost two weeks, has some new problem occurred? 😉

Generally speaking, the order in which you create or rebuild non-clustered index makes no diference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jkramprakash SSCommitted Points: 1870 More actions · Answer 6

jkramprakash

SSCommitted

Points: 1870

April 2, 2019 at 12:03 pm

#2500696

ok.Thank you.