You won't be able to guess where this performance issue was coming from!
Share
Case Study: Speeding Up 🚀 a Slow SAP BO 4.3 Environment 🐢
A client approached us after their SAP BusinessObjects 4.3 environment became painfully slow. Despite generous Tomcat heap settings, navigating the CMC and the Fiori BI Launchpad was sluggish, impacting user productivity and report delivery.
Investigation & Findings
We dug into the CMS database (SQL Server 2022) and discovered the culprit: missing indexes. Without proper indexing, key queries were scanning large tables, throttling performance. Thankfully, SQL Server’s Dynamic Management Views (DMVs) can identify missing index recommendations.
Step 1: Backup the CMS Database
Essential first step: always take a full backup before making structural changes.
BACKUP DATABASE BO43CMSDEV
TO DISK = 'D:\Backup\CMSDB.bak';Step 2: Identify Missing Indexes
Run this query to list tables and columns where indexes would yield the highest performance impact:
SELECT
mid.statement AS [Table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.user_seeks,
migs.avg_total_user_cost * migs.avg_user_impact AS [ImprovementImpact]
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY [ImprovementImpact] DESC;Step 3: Auto-Create Recommended Indexes
To quickly resolve performance bottlenecks in non-production environments, we accepted all recommendations and auto-generated indexes using this script:
DECLARE
@sql NVARCHAR(MAX),
@table SYSNAME,
@equality NVARCHAR(MAX),
@inequality NVARCHAR(MAX),
@includeCols NVARCHAR(MAX),
@indexName NVARCHAR(256),
@baseTable NVARCHAR(256),
@cleanName NVARCHAR(256);
DECLARE cur CURSOR FOR
SELECT
mid.statement,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC;
OPEN cur;
FETCH NEXT FROM cur INTO @table, @equality, @inequality, @includeCols;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @baseTable = REPLACE(REPLACE(REPLACE(@table, '[', ''), ']', ''), '.', '_');
SET @cleanName = LEFT(
REPLACE(REPLACE(REPLACE(
ISNULL(@equality, '') + '_' + ISNULL(@inequality, ''),
'[', ''), ']', ''), ', ', '_'),
100
);
SET @indexName = 'IX_' + LEFT(@baseTable + '_' + @cleanName, 128);
SET @sql = 'CREATE NONCLUSTERED INDEX [' + @indexName + '] ON ' + @table + ' (' +
COALESCE(@equality, '') +
CASE WHEN @inequality IS NOT NULL AND @equality IS NOT NULL
THEN ', ' + @inequality
WHEN @inequality IS NOT NULL
THEN @inequality ELSE ''
END + ') ' +
COALESCE('INCLUDE (' + @includeCols + ')', '') + ';';
PRINT '-- Creating index: ' + @indexName;
PRINT @sql;
BEGIN TRY
EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM cur INTO @table, @equality, @inequality, @includeCols;
END
CLOSE cur;
DEALLOCATE cur;
When to Use This Script
- Non-production or staging environments only
- After a verified database backup
- When you accept automatically created indexes without manual review