Rule of thumb when to disable indexes?

Question

Rule of thumb when to disable indexes?

roblew 15918

Ten Centuries

Points: 1059
More actions
June 19, 2014 at 4:20 am

#294712

It's often said or done that when inserting or updating into a 'large' table that disabling the non-clustered indexes can is needed for performance.
Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule of thumb to this?
Say you have a table with half a billion rows and 4 non-clustered indexes and are only updating half a million rows then sometimes disabling every night and re-enabling can take way more time than the actual update. Haven't found an articles advising to disable them when a table is over X rows and you are updating Y% of them...
Just wondering what peoples thoughts, experiences are on this.
thanks for your thoughts.

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

...
Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule of thumb to this?
...

Yep, there is one rule of thumb to this:

The only true way to find out if turning indexes off is best or not, is by testing the different options!

🙂

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Test both options, see which is best.

Bear in mind that to enable an index you need to rebuild it, so it'll only be a good idea when the time taken to rebuild four nonclustered index is less than the time saved by disabling them for the update

You won't find any trustworthy article saying to disable them when the table has more than X rows and you're updating more than Y%, because it's very dependent on what you're doing in the update, what the indexes are, what your hardware is like, what other load is on the server and probably several more factors.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

roblew 15918 Ten Centuries Points: 1059 More actions · Answer 3

roblew 15918

Ten Centuries

Points: 1059

June 19, 2014 at 4:43 am

#1723152

Thanks 🙂

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

Taking a higher-level view, what's most important for performance of very large tables is making sure the clustered index is based on the best column(s). That can often eliminate the need for one or more non-clustered indexes, thus speeding up all statements.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

Hi

I use the standard report "Index usage statistics" in ssms. I analyse the Index usage statistics and the Index operational statistics to determine the degree of usefulness of an index.

Igor Micev,My blog: www.igormicev.com