Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 5500 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 900 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:93070
Title:Adding or dropping SQL indexes temporarily on production database tables
URL:http://dynamicsgpland.blogspot.com/2017/08/adding-or-dropping-sql-indexes.html
Description: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!



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




Category:
Link Owner:
Date Added:August 12, 2017 06:59:49 AM
Number Hits:0
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.

 
GPWindow.com

Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.