Adding and removing indexes

Question

Adding and removing indexes

Data Rat

SSC Eights!

Points: 943
More actions
August 10, 2018 at 12:36 pm

#359157

Hi Friends,
I was wondering is it a best practice to add indexes using sql statements before running a query and then removing them after the query has finished. I understand that for some operations to have indexes on a column are good and for other operations it is inefficient to have them. Are there any drawbacks to doing it this way. Thanks !!!

Viewing 3 posts - 1 through 2 (of 2 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Ronnie65 - Friday, August 10, 2018 12:36 PM
Hi Friends,
I was wondering is it a best practice to add indexes using sql statements before running a query and then removing them after the query has finished. I understand that for some operations to have indexes on a column are good and for other operations it is inefficient to have them. Are there any drawbacks to doing it this way. Thanks !!!

You would be actually losing the benefits of having indexes in the first place. Basically, adding an index would require to scan the whole table and write the index on disk and that's exactly what you want to prevent either by creating them or deleting them.
There is an advantage on dropping indexes before a large data load and recreate them after the load. That's because it's usually faster to create indexes in order that rearrange the index pages during the load. It also reduces eliminates index fragmentation in the process.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

To add to what Luis posted, you have to remember that, unless you're in some other Recovery Model other than FULL, index creation is fully logged. If you have a lot of queries that use the same or similar index, most of your disk I/O and CPU usage will be dedicated to nothing but index creation.

You also have to remember that you need the "Expensive Edition" to build indexes ONLINE and that takes a whole lot longer than building them OFFLINE.

So, "NO", it is NOT a "Best Practice" to generally create indexes for use and then drop them. As with all else in SQL Server, "It Depends". There are some benefits during index maintenance to drop or disable certain types of indexes and then rebuild them instead of defragging them and there are some benefits to doing minimal logging during large imports that may require dropping indexes to get the full effect of minimal logging (even with the magic that can happen with Trace Flag 610 and the change in requirements for "Minimal Logging" in SQL Server 2016). But, not as a general "Best Practice".

Please see the following link for more problems that creating and dropping indexes as a matter of rote may cause whether doing it online or offline..
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-2017

--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)