Through the page 9521 Database Missing Indexes it is possible to see the missing indexes suggested by SQL Server for Business Central (in fact, an SQL function is called which returns the missing indexes).
In this case the same function has been recreated on SQL (stored procedure) so that it can be launched both from BC (Onpremise) and from SQL.
For Business Central Online it is only possible to create indexes by declaring them on tables in AL, not from SQL as it is not directly available.
Page nr. 9521 – Database Missing Indexes
Example of missing indexes:
NOTE: these are all the suggested indexes, there is no peer-percentage fragmentation control (but it could be inserted in the stored procedure), but if they are proposed it means that they are needed.
Create missing index
The customization below allows you to extend the standard page by adding an action to launch the SQL stored procedure (it’s just an example, use .NET core to exec SQL Stored procedure)
- Click on “Create missing index” 🡪 create index on “Warehouse Shipment Line”
AL Code
//#1 – Extend Page Database Missing Indexes
pageextension 85001 “ExtDatabase Missing Indexes” extends “Database Missing Indexes”
{
layout
{
}
actions
{
addfirst(Processing)
{
action(AltERtest)
{
ApplicationArea = All;
Caption = ‘Create missing index’, Comment = ‘Creazione indici mancanti’;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
Image = Process;
trigger OnAction();
begin
CLEAR(AddFunctions);
AddFunctions.Createmissingindex(”);
end;
}
}
}
var
AddFunctions: Codeunit AdditionalFunctionSQL
//#2 – Create Missing Indexes – exec SQL Stored Procedure
procedure Createmissingindex(parBCEnv: Code[100])
var
Query2Exec: Text[1024];
ShipLineFilters: Text[250];
WhereFilter: Text[1024];
TxtProcess: label ‘Processing’;
StartingDateTime: DateTime;
begin
if IsNull(SQLConnectionDN) then
SQLConnect(); //create a standard SQL Connection
Query2Exec := ”;
Query2Exec := ‘sp_CreateMissIndexBC’; //SQL Stored Procedure
parBCEnv := ”;
parBCEnv := ‘[‘ + SQLConnSetup.”Database Name” + ‘]’;
Clear(SQLCommand);
SQLCommand := SQLConnectionDN.CreateCommand();
SQLCommand.CommandText(Query2Exec);
SQLCommand.CommandType := SQLCommand.CommandType::StoredProcedure;
SQLCommand.CommandTimeout := 180;
Clear(SQLParameter);
SQLParameter := SQLParameter.SqlParameter();
SQLParameter.ParameterName := ‘@BCEnv’;
SQLParameter.DbType := SQLParameter.DbType::String;
SQLParameter.Size := 100;
SQLParameter.Value := parBCEnv;
SQLParameter.Direction := SQLParameter.Direction.Input;
SQLCommand.Parameters.Add(SQLParameter);
SQLCommand.ExecuteNonQuery();
SQLConnectionDN.Close();
Clear(SQLCommand);
Clear(SQLConnectionDN);
end;
SQL Stored Procedure: “sp_CreateMissIndexBC”
The sql stored procedure sp_CreateMissIndexBC creates the missing indexes and is called from a .NET core connection from the BC page.
SQL Side
Manual execution example
exec [dbo].[sp_CreateMissIndexBC] ‘[BC_T_22]’
Stored Procedure – “sp_CreateMissIndexBC”
— =============================================================
— Author: RS
— Create date: 31/07/2023
— Description: Create SQL Indexes from BC Missing Indexes Logic
— =============================================================
CREATE PROCEDURE [dbo].[sp_CreateMissIndexBC]
@BCEnv varchar(100)
AS
BEGIN
SET NOCOUNT ON;
— Variables
DECLARE @LenEnv as integer
SET @LenEnv = LEN(@BCEnv)
DECLARE @CreateIndex VARCHAR(max) — Index SQL String
— IndexCheck for BCEnv – list
select * from sys.dm_db_missing_index_details where left(statement, @LenEnv) = @BCEnv
SELECT
‘CREATE INDEX missing_index_BCEnv_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle) + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL (mid.equality_columns, ”) + CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ‘,’
ELSE ”
END + ISNULL (mid.inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_missing_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 0
AND left(statement, @LenEnv) = @BCEnv –Filter by BCEnv
— Create Index from Cursor (EXEC)
DECLARE Index_cursor CURSOR FOR
SELECT
‘CREATE INDEX missing_index_BCEnv_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle) + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL (mid.equality_columns, ”) + CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ‘,’
ELSE ”
END + ISNULL (mid.inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_missing_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 0
AND left(statement, @LenEnv) = @BCEnv –Filter by BCEnv
— Open Cursor
OPEN Index_cursor
FETCH NEXT FROM Index_cursor INTO @CreateIndex
WHILE @@FETCH_STATUS = 0
BEGIN
— Print Index
PRINT @CreateIndex
— Create SQL Index
EXECUTE (@CreateIndex)
FETCH NEXT FROM Index_cursor INTO @CreateIndex
END
— Close Cursor
CLOSE Index_cursor
DEALLOCATE Index_cursor
SET NOCOUNT OFF;
END
Download source here:
NAV_SQL/sp_CreateMissIndexBC at sp_CreateMissIndexBC · rstefanetti/NAV_SQL (github.com)
