Business Central Performance Optimierung mit V8 Search XE – Teil 1

V8 Search XE 4 has a powerful set of tools to analyze and solve Dynamics Business Central performance issues. This article deals with the quick diagnosis of SQL Server problems in conjunction with Business Central.

Part 1: V8 Search XE SQL Toolbox

With the V8 Search XE SQL Toolbox, administrators or system administrators can save a lot of time to analyze basic performance problems with Business Central and the SQL Server in seconds.

Every database administrator naturally has his own collection of scripts and tools to administer his SQL server. The collection of scripts we have selected in V8 Search XE is designed to give you a first impression of the status of your SQL server and your databases. The selection of TSQL queries is primarily intended for all system administrators to gain a quick overview of the status of the SQL Server and the respective Dynamics Business Central databases. The advantage of V8 Search XE is that you do not have a loose collection of scripts, but a structured setup with ready-made dialogs that show you the results of the queries.

The V8 Search XE SQL Toolbox is divided into two areas; the first area contains queries relating to the SQL Server. The second section is again divided into two parts. The first section deals with the selected database and general questions about the status of the SQL database. In the second section, the queries are specifically geared towards the topic of database indexing.

 SQL Server Check

The SQL Server Check section had a complex substructure with a myriad of SQL scripts that can document the state of the SQL server. You will not need every script every day, some of them may never be executed. Of course, we cannot judge how your SQL server landscape is structured. The included scripts offer you the possibility to react to different scenarios.

In jedem einzelnen Themenordner im SQL Server Check stehen ihnen diverse Abfragen zur Verfügung.

 SQL Server Database

The SQL Server Database area is divided into two categories. The first part relates to information about the status of the SQL database. The second category, SQL Server Database Index, is specifically focused on the performance and the associated indexing of the database. An optimal SQL database index strategy for Dynamics Business Central is extremely important. From our point of view, every system administrator should deal intensively with this topic. For example, when a Business Central page is opened or filtered for data in a page, Business Central sends a query to the SQL server to retrieve the data. If this query does not have an optimal index, the display of the data in the page is very often delayed. In the worst case, slow queries without an optimized index lead to table locks or deadlocks. We therefore believe that indexing Business Central SQL databases is extremely important. Every system administrator should deal intensively with this topic.

To use the SQL Server database queries, first select the SQL database about which you want to obtain information.

The name of the individual queries in the SQL toolbox indicates the content of the information.

Unfortunately, we cannot go into every single query in this article, as the collection in the SQL Toolbox is extensive.

Im zweiten Teil des Artikels präsentieren wir ihnen detaillierte Details zu den einzelnen Abfragen und zeigen ihnen, wie Sie mit den gewonnenen Erkenntnissen umgehen können.
 

FAQ

Q.: Can only Dynamics Business Central SQL databases be administered with the V8 Search XE SQL Toolbox?

A.: No, you can administer any SQL Server database on the respective instance with the V8 Search XE SQL Toolbox. You even have the option of querying every SQL Server in your network with the SQL Toolbox.

Q.: How often should I use the "Missing Index" query?

A.: In our opinion, the Missing Index query is one of the most important queries in the toolbox. We recommend that you run this query at least once a week. When introducing V8 Search XE, we always point out that the proposed new index to be created should not be created blindly. Every index that is created on the database should be checked accordingly to ensure that it fulfills the desired functionality.
We have set up special queries in the SQL toolbox for this purpose. You can use these queries to check the newly created indices for their use. Of course, you can also use them to check existing indices of the respective SQL tables.

Gerne beantworten wir ihnen persönlich weitergehende Fragen zu diesem Thema.

Your dynamicsproject.com Team