Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Safe to disable databa...
Small and medium business | Business Central, N...
Suggested answer

Safe to disable database index?

(1) ShareShare
ReportReport
Posted on by Community member 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.
  • 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,748 Super User 2024 Season 1 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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans