I am a database administrator who supports a SQL 2016 instance for an on-prem Business Central install.
Several years ago I supported a few SQL instances for AX and GP installations at a number of client sites, but they were always handed to me configured by the ERP consultant with the relevant settings and maintenance jobs configured to the specific needs of the application. I was vaguely aware of several trace flags that were implemented at that time and very low fill factor specified, but otherwise my responsibility was for the most part only to ensure the necessary jobs ran and the database was backed up. Performance problems were always resolved by the consultant.
I have been looking now for specific performance configurations for BC and haven't found much on the SQL side.
A recent issue has been a lot of blocking almost exclusively through update locks with a small amount of contention in shared (read) locks.
Is it the case with BC that indexes must be managed by BC or can I create them myself? If I remember correctly, indexing in a SQL instance serving AX or GP caused some sort of problem. When I review the indexes that are in database currently, all of them look like they were created by some sort of ORM based on the naming.
I have almost 30 tables that have between 10 and 17 indexes about 2/3 of which have 5-9 key columns. I would normally try very hard to not put even 7-8 indexes on a table in a typical mixed OLTP/OLAP application database and keep it at most 4-5 key columns unless there was a really compelling performance reason to risk the insert performance, let alone in an application that is extremely sensitive to insert update and delete performance. I can see from index usage statistics that most of these indexes either have never been used or are rarely used. could these indexes be contributing to some of the blocking?
Should I be rebuilding indexes with lots of empty pages? SQL generally doesn't benefit from leaving empty pages anything past SQL 2005 but am curious if there is something particular about the way BC writes to SQL that could benefit from it.
Another issue I am seeing is that some of the queries BC generates are very inefficient. There are queries that scan millions of records then filter them down to as little as a single result. I have a lot of select statements that are taking out update locks that cause blocking. I am not sure what my question about that is, other than asking for general advisement on query tuning in BC.
We recently did a big posting of several thousand invoices and even after hours when not a single other user was in the system we still received heavy blocking.
There is no memory, cpu or disk pressure reported by the SQL server, all of the waits are waiting on locks.
Sorry if this was rambling, I just don't know what I need to ask or look for in this.
