Adding or dropping SQL indexes temporarily on production database tables

By Steve Endow

During one of my presentations on Optimizing SQL Scripting at the GP Tech 2017 Conference this week at the Microsoft campus in Fargo, one of the attendees asked an interesting question:

Suppose you need to run a complex query just a few times, and you find that the query would benefit greatly from adding new indexes to one or more tables.  Rather than adding 'permanent' indexes, would it be prudent to temporarily add the indexes so that you could run the query faster, and then remove the indexes when you no longer need them?


I think it is great question. I immediately thought of one likely real world scenario for this, and coincidentally, I shared a Lyft back to the airport with a consultant who described a second scenario where a slightly different index management process was required.


Scenario 1:  Imagine that at the end of each financial quarter, dozens of large, complex financial and sales analysis reports are run against dozens of Dynamics GP company databases.  If some reports take a minute to run, and a few indexes can be added to reduce the report run times to a few seconds, that time savings could really add up.  I could definitely see the value of adding indexes to speed up this process.

But is it worth adding permanent indexes to tables to support the quarterly reports?  Or is it better to add the indexes once per quarter, run the reports, and then remove the indexes?

I don't currently know how to assess the actual costs vs. benefits of those situations, but given that Dynamics GP is already drowning in SQL indexes, and given that the indexes may be dropped during a GP upgrade (and it's easy to forget to recreate them), I think that creating the indexes temporarily seems like a reasonable solution for this hypothetical example.

The one concern I expressed about the solution was the potential for the CREATE INDEX process to lock the tables as they were being built.

I did some research, and confirmed my concern that a table will be locked and inaccessible during the CREATE INDEX process.

This is mentioned in the "Performance Considerations" section of this Books On Line page:

https://technet.microsoft.com/en-us/library/ms190197(v=sql.105).aspx

Most Dynamics GP customers use SQL Server Standard Edition, so indexes are created "offline", and the table is locked until the create index operation completes.

SQL Server Enterprise edition does have an "online" indexing option, but from what I have been able to find, even that feature doesn't provide 100% accessibility of the table during the indexing operation, so there may be some challenges in very high volume environments.

If the temporary indexes make sense, my recommendation would be to add the indexes during a maintenance window, such as late at night, and then run the queries the next day (or next few days), and then remove the index when they are no longer needed.


Scenario 2:  A Dynamics GP consultant told me a story about a prior job where he had to bulk load millions of records into a table on a regular basis.  The bulk load had a very limited time window, so the import had to be completed as quickly as possible.

In order to speed up the import process, they dropped the indexes on the table, imported the millions of additional records into the table, and then added the indexes back to the table.  I hadn't considered that scenario before, but he explained it worked very well.

I was able to find this Books Online article about it and recommendations on when to drop or not drop indexes for bulk load operations.  It provides recommendations depending on whether the table is empty or not, and how much new data is being imported.

https://technet.microsoft.com/en-us/library/ms177445(v=sql.105).aspx


So I learned a few interesting things myself during my session.  Hope this was helpful!

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com