Environment: 

iVend 6.6

Problem Statement: 

We have noticed that customers are facing performance issues with iVend 6.6.

Symptoms: 

Environment Specific

Resolution/Work Around: 

Technical Team has checked and verified the issue and provided below steps which need to be followed and exercised :

1. Run the query on HO, Store and Offline POS DBs and find if any custom indexes are present [DO NOT DELETE Index Name - IDX_PrcUOMPriceMatrix as this nomenclature was used by CXS Product Team mistakenly]

SQL Query - Select * from sys.indexes where [name] like 'IDX%'

The above query will provide names of custom indexes built by the CXS L3 Team. If you find any please do not delete and check from the attachment. Those that do not exist execute only those.

2. P.F.A list of common NON-CLUSTERED Indexes that are required to be applied on iVend Enterprise (HO DB), Store DBs and Offline POS DBs. You can provide this directly to the customer and inform them to stop all the services, all business users must be logged out and then they need to execute the script. [Better if the script is executed during downtime after taking DB Backup].

CREATE NONCLUSTERED INDEX [IDX_CXS_CusCustomerAddress5] ON [dbo].[CusCustomerAddress](CustomerKey ASC)

CREATE NONCLUSTERED INDEX [IDX_CXS_CusCustomerContact5] ON [dbo].[CusCustomerContact](CustomerKey ASC)

CREATE NONCLUSTERED INDEX [IDX_CXS_InvGoodReceiptDetail5] ON [dbo].[InvGoodReceiptDetail](GoodsReceiptKey ASC)

INCLUDE (GoodsReceiptDetailKey, LineNumber, SourceKey, SourceDetailKey, ProductKey, QuantityOrdered, QuantityReceived, Price, TaxKey, TaxRate, ScannedSequence, ItemsPerUnit, UOMKey, UOMQuantityReceived, UOMQuantityOrdered, ReasonCodeKey, QuantityReturned, UOMQuantityReturned, UnitCost, Total, UniqueLineNumber, FromLocationKey, ToLocationKey, SourceDetailUniqueLineNumber, ToWarehouseKey, Comments, DiscountType, DiscountAmount, DiscountPercentage, Tax, TotalTaxableAmount)

CREATE NONCLUSTERED INDEX [IDX_CXS_InvProduct5] ON [dbo].[InvProduct](IsDeleted ASC, Description ASC)

INCLUDE (ProductKey, IsGiftCertificate)

CREATE NONCLUSTERED INDEX [IDX_CXS_InvProductCategoryProduct5] ON [dbo].[InvProductCategoryProduct](ProductCategoryKey ASC)

INCLUDE (ProductKey)

CREATE NONCLUSTERED INDEX [IDX_CXS_LbrLaborSession5] ON [dbo].[LbrLaborSession](StoreKey ASC, UserKey ASC, Status ASC)

INCLUDE (LaborSessionKey, JobCodeKey, BusinessDate, StartDate, EndDate, BreakCount, TotalBreakTime, TotalTime, LastBreakTime, CreatedBy, Created, ModifiedBy, Modified)

CREATE NONCLUSTERED INDEX [IDX_CXS2_ProPromotionApplicabilityDetails5] ON [dbo].[ProPromotionApplicabilityDetails](PromotionApplicabilityKey ASC)

INCLUDE (SourceType, SourceKey)

CREATE NONCLUSTERED INDEX [IDX_CXS_TilCountDenomination5] ON [dbo].[TilCountDenomination](TillCountDetailKey ASC)

INCLUDE (TillCountDenominationKey, CurrencyDenominationKey, CurrencyKey, PaymentTypeKey, Quantity, DenominationValue, Amount, ExchangeRateKey, ExchangeRate, ForeignCurrencyAmount)

CREATE NONCLUSTERED INDEX [IDX_CXS_TilSessionTransaction5] ON [dbo].[TilSessionTransaction](TransactionType ASC, TillKey ASC) INCLUDE (StoreKey, BusinessDate, Amount)

CREATE NONCLUSTERED INDEX [IDX_CXS_TilTill5] ON [dbo].[TilTill](Status ASC) INCLUDE (TillKey, StoreKey, BusinessDate, StartDate, EndDate, FinalizedDate, MasterTillKey)

CREATE NONCLUSTERED INDEX [IDX_CXS_TrxTransactionAudit5] ON [dbo].[TrxTransactionAudit](SourceDetailType ASC, SourceDetailKey ASC)

CREATE NONCLUSTERED INDEX [IDX_CXS_TrxTransactionCoupon5] ON [dbo].[TrxTransactionCoupon](SourceKey ASC, SourceType ASC, Type ASC)

CREATE NONCLUSTERED INDEX [IDX_CXS_TrxTransaction7] ON [dbo].[TrxTransaction](PassIssued ASC, BusinessDate ASC, StoreKey ASC, IsSuspended ASC, CustomerKey ASC, IsVoided ASC)

INCLUDE (ActualDate, TransactionId, UserKey, POSKey, Tax, Total, CurrencyKey, TillKey, Created, IsDeleted, ExchangeRate)

CREATE NONCLUSTERED INDEX IDX_CXS_TrxTransactionSalesPerson5 ON [dbo].[TrxTransactionSalesPerson] ([SourceKey],[Type])

CREATE NONCLUSTERED INDEX IDX_CXS_TrxTransactionSaleItem5 ON [dbo].[TrxTransactionSaleItem] ([Type]) INCLUDE ([TransactionKey],[ProductKey],[Description],[Price],[Quantity],[DiscountPercent],[SaleDiscountAmount],[TotalTaxableAmount])

CREATE NONCLUSTERED INDEX IDX_CXS_RepReplicationTransactionDetail5 ON [dbo].[RepReplicationTransactionDetail] ([ReplicationTransactionKey]) INCLUDE ([ReplicationTransactionDetailKey])

CREATE NONCLUSTERED INDEX IDX_CXS_RepIntegrationLog ON [dbo].[RepIntegrationLog] ([Flag],[ApplicationKey]) INCLUDE ([IntegrationKey],[SourceType],[SourceKey],[OperationType],[Status],[LogDatetime])

CREATE NONCLUSTERED INDEX IDX_CXS_InvInventoryItemLog ON [dbo].[InvInventoryItemLog] ([IsProcessed],[Created])

CREATE NONCLUSTERED INDEX IDX_CXS_RepReplicationReceivedPayload ON [dbo].[RepReplicationReceivedPayload] ([SiteId],[Status],[RecordCount])

CREATE NONCLUSTERED INDEX IDX_CXS_RepReplicationTransactionDetail ON [dbo].[RepReplicationTransactionDetail] ([DestinationSiteId])

CREATE NONCLUSTERED INDEX IDX_CXS_RepReplicationTransactionDetailSend ON [dbo].[RepReplicationTransactionDetailSend] ([DestinationSiteId])

CREATE NONCLUSTERED INDEX IDX_CXS_InvBatchLog ON [dbo].[InvBatchLog] ([IsProcessed])

CREATE NONCLUSTERED INDEX IDX_CXS_PrcPriceMatrix10

ON [dbo].[PrcPriceMatrix] ([PriceListKey],[IsDeleted])

INCLUDE ([ProductKey],[Price])

CREATE NONCLUSTERED INDEX IDX_CXS_PrcUOMPriceMatrix10

ON [dbo].[PrcUOMPriceMatrix] ([PriceListKey],[ProductKey],[IsDeleted])

INCLUDE ([UOMPriceMatrixKey],[UOMKey],[Price],[CurrencyKey],[Price1],[CurrencyKey1],[Price2],[CurrencyKey2],[AutoUpdate],[Created],[Modified],[CreatedBy],[ModifiedBy])

3. After the Indexes are built please optimize the whole DB [Note – Please inform the customer this is time-consuming and it might take time. If they don’t approve, hand over the script to them and inform them clearly through email i.e. script needs to be executed during downtime only).

-- Optimize DB

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

EXEC sp_updatestats

4. After the Indexes and optimization is done SHRINK whole DB [Note – Please inform the customer this is time-consuming and it might take time. If they don’t approve, hand over the script to them and inform them clearly through email i.e. script needs to be executed during downtime only).

--SHRINK THE DB after optimization

DBCC SHRINKDATABASE(@DBName, 4 )

Recommendations /Suggestion: 

N/A