Case Studies

CTC Global Sdn Bhd

Issue Brief: Excessive blocking in production database, and daily operations could not be done on-time.

Applied Recommendations: 15     Duration: 2 Weeks     Improvement: 90%


Average IO Latency (ms) / Database


Before


After


Top 10 Long Running Queries

Before

Average_SecondsQueryDatabase_name
891.71)SELECT "MANDT" AS c ,"EBELN" AS c ,"EBELP" AS c ,"LOEKZ" AS c ,"STATU" AS c ,"AEDAT" AS c ,"TXZ01" AS c ,"MATNR" AS c ,"EMATN" AS c ,"BUKRS" AS c ,"WERKS" AS c ,"LGORT" AS c ,"BEDNR" AS c ,"MATKL" A...
790.57)SELECT "MANDT" AS c ,"EBELN" AS c ,"BUKRS" AS c ,"BSTYP" AS c ,"BSART" AS c ,"BSAKZ" AS c ,"LOEKZ" AS c ,"STATU" AS c ,"AEDAT" AS c ,"ERNAM" AS c ,"PINCR" AS c ,"LPONR" AS c ,"LIFNR" AS c ,"SPRAS" A...
396.92)SELECT T_00 ."VBELN" AS c ,T_01 ."POSNR" AS c ,T_00 ."AUART" AS c ,T_00 ."ERDAT" AS c ,T_01 ."PS_PSP_PNR" AS c ,T_01 ."WERKS" AS c ,T_01 ."CHARG" AS c ,T_01 ."MATNR" AS c ,T_04 ."MAKTX" AS c ,T_01 ....
348.26)SELECT "MANDT" AS c ,"MATNR" AS c ,"ERSDA" AS c ,"ERNAM" AS c ,"LAEDA" AS c ,"AENAM" AS c ,"VPSTA" AS c ,"PSTAT" AS c ,"LVORM" AS c ,"MTART" AS c ,"MBRSH" AS c ,"MATKL" AS c ,"BISMT" AS c ,"MEINS" A...
124.47 DELETE FROM sap_perfinfo FROM @sampler a, sap_perfinfo b WHERE a.sample_id= b.sample_id AND a.stype= 'Z' AND b.resi_flag=...P01
103.78)SELECT "MANDT" AS c ,"BUKRS" AS c ,"BELNR" AS c ,"GJAHR" AS c ,"BLART" AS c ,"BLDAT" AS c ,"BUDAT" AS c ,"MONAT" AS c ,"CPUDT" AS c ,"CPUTM" AS c ,"AEDAT" AS c ,"UPDDT" AS c ,"WWERT" AS c ,"USNAM" A...
92.84)SELECT "MANDT" AS c ,"BUKRS" AS c ,"BELNR" AS c ,"GJAHR" AS c ,"PAGENO" AS c ,"TIMESTMP" AS c ,"PAGELG" AS c ,"VARDATA" AS c FROM "RFBLG" WHERE "MANDT" = @P1 AND "BELNR" BETWEEN @P2 AND @P3 ORDER BY...
56.93select distinct tabname, indname, indid from ( select distinct OBJECT_NAME(i.object_id) as tabname, i.name as indname, i.index_id as indid, s.auto_created as is_autocreated, s.user_created as...
46.48)SELECT TOP 1 COUNT(*) FROM "CDHDR" WHERE "MANDANT" = @P1 AND "CHANGENR" = @P2 /* R3:SAPLSE16N:32734 T:CDHDR */...
43.79)SELECT T_00 ."ERNAM" AS c ,T_00 ."LIFNR" AS c ,T_00 ."BEDAT" AS c ,T_00 ."EBELN" AS c ,T_01 ."EBELP" AS c ,T_01 ."NETPR" AS c ,T_01 ."AFNAM" AS c ,T_01 ."MATNR" AS c ,T_01 ."MATKL" AS c ,T_01 ."MENG...

After

Average_SecondsQueryDatabase_name
0.57CREATE PROCEDURE sap_p_retrieve @cclasschar(1) AS SET NOCOUNT ON -- Initialize selection criteria DECLARE @cntr_tab TABLE ( counter_id int ) INSERT INTO @cntr_tab SELECT counter_id FROM sap_perfcntr ...P01
0.27create function sap_curr_db_size_details() returns @dbszdetl table ( [reserved]decimal(15, 0), [numtabs] int, [tabdata] decimal(15, 0), [numinds] int, [inddata] decimal(15, 0), [unused]decimal(15, 0)...P01
0.01CREATE PROCEDURE sap_dbcoll AS DECLARE @rcint -- Procedure return value DECLARE @sample_id int -- IDENTITY col of sap_perfsample DECLARE @threshint -- Last sample_id before restart DECLARE @boot...P01
0.00-- @cclass : See sap_perfcntr for definitions -- @since: -1 : Since startup --0 : Floating average -- >0 : sample_id of reset (since reset) CREATE PROCEDURE sap_p_snapshot @obj_sourcesysname = '...P01
0.00create function sap_curr_db_sizeinfo() returns @dbszinfo table ( db_size int, db_alloc int, log_size int, log_alloc int ) as BEGIN declare @db_size bigint declare @db_allocbigint declare @log_sizebig...P01
0.00CREATE proc sap_get_columns_of_indexes @table_name sysname, @schema_name sysname, @db_name sysname, @also_autocre_stats char(1), @also_mancre_stats char(1), @target_schema sysname = null AS BEGIN set...P01
0.00create procedure sap_mon_sqldbfiles @obj_sch sysname = null, @dbname sysname = null as begin set nocount on declare @perm int declare @curdbname sysname declare @physname nvarchar(512) ...P01
0.00create procedure sap_rz20_opentrx as begin set nocount on declare @trancnt int declare @returndata table ( item nvarchar(10), -- block or trx secondsint, descrnvarchar(100) ) declare @block...P01
0.00create procedure sap_rz20_tlogcheck @time nchar(4) = 'mins' AS BEGIN declare @tlogMins int declare @logUsed float create table #dbccout ( dbNamesysname, logSizeMB float, logUsed float, statu...P01



Pacific and Orient Technologies Sdn Bhd

Issue Brief: Current database servers are configured in Hyper-Converged infrastructure,Un-managed database cause increase growth in Memory sprawling. Customer was concerned that they will be required to upgrade physical Memory in entire pool of servers in future.

Applied Recommendations: 9     Duration: 1 Week     Improvement: 50%


Average IO Latency (ms) / Database


Before


After


Memory Utilization (MB) / Database


Before


After




Skynet Express Sdn Bhd

Issue Brief: All the heavy workload database migrated into a single hardware; and resource utlization frequently hit the threshold. overall database performance degraded due to excessive hardware resource utlization.

Applied Recommendations: 8     Duration: 2 Weeks     Improvement: 60%


Average IO Latency (ms) / Database


Before


After


Memory Utilization (MB) / Database


Before


After


Processor Signal Wait


Before


After