Skip to main content

Notifications

Business Central forum
Suggested answer

Safe to disable database index?

Posted on by 13
Hi there,
We have 1.6 million records in the table Sales Invoice Header and doing search and filtering on Sales Invoice Number is slow. 15 seconds.
 
With help from database profiler I found the slow query and eventually I figured out that disabling the index $Key9 on the table speeds up the query. It now runs instantly in our test environment.
 
My question: can I safely disable this index in production, or is it something that our Dynamics Partner needs to do via application code?
 
Edit:
The key is a non-clustered index on [Posting Date] ASC, [No_] ASC
 
We have disabled it in test, and we do see any negative impact. My thinking is that if it has an impact, we just reactivate the index. 
 
This is the execution plan with the index disabled:
 
This is the execution plan with the index enabled:
 
The query:
SELECT  TOP (50) /Sales Invoice Header/./timestamp/ AS /timestamp/,        /Sales Invoice Header/./No_/ AS /No_/FROM        /STAR/.dbo./Company$Sales Invoice Header$437dbf0e-84ff-417a-965d-ed2bb9650972/ AS /Sales Invoice Header/ WITH(READUNCOMMITTED)WHERE        (        /Sales Invoice Header/./No_/ COLLATE Danish_Greenlandic_100_CI_AI             LIKE N'%333741%'        OR /Sales Invoice Header/./Sell-to Customer No_/ COLLATE Danish_Greenlandic_100_CI_AI LIKE N'%333741%'        OR /Sales Invoice Header/./Posting Description/ COLLATE Danish_Greenlandic_100_CI_AI  LIKE N'%333741%')ORDER BY        /Posting Date/ DESC,        /No_/ DESC
 
The query is sent when we search for a posted invoice on page Posted Sales Invoices (143) with the filter on Number. I guess it is the query that fetches the invoices in the drop down that pops up when you start typing the invoice number.
As said earlier, the table /only/ contains 1,6m records, which should be far below the upper limits of what BC is capable of. I'm struggling to understand why we see such performance issues in a standard functionality.
Categories:
  • Community member Profile Picture
    Community member 13 on at
    Safe to disable database index?
    @Inge M. Bruvik: They index consists of Posting Date and No as you say - We have disabled it in test, and we do see any negative impact. My thinking is that if it has an impact, we just reactivate the index. 
     
    This is the execution plan with the index disabled:
     
    This is the execution plan with the index enabled:
     
    The query:
    SELECT  
        TOP (50)
        "Sales Invoice Header"."timestamp" AS "timestamp",
        "Sales Invoice Header"."No_" AS "No_"
    FROM
        "STAR".dbo."Company$Sales Invoice Header$437dbf0e-84ff-417a-965d-ed2bb9650972" AS "Sales Invoice Header" WITH(READUNCOMMITTED)
    WHERE
        (
            "Sales Invoice Header"."No_" COLLATE Danish_Greenlandic_100_CI_AI LIKE N'%3337418%'
            OR "Sales Invoice Header"."Sell-to Customer No_" COLLATE Danish_Greenlandic_100_CI_AI LIKE N'%3337418%'
            OR "Sales Invoice Header"."Posting Description" COLLATE Danish_Greenlandic_100_CI_AI LIKE N'%3337418%'
        )
    ORDER BY
        "Posting Date" DESC,
        "No_" DESC
     
    The query is sent when we search for a posted invoice on page Posted Sales Invoices (143) with the filter on Number. I guess it is the query that fetches the invoices in the drop down that pops up when you start typing the invoice number.
    As said earlier, the table "only" contains 1,6m records, which should be far below the upper limits of what BC is capable of. I'm struggling to understand why we see such performance issues in a standard functionality.
  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    Safe to disable database index?
    You are not saying anything what the key fields in key 9 are in your system.
    In the latest version of  Business Central this is the posting date index and that one I would hesitate to disable just like that.
    But in your system key 9 can be something different.
    I would therefor recommend to run this through your partner, they should be able to give your more insight into the impact removing that specific index can have on your system.
    If you have any information around the specific key fields for your index you can update this question with that info.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,493 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans